1. מבוא
אנחנו שמחים שהצטרפת לחלק השלישי של העקרונות הבסיסיים של Apps Script עם הפלייליסט ב-Google Sheets.
על ידי השלמת שיעור Lab זה, תוכלו ללמוד איך להשתמש במניפולציה של נתונים, בתפריטים מותאמים אישית ובאחזור נתונים ציבורי ב-Apps Script כדי לשפר את חוויית השימוש ב-Sheets'. אתם תמשיכו לעבוד עם הכיתות SpreadsheetApp , Spreadsheet , Sheet ו-Range שהכילו את ערכות הקוד הקודמות בפלייליסט הזה.
מה תלמדו
- איך מייבאים נתונים מגיליון אלקטרוני אישי או משותף ב-Drive?
- איך יוצרים תפריט מותאם אישית באמצעות הפונקציה
onOpen(). - איך מנתחים ומשנים ערכים של נתוני מחרוזת בתאים של Google Sheets?
- כיצד לשלוף ולשנות נתוני אובייקט JSON ממקור API ציבורי.
לפני שמתחילים
זוהי שיעור הקוד השלישי בסקריפט של Apps of Apps ב-Google Sheets. לפני התחלת שיעור ה-Codelab הזה, חשוב להקפיד להשלים את ה-codelabs הקודמים:
מה תצטרך להכין
- היכרות עם הנושאים הבסיסיים של Apps Script שנבדקו בשיעורי הקוד הקודמים של הפלייליסט הזה.
- היכרות בסיסית עם עורך Apps Script
- היכרות בסיסית עם Google Sheets
- יכולת לקרוא את Sheets A1 ב-Sheets
- היכרות בסיסית עם JavaScript וה
Stringכיתה שלה
2. הגדרה
במסגרת התרגילים ב-Codelab הזה צריך לעבוד עם גיליון אלקטרוני. כדי ליצור גיליון אלקטרוני לשימוש בתרגילים האלה, מבצעים את השלבים הבאים:
- יצירת גיליון אלקטרוני ב-Google Drive. כדי לעשות זאת מממשק Drive, בוחרים באפשרות > חדש: Google Sheets. פעולה זו יוצרת את הגיליון האלקטרוני החדש ופותחים אותו. הקובץ נשמר בתיקיית Drive.
- לוחצים על הכותרת של הגיליון האלקטרוני ומשנים אותו מ-"גיליון אלקטרוני ללא שם&&; ל-" מניפולציה של נתונים ותפריטים מותאמים אישית". הגיליון אמור להיראות כך:
- כדי לפתוח את עורך הסקריפטים, לוחצים על תוספים>. סקריפט של אפליקציות
- לוחצים על שם הפרויקט ב-Apps Script ומשנים אותו מ-"פרויקט" ל-"מניפולציה של נתונים ותפריטים מותאמים אישית." לוחצים על שינוי שם כדי לשמור את השינוי בשם הפריט.
הגיליון האלקטרוני ופרויקט ריקים, אפשר להתחיל את שיעור ה-Lab. אפשר לעבור לקטע הבא כדי להתחיל לקבל מידע על תפריטים מותאמים אישית.
3. סקירה כללית: ייבוא נתונים באמצעות פריט בתפריט מותאם אישית
Apps Script מאפשר להגדיר תפריטים מותאמים אישית שיופיעו ב-Google Sheets. אפשר להשתמש גם בתפריטים מותאמים אישית ב-Google Docs, Google Slides ו-Google Forms. כאשר מגדירים פריט בתפריט מותאם אישית, יוצרים תווית טקסט ומחברים אותו לפונקציית Apps Script בפרויקט הסקריפט. לאחר מכן תוכלו להוסיף את התפריט לממשק המשתמש כך שיופיע ב-Google Sheets:
כשמשתמש לוחץ על פריט בתפריט מותאם אישית, הפונקציה Apps Script ששייכתם לו מופעלת. זוהי דרך מהירה להריץ פונקציות של Apps Script בלי לפתוח את עורך הסקריפטים. הוא גם מאפשר למשתמשים אחרים בגיליון האלקטרוני להפעיל את הקוד בלי שיצטרכו לדעת איך הוא פועל או איך Apps Script עובד. עבורם, זה רק פריט נוסף בתפריט.
פריטי תפריט מותאמים אישית מוגדרים בפונקציה onOpen() טריגר פשוט, שעליה תלמדו בקטע הבא.
4. הפונקציה onOpen()
טריגרים פשוטים ב-Apps Script מספקים דרך להריץ קוד Apps Script ספציפי בתגובה לתנאים או לאירועים מסוימים. כשיוצרים טריגר, מגדירים אילו אירועים יפעילו את הטריגר ומספקים פונקציה של Apps Script שפועלת עבור האירוע.
onOpen() הוא דוגמה לטריגר פשוט. קל להגדיר אותן; כל מה שצריך לעשות זה לכתוב פונקציה ב-Apps Script בשם onOpen(), ו-Apps Script יריץ אותה בכל פעם שהגיליון האלקטרוני המשויך ייפתח או ייטען מחדש.
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
/* ... */
}
יישום
יש ליצור תפריט מותאם אישית.
- צריך להחליף את הקוד בפרויקט הסקריפט שלך:
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Book-list')
.addItem('Load Book-list', 'loadBookList')
.addToUi();
}
- שומרים את פרויקט הסקריפט.
בדיקת קוד
כדאי לבדוק איך הקוד הזה עובד. ב-onOpen(), בשורה הראשונה נעשה שימוש בשיטה getUi() כדי לקבל אובייקט Ui שמייצג את ממשק המשתמש של הגיליון האלקטרוני הפעיל שאליו משויך הסקריפט הזה.
שלושת הקווים הבאים יוצרים את התפריט (Book-list), מוסיפים פריט לתפריט (Load Book-list) ואז מוסיפים את התפריט לממשק הגיליון האלקטרוני. הדבר נעשה בשיטות createMenu(caption), addItem(caption, functionName) ו-addToUi(), בהתאמה.
השיטה addItem(caption, functionName) יוצרת חיבור בין תווית הפריט בתפריט לבין הפונקציה Apps Script שפועלת כשבוחרים את האפשרות בתפריט. במקרה כזה, בחירה באפשרות Load Book-list בתפריט תגרום ל-Sheets לנסות להפעיל את הפונקציה loadBookList() (שעדיין לא קיימת).
תוצאות
הפעילו את הפונקציה הזו עכשיו כדי לראות שהיא פועלת:
- ב-Google Sheets טוענים מחדש את הגיליון האלקטרוני. הערה: בדרך כלל הפעולה הזו סוגרת את הכרטיסייה בעורך הסקריפטים.
- פותחים מחדש את עורך הסקריפטים: בוחרים כלים > עורך סקריפטים.
לאחר טעינת הגיליון האלקטרוני מחדש, התפריט החדש Book-list אמור להופיע בסרגל התפריטים:
לחיצה על רשימת הספרים תציג את התפריט שנפתח:
הקטע הבא יוצר את הקוד לפונקציה loadBookList() ומוסיף דרך אחת שבה אפשר לבצע פעולות בנתונים בסקריפט של Apps: קריאת גיליונות אלקטרוניים אחרים.
5. ייבוא נתונים מגיליון אלקטרוני
כעת, לאחר שיצרתם תפריט מותאם אישית, תוכלו ליצור פונקציות שניתן להפעיל בלחיצה על פריט התפריט.
כרגע, בתפריט המותאם אישית Book-list יש פריט אחד בתפריט: Load Book-list. הפונקציה שנקראת כשבוחרים את פריט התפריט Load Book-list, loadBookList(), לא קיימת בסקריפט שלך, כך שהבחירה ב-Book-list > טעינת רשימת ספרים תגרום לשגיאה:
כדי לתקן את השגיאה הזו, צריך להטמיע את הפונקציה loadBookList().
יישום
ברצונך שהפריט החדש בתפריט ימלא את הגיליון האלקטרוני בנתונים, לכן עליך ליישם את loadBookList() כדי לקרוא נתוני ספרים מגיליון אלקטרוני אחר ולהעתיק אותו לגיליון הבא:
- צריך להוסיף את הקוד הבא לסקריפט של
onOpen():
/**
* Creates a template book list based on the
* provided 'codelab-book-list' sheet.
*/
function loadBookList(){
// Gets the active sheet.
var sheet = SpreadsheetApp.getActiveSheet();
// Gets a different spreadsheet from Drive using
// the spreadsheet's ID.
var bookSS = SpreadsheetApp.openById(
"1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo"
);
// Gets the sheet, data range, and values of the
// spreadsheet stored in bookSS.
var bookSheet = bookSS.getSheetByName("codelab-book-list");
var bookRange = bookSheet.getDataRange();
var bookListValues = bookRange.getValues();
// Add those values to the active sheet in the current
// spreadsheet. This overwrites any values already there.
sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth())
.setValues(bookListValues);
// Rename the destination sheet and resize the data
// columns for easier reading.
sheet.setName("Book-list");
sheet.autoResizeColumns(1, 3);
}
- שומרים את פרויקט הסקריפט.
בדיקת קוד
כיצד פועלת פונקציה זו? הפונקציה loadBookList() משתמשת בשיטות בעיקר מהכיתות Spreadsheet, Sheet ו-Range שהושקו בקודקי הקוד הקודמים. בהתאם לקונספטים האלה, אפשר לחלק את הקוד של loadBookList() ל-4 הקטעים הבאים:
1: זיהוי גיליון היעד
בשורה הראשונה נעשה שימוש ב-SpreadsheetApp.getActiveSheet() כדי לקבל הפניה לאובייקט הגיליון הנוכחי, ומאוחסנת בו במשתנה sheet. זה הגיליון שאליו הנתונים יועתקו.
2: זיהוי נתוני המקור
השורות הבאות מגדירות ארבעה משתנים המתייחסות לנתוני המקור שאתם מאחזרים:
bookSSשומרת הפניה לגיליון האלקטרוני שממנו הקוד קורא נתונים. הקוד מוצא את הגיליון האלקטרוני לפי מזהה הגיליון האלקטרוני שלו. בדוגמה הזו, סיפקנו את המזהה של גיליון אלקטרוני מקור שממנו רוצים לקרוא, ופותחים את הגיליון האלקטרוני בשיטהSpreadsheetApp.openById(id).bookSheetשומרת הפניה לגיליון בתוךbookSSהמכיל את הנתונים הרצויים. הקוד מזהה את הגיליון שממנו יש לקרוא, בשםcodelab-book-list.bookRangeשומרת קובץ עזר למגוון נתונים ב-bookSheet. השיטהSheet.getDataRange()מחזירה את הטווח שמכיל את כל התאים שאינם ריקים בגיליון. זו דרך קלה לוודא שאתם מקבלים טווח שמכסה את כל הנתונים בגיליון, בלי לכלול שורות ועמודות ריקות.bookListValuesהוא מערך דו-ממדי המכיל את כל הערכים שהתקבלו מהתאים ב-bookRange. השיטהRange.getValues()יוצרת את המערך הזה על ידי קריאת הנתונים מגיליון המקור.
3: העתקת הנתונים מהמקור ליעד
קטע הקוד הבא מעתיק את נתוני bookListValues ל-sheet ולאחר מכן משנה את השם של הגיליון:
- נעשה שימוש ב-
Sheet.getRange(row, column, numRows, numColumns)כדי לזהות היכן להעתיק את הנתונים בsheet. - השיטות
Range.getHeight()ו-Range.getWidth()משמשות למדידת גודל הנתונים ולהגדרת טווח יעד של אותם מאפיינים. Range.setValues(values)מעתיק את המערך הדו-ממדי שלbookListValuesלטווח היעד, שמחליף את כל הנתונים שכבר קיימים.
4: עיצוב גיליון היעד
הסמל Sheet.setName(name) משמש לשינוי השם של גיליון היעד ל-Book-list. בשורה האחרונה בפונקציה נעשה שימוש ב-Sheet.autoResizeColumns(startColumn, numColumns) כדי לשנות את גודל שלוש העמודות הראשונות בגיליון היעד, וכך לקרוא את הנתונים החדשים בקלות רבה יותר.
תוצאות
תוכלו לראות את הפונקציה הזו בפעולה. ב-Google Sheets, בוחרים באפשרות Book-list > יש לטעון את רשימת הספרים כדי להפעיל את הפונקציה למילוי הגיליון האלקטרוני:
עכשיו יש לכם גיליון עם רשימה של כותרים, מחברים ומספרי ISBN בני 13 ספרות. בקטע הבא לומדים איך לשנות ולעדכן את הנתונים ברשימת הספרים הזו באמצעות מניפולציה של תפריטים ותפריטים מותאמים אישית.
6. סקירה כללית: ניקוי הנתונים בגיליון האלקטרוני
מעכשיו יש לכם פרטי ספר בגיליון. כל שורה מתייחסת לספר ספציפי, ולציין את הכותרת, המחבר ומספר ה-ISBN בעמודות נפרדות. עם זאת, ייתכן שיהיו כמה בעיות בנתונים הגולמיים האלה:
- בחלק מהשורות, הכותר והמחבר ממוקמים יחד בעמודת הכותרת, ומקושרים באמצעות פסיק או המחרוזת " לפי ".
- בחלק מהשורות חסרה הכותרת או המחבר של הספר.
בסעיפים הבאים תתקנו את הבעיות האלה על ידי ניקוי הנתונים. עבור הבעיה הראשונה, צריך ליצור פונקציות שקוראים את עמודת הכותרת ומפצלות את הטקסט בכל פעם שנמצאים פסיק או מירכאות; לפי &תו מפריד; מתבצע הפרדה של מחרוזת המשנה והמחבר המתאימים בכותרת. עבור הגיליון השני, אתם כותבים קוד שמחפש באופן אוטומטי מידע על ספר חסר באמצעות ממשק API חיצוני, ומוסיף את המידע הזה לגיליון.
7. הוספת מנות לתפריט
צריך ליצור שלושה פריטים בתפריט כדי לשלוט בפעולות הניקוי של הנתונים שרוצים להטמיע.
יישום
יש לעדכן את onOpen() כך שיכלול את הפריטים הנוספים בתפריט שדרושה לך. בצע את הפעולות הבאות:
- יש לעדכן את הקוד
onOpen()בפרויקט הסקריפט שלך:
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Book-list')
.addItem('Load Book-list', 'loadBookList')
.addSeparator()
.addItem(
'Separate title/author at first comma', 'splitAtFirstComma')
.addItem(
'Separate title/author at last "by"', 'splitAtLastBy')
.addSeparator()
.addItem(
'Fill in blank titles and author cells', 'fillInTheBlanks')
.addToUi();
}
- שומרים את פרויקט הסקריפט.
- בעורך הסקריפטים, בוחרים באפשרות
onOpenמתוך רשימת הפונקציות ולוחצים על הפעלה. הפעולה הזו תפעיל אתonOpen()כדי לבנות מחדש את תפריט הגיליון האלקטרוני, כך שלא יהיה צורך לטעון מחדש את הגיליון האלקטרוני.
בקוד החדש הזה, השיטה Menu.addSeparator() יוצרת מחלק אופקי בתפריט כדי לשמור על הסדר בקבוצות של פריטים הקשורים לתפריט. לאחר מכן נוספים פריטי התפריט החדשים מתחתיו, עם התוויות Separate title/author at first comma, Separate title/author at last "by" וFill in blank titles and author cells.
תוצאות
בגיליון האלקטרוני, לוחצים על התפריט Book-list כדי להציג את אפשרויות התפריט החדשות:
לחיצה על הפריטים החדשים גורמת לשגיאה, כי לא הטמעתם את הפונקציות התואמות שלהם, אז כדאי לעשות את זה עכשיו.
8. פיצול הטקסט מפרידים בפסיקים
מערך הנתונים שייבאת לגיליון האלקטרוני כולל מספר תאים שבהם המחבר והכותרת משולבים באופן שגוי בתא אחד באמצעות פסיק:
פיצול מחרוזות טקסט לעמודות נפרדות הוא משימה נפוצה בגיליון אלקטרוני. Google Sheets מספק פונקציה SPLIT() שמחלקת מחרוזות לעמודות. עם זאת, לעיתים קרובות יש מערכי נתונים עם בעיות שלא ניתן לפתור בקלות באמצעות Sheets' פונקציות מובנות. במקרים כאלה, תוכלו לכתוב קוד של Apps Script כדי לבצע את הפעולות המורכבות הנדרשות לניקוי ולארגון הנתונים.
כדי להתחיל לנקות את הנתונים, צריך להטמיע פונקציה בשם splitAtFirstComma() שמחלקת את המחבר ואת שם הספר לתאים התואמים, אם נמצאו פסיקים.
הפונקציה splitAtFirstComma() צריכה לבצע את הפעולות הבאות:
- קבלת הטווח שמייצג את התאים שנבחרו.
- בודקים אם לתאים שבטווח יש פסיק.
- כדי למצוא פסיקים, יש לפצל את המחרוזת לשתי מחרוזות משנה (ורק שתיים) במיקום של הפסיק הראשון. כדי לפשט את הדברים, אפשר להניח שכל פסיק מציין תבנית &של מירכאות;[authors], [title]". אפשר גם להניח אם יש כמה פסיקים בתא, מתאים לפסיק הראשון בפסיק.
- מזינים את מחרוזת המשנה בתור התוכן החדש של הכותר ותאי המחבר המתאימים.
יישום
כדי ליישם את השלבים האלה, עליכם להשתמש באותן שיטות של שירות גיליונות אלקטרוניים שהשתמשתם בהן בעבר, אבל תצטרכו גם להשתמש ב-JavaScript כדי להשפיע על נתוני המחרוזת. איך עושים את זה?
- בעורך Apps Script, מוסיפים את הפונקציה הבאה לסוף של פרויקט הסקריפט:
/**
* Reformats title and author columns by splitting the title column
* at the first comma, if present.
*/
function splitAtFirstComma(){
// Get the active (currently highlighted) range.
var activeRange = SpreadsheetApp.getActiveRange();
var titleAuthorRange = activeRange.offset(
0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);
// Get the current values of the selected title column cells.
// This is a 2D array.
var titleAuthorValues = titleAuthorRange.getValues();
// Update values where commas are found. Assumes the presence
// of a comma indicates an "authors, title" pattern.
for (var row = 0; row < titleAuthorValues.length; row++){
var indexOfFirstComma =
titleAuthorValues[row][0].indexOf(", ");
if(indexOfFirstComma >= 0){
// Found a comma, so split and update the values in
// the values array.
var titlesAndAuthors = titleAuthorValues[row][0];
// Update the title value in the array.
titleAuthorValues[row][0] =
titlesAndAuthors.slice(indexOfFirstComma + 2);
// Update the author value in the array.
titleAuthorValues[row][1] =
titlesAndAuthors.slice(0, indexOfFirstComma);
}
}
// Put the updated values back into the spreadsheet.
titleAuthorRange.setValues(titleAuthorValues);
}
- שומרים את פרויקט הסקריפט.
בדיקת קוד
נבחן את הקוד החדש המורכב משלושה חלקים עיקריים:
1: אחזור של ערכי הכותרת המודגשים
שלוש השורות הראשונות מגדירות שלושה משתנים שקשורים לנתונים הנוכחיים שבגיליון:
activeRangeמייצג את הטווח שהמשתמש הדגיש כרגע בעת הפעלת הפונקציהsplitAtFirstComma(). כדי שהתרגיל הזה יהיה פשוט, נוכל להניח שהמשתמש עושה זאת רק כאשר הוא מדגיש תאים בעמודה A.titleAuthorRangeמייצג טווח חדש שמכסה את אותם התאים כמוactiveRange, אבל כולל גם עמודה אחת מימין.titleAuthorRangeנוצר באמצעות השיטהRange.offset(rowOffset, columnOffset, numRows, numColumns). הקוד צריך את הטווח המורחב הזה מפני שהוא מחייב מקום להזין את המחברים שמוצאים בעמודת הכותרת.titleAuthorValuesהוא מערך דו-ממדי של נתונים שחולצו מ-titleAuthorRangeבאמצעותRange.getValues().
2: בדיקת כל כותרת ופיצול במפריד הפסיק הראשון שנמצא
בקטע הבא נבדק הערכים ב-titleAuthorValues כדי למצוא פסיקים. JavaScript עבור לולאה משמש לבדיקת כל הערכים בעמודה הראשונה של titleAuthorValues. כשנמצא מחרוזת משנה פסיק (", ") באמצעות שיטת המחרוזת באינדקס של JavaScript (), הקוד מבצע את הפעולות הבאות:
- הערך של מחרוזת התא הועתק אל המשתנה
titlesAndAuthors. - מיקום הפסקה נקבע באמצעות השיטה אינדקס של מחרוזת JavaScript() .
- השיטה של מחרוזת מחרוזת JavaScript() נקראת פעמיים כדי לקבל את מחרוזת המשנה לפני המפריד והפסיק אחרי המפריד.
- מחרוזות המשנה מועתקות חזרה למערך ה-2D של ValueAuthorValues, תוך החלפה של הערכים הקיימים במיקום הזה. מאחר שאנחנו מניחים בדפוס &&&;[authors], [title]" דוגמת הסידור של שתי מחרוזות המשנה הופכת את הכותרת לעמודה הראשונה ולמחברים בשנייה.
הערה: אם הקוד לא מוצא פסיק, הוא משאיר את הנתונים בשורה ללא שינוי.
3: העתקת הערכים החדשים בחזרה לגיליון
לאחר שנבדוק את כל הערכים של תא הכותרת, מערך ה-2D המעודכן של titleAuthorValues יועתק בחזרה לגיליון האלקטרוני באמצעות שיטת Range.setValues(values).
תוצאות
עכשיו אפשר לראות את ההשפעות של הפונקציה splitAtFirstComma(). כדי להפעיל את הרכיב, בוחרים בפריט התפריט הפרדה בין שם הפריט או המחבר בפסיק הראשון אחרי שבוחרים...
...תא אחד:
...או מספר תאים:
סיימתם ליצור פונקציה של Apps Script שמעבדת נתונים של Sheets. בשלב הבא, תטמיעו את הפונקציה השנייה של המפצל.
9. הפיצול של הטקסט & "לפי מפריד;
כשמעיינים בנתונים המקוריים אפשר לראות בעיה אחרת. בדיוק כמו שחלק מהכותרות של המחברים והשמות של תאים בתא אחד הם "[authors], [title]", פורמט התאים של הכותב והכותרת הוא "[title] לפי [authors]":
יישום
אפשר לפתור את הבעיה באמצעות אותה טכניקה מהקטע האחרון, על ידי יצירת פונקציה בשם splitAtLastBy(). לפונקציה הזו יש תפקיד דומה ל-splitAtFirstComma() – ההבדל היחיד הוא שהיא מחפשת דפוס טקסט שונה מעט. כדי ליישם את הפונקציה הזו, מבצעים את הפעולות הבאות:
- בעורך Apps Script, מוסיפים את הפונקציה הבאה לסוף של פרויקט הסקריפט:
/**
* Reformats title and author columns by splitting the title column
* at the last instance of the string " by ", if present.
*/
function splitAtLastBy(){
// Get the active (currently highlighted) range.
var activeRange = SpreadsheetApp.getActiveRange();
var titleAuthorRange = activeRange.offset(
0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);
// Get the current values of the selected title column cells.
// This is a 2D array.
var titleAuthorValues = titleAuthorRange.getValues();
// Update values where " by " substrings are found. Assumes
// the presence of a " by " indicates a "title by authors"
// pattern.
for(var row = 0; row < titleAuthorValues.length; row++){
var indexOfLastBy =
titleAuthorValues[row][0].lastIndexOf(" by ");
if(indexOfLastBy >= 0){
// Found a " by ", so split and update the values in
// the values array.
var titlesAndAuthors = titleAuthorValues[row][0];
// Update the title value in the array.
titleAuthorValues[row][0] =
titlesAndAuthors.slice(0, indexOfLastBy);
// Update the author value in the array.
titleAuthorValues[row][1] =
titlesAndAuthors.slice(indexOfLastBy + 4);
}
}
// Put the updated values back into the spreadsheet.
titleAuthorRange.setValues(titleAuthorValues);
}
- שומרים את פרויקט הסקריפט.
בדיקת קוד
יש כמה הבדלים מרכזיים בין הקוד הזה לבין splitAtFirstComma():
- מחרוזת המשנה "
by" משמשת כמפריד מחרוזת, במקום ב-",". - כאן נעשה שימוש בשיטת JavaScript
String.lastIndexOf(substring)במקום ב-String.indexOf(substring). כלומר, אם יש כמה מחרוזות & ;by" במחרוזת הראשונית, כל המחרוזות & &האחרונות;by" נחשבות כחלק מהכותרת. - לאחר פיצול המחרוזת, מחרוזת המשנה הראשונה מוגדרת ככותרת והשנייה היא כמחבר (זהו הסדר ההפוך מ-
splitAtFirstComma()).
תוצאות
עכשיו אפשר לראות את ההשפעות של הפונקציה splitAtLastBy(). מנסים להריץ את הפריט על-ידי בחירה באפשרות שם הספר/המחבר האחרון בסוף &"לפי &מירכאות; לאחר בחירת...
...תא אחד:
...או מספר תאים:
השלמת את הסעיף הזה ב-Codelab. עכשיו אפשר להשתמש ב-Apps Script כדי לקרוא ולשנות נתוני מחרוזת בגיליון, ולהשתמש בתפריטים מותאמים אישית כדי לבצע פקודות שונות ב-Apps Script.
בקטע הבא לומדים איך לשפר את מערך הנתונים הזה עוד יותר על ידי מילוי תאים ריקים באמצעות נתונים שנשלפים מ-API ציבורי.
10. סקירה כללית: קבלת נתונים מממשקי API ציבוריים
עד עכשיו צמצמת את מערך הנתונים כדי לתקן כמה בעיות הקשורות לפורמט ולמחבר, אבל במערך הנתונים עדיין חסר מידע. הוא מודגש בתאים הבאים:
לא ניתן לקבל את הנתונים החסרים על ידי שימוש בפעולות מחרוזת בנתונים הקיימים. במקום זאת, תצטרכו להשיג את הנתונים החסרים ממקור אחר. כדי לעשות זאת ב-Apps Script, צריך לבקש מידע מממשקי API חיצוניים שיכולים לספק נתונים נוספים.
ממשקי API הם ממשקי תכנות יישומים. זהו מונח כללי, אך זהו שירות שתוכנות וסקריפטים יכולים להתקשר אליו כדי לבקש מידע או לבצע פעולות מסוימות. בקטע הזה, אתם קריאה ל-API שזמין לציבור כדי לבקש פרטי ספר שאפשר להוסיף לתאים הריקים בגיליון.
בקטע הזה תוכלו:
- בקשת נתוני ספר ממקור API חיצוני.
- חלץ את פרטי הכותרת והמחבר מהנתונים שהוחזרו וכתוב אותם בגיליון האלקטרוני.
11. אחזור נתונים חיצוניים באמצעות UrlFetch
לפני שאתם מתעמקים בקוד שפועל ישירות עם הגיליון האלקטרוני, אתם יכולים לקבל מידע על עבודה עם ממשקי API חיצוניים ב-Apps Script. לשם כך, עליכם ליצור פונקציית עזרה ספציפית לשליחת בקשה לקבלת פרטי ספר דרך ה-Open Directory API הציבורי.
פונקציית העזרה שלנו, fetchBookData_(ISBN), משתמשת במספר ISBN של ספר בן 13 ספרות כפרמטר ומחזירה נתונים על אותו ספר. הוא מתחבר ל-Open Directory API ומאחזר אותו, ולאחר מכן מנתח את אובייקט JSON שהוחזר.
יישום
כדי להטמיע את פונקציית העזרה הזו, אפשר לבצע את הפעולות הבאות:
- בעורך Apps Script, מוסיפים את הקוד הבא לסוף הסקריפט:
/**
* Helper function to retrieve book data from the Open Library
* public API.
*
* @param {number} ISBN - The ISBN number of the book to find.
* @return {object} The book's data, in JSON format.
*/
function fetchBookData_(ISBN){
// Connect to the public API.
var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
+ ISBN + "&jscmd=details&format=json";
var response = UrlFetchApp.fetch(
url, {'muteHttpExceptions': true});
// Make request to API and get response before this point.
var json = response.getContentText();
var bookData = JSON.parse(json);
// Return only the data we're interested in.
return bookData['ISBN:' + ISBN];
}
- שומרים את פרויקט הסקריפט.
בדיקת קוד
הקוד הזה מחולק לשני קטעים עיקריים:
1: בקשת ה-API
בשתי השורות הראשונות, fetchBookData_(ISBN) מתחבר אל ה-Open Directory API הציבורי באמצעות ה-API של נקודת הקצה וב-Apps Script של אחזור כתובות URL.
המשתנה url הוא רק מחרוזת URL, כמו כתובת אינטרנט. היא מצביעה על מיקום בשרתים של הספרייה הפתוחה. הוא כולל גם שלושה פרמטרים (bibkeys, jscmd ו-format) שמסבירים לשרתים של הספרייה הפתוחה איזה מידע אתם מבקשים ואיך לבנות את התגובה. במקרה כזה, תספקו את מספר ה-ISBN של הספר ותבקשו מידע מפורט כדי להחזיר אותו בפורמט JSON.
לאחר יצירת מחרוזת כתובת האתר, הקוד שולח בקשה למיקום ומקבל תגובה. הדבר נעשה בשיטת UrlFetchApp.fetch(url, params). הוא שולח בקשה למידע אל כתובת ה-URL החיצונית שאתם מספקים, ומאוחסן בה התגובה שהתקבלה במשתנה response. בנוסף לכתובת ה-URL, הקוד מגדיר את הפרמטר האופציונלי muteHttpExceptions כ-true. הגדרה זו פירושה שהקוד לא יופסק אם הבקשה תגרום לשגיאה ב-API. במקום זאת, תוחזר שגיאת השגיאה.
הבקשה מחזירה אובייקט HTTPResponse המאוחסן במשתנה response. תגובות HTTP כוללות קוד תגובה, כותרות HTTP ותוכן התגובה הראשי. המידע שמעניין כאן הוא תוכן ה-JSON הראשי, לכן הקוד חייב לחלץ אותו ולאחר מכן לנתח את ה-JSON כדי לאתר ולהחזיר את המידע הרצוי.
2: ניתוח התגובה ב-API והחזרת המידע הרצוי
כשלוש שורות הקוד האחרונות, השיטה HTTPResponse.getContentText() מחזירה את התוכן הראשי של התגובה כמחרוזת. מחרוזת זו היא בפורמט JSON, אך Open Directory API מגדיר את התוכן והפורמט המדויקים. השיטה JSON.parse(jsonString) ממירה את מחרוזת ה-JSON לאובייקט JavaScript כדי שניתן יהיה לחלץ בקלות חלקים שונים של הנתונים. לבסוף, הפונקציה מחזירה את הנתונים התואמים למספר ה-ISBN של הספר.
תוצאות
עכשיו, אחרי שהטמעתם את fetchBookData_(ISBN), פונקציות אחרות בקוד יכולות למצוא מידע על כל ספר באמצעות מספר ה-ISBN שלו. הפונקציה הזו תעזור לכם למלא את התאים בגיליון האלקטרוני.
12. כתיבת נתוני API לגיליון אלקטרוני
עכשיו אפשר להטמיע פונקציה ב-fillInTheBlanks() שמבצעת את הפעולות הבאות:
- זיהוי נתוני הכותר והמחבר החסרים בטווח הנתונים הפעיל.
- איחזור נתונים חסרים של ספר ספציפי באמצעות קריאה ל-Open Directory API באמצעות שיטת העזרה של
fetchBookData_(ISBN). - מעדכנים את הערכים החסרים של הכותרים או המחברים בתאים המתאימים.
יישום
כדי ליישם את הפונקציה החדשה הזו:
- בעורך Apps Script, מוסיפים את הקוד הבא לסוף פרויקט הסקריפטים:
/**
* Fills in missing title and author data using Open Library API
* calls.
*/
function fillInTheBlanks(){
// Constants that identify the index of the title, author,
// and ISBN columns (in the 2D bookValues array below).
var TITLE_COLUMN = 0;
var AUTHOR_COLUMN = 1;
var ISBN_COLUMN = 2;
// Get the existing book information in the active sheet. The data
// is placed into a 2D array.
var dataRange = SpreadsheetApp.getActiveSpreadsheet()
.getDataRange();
var bookValues = dataRange.getValues();
// Examine each row of the data (excluding the header row).
// If an ISBN is present, and a title or author is missing,
// use the fetchBookData_(isbn) method to retrieve the
// missing data from the Open Library API. Fill in the
// missing titles or authors when they're found.
for(var row = 1; row < bookValues.length; row++){
var isbn = bookValues[row][ISBN_COLUMN];
var title = bookValues[row][TITLE_COLUMN];
var author = bookValues[row][AUTHOR_COLUMN];
if(isbn != "" && (title === "" || author === "") ){
// Only call the API if you have an ISBN number and
// either the title or author is missing.
var bookData = fetchBookData_(isbn);
// Sometimes the API doesn't return the information needed.
// In those cases, don't attempt to update the row.
if (!bookData || !bookData.details) {
continue;
}
// The API might not return a title, so only fill it in
// if the response has one and if the title is blank in
// the sheet.
if(title === "" && bookData.details.title){
bookValues[row][TITLE_COLUMN] = bookData.details.title;
}
// The API might not return an author name, so only fill it in
// if the response has one and if the author is blank in
// the sheet.
if(author === "" && bookData.details.authors
&& bookData.details.authors[0].name){
bookValues[row][AUTHOR_COLUMN] =
bookData.details.authors[0].name;
}
}
}
// Insert the updated book data values into the spreadsheet.
dataRange.setValues(bookValues);
}
- שומרים את פרויקט הסקריפט.
בדיקת קוד
הקוד מחולק לשלושה קטעים:
1: קריאת המידע הקיים על הספר
שלוש השורות הראשונות של הפונקציה מגדירות קבועות כדי להקל על הקריאה של הקוד. בשתי השורות הבאות, המשתנה bookValues משמש לשמירת עותק של פרטי הספר בגיליון. הקוד יקרא מידע מ-bookValues, ישתמש ב-API כדי למלא מידע חסר ויכתוב את הערכים האלה בגיליון האלקטרוני.
2: אחזור מידע חסר באמצעות פונקציית העזרה
הקוד מופיע בכל שורה ב-bookValues כדי למצוא כותרות או מחברים חסרים. כדי לצמצם את מספר הקריאות ל-API תוך שיפור היעילות, הקוד קורא ל-API רק אם הם נכונים:
- לשורת ה-ISBN של השורה יש ערך.
- הכותרת או התא של המחבר בשורה ריקים.
אם התנאים מתקיימים, הקוד קורא ל-API באמצעות פונקציית העזרה של fetchBookData_(isbn) שהוטמעה בעבר, ושומר את התוצאה במשתנה bookData. עכשיו אמור להופיע בו המידע החסר שרוצים להוסיף לגיליון.
המשימה היחידה שנשארה היא להוסיף את הפרטים ב-bookData לגיליון האלקטרוני שלנו. עם זאת, יש אזהרה. לצערנו, ממשקי API ציבוריים כמו Open Directory Books API לא מכילים לפעמים את המידע שביקשת, או שלפעמים יש בעיה אחרת שמונעת ממנו לספק את המידע. אם מניחים שכל בקשת API תצליח, הקוד שלכם לא יהיה חזק מספיק כדי לטפל בשגיאות לא צפויות.
כדי לוודא שהקוד יכול לטפל בשגיאות API, הקוד חייב לבדוק את תגובת ה-API חוקית לפני הניסיון להשתמש בה. אחרי הזנת הקוד bookData, מתבצעת בדיקה פשוטה כדי לאמת ש-bookData ו-bookData.details קיימים לפני שמנסים לקרוא אותם. אם חסר אחד מהם, פירוש הדבר שה-API לא כלל את הנתונים הרצויים. במקרה כזה, הפקודה continue מורה לקוד לדלג על השורה הזו. אי אפשר למלא את התאים החסרים, אבל לפחות הסקריפט לא קורס.
3: כתיבת מידע מעודכן חזרה לגיליון
בחלק האחרון של הקוד יש בדיקות דומות כדי לאמת שה-API החזיר את פרטי הכותרת והמחבר. הקוד מעדכן את מערך bookValues רק אם הכותרת המקורית או התא של המחבר ריקים וה-API מחזיר ערך שניתן למקם שם.
היציאה מהלולאה מתבצעת לאחר בדיקת כל השורות בגיליון. השלב האחרון הוא לכתוב את מערך bookValues המעודכן עכשיו לגיליון האלקטרוני באמצעות Range.setValues(values).
תוצאות
עכשיו אפשר לסיים את ניקוי הנתונים של הספר. בצע את הפעולות הבאות:
- אם עדיין לא הדגישו את הטווח A2:A15 בגיליון, תוכלו לבחור באפשרות Book-list > יש להפריד בין הכותרות/הכותבים בפסיק הראשונה כדי לנקות את הבעיות הקשורות לפסיק.
- אם עדיין לא עשיתם זאת, מדגישים את הטווח A2:A15 בגיליון ובוחרים באפשרות Book-list > יש להפריד בין הכותרים/הכותבים בסוף &"לפי מירכאות;.
- כדי למלא את כל התאים שנותרו, בוחרים באפשרות רשימת ספרים > ממלאים כותרות ריקות ותאי מחברים:
13. סיכום
ברכות על השלמת Lab זה. למדת איך ליצור תפריטים מותאמים אישית כדי להפעיל חלקים שונים של הקוד של Apps Script. הסברת גם איך לייבא נתונים אל Google Sheets באמצעות שירותי Apps Script וממשקי API ציבוריים. זוהי פעולה נפוצה בעיבוד גיליונות אלקטרוניים, ו-Apps Script מאפשר לייבא נתונים ממגוון רחב של מקורות. לבסוף, ראינו איך אפשר להשתמש בשירותי Apps Script וב-JavaScript כדי לקרוא, לעבד ולהוסיף נתונים של גיליונות אלקטרוניים.
האם שיעור ה-Lab הזה הועיל לך?
מה למדת
- איך מייבאים נתונים מגיליון אלקטרוני של Google?
- איך יוצרים תפריט מותאם אישית בפונקציה
onOpen(). - איך לנתח ולשנות ערכים של נתוני מחרוזת.
- איך להתקשר לממשקי API ציבוריים באמצעות שירות אחזור כתובות URL.
- איך לנתח נתוני אובייקט JSON שאוחזרו ממקור API ציבורי.
המאמרים הבאים
שיעור ה-Lab הבא בפלייליסט הזה מעמיק יותר לגבי הפורמט של נתונים בגיליון אלקטרוני.
מאתרים את מעבדת הקוד הבאה בעיצוב נתונים.