היסודות של Apps Script עם Google Sheets #4: עיצוב נתונים

1. מבוא

אנחנו שמחים שהצטרפת לחלק הרביעי של Apps Scripts עם פלייליסט Google Code Code.

על ידי השלמת קוד Lab זה, תוכלו ללמוד איך לעצב את נתוני הגיליונות האלקטרוניים שלכם ב-Apps Script, ולכתוב פונקציות ליצירת גיליונות אלקטרוניים מאורגנים המלאים בנתונים בפורמט, שנשלפו מ-API ציבורי.

מה תלמדו

  • איך להחיל פעולות עיצוב שונות ב-Google Sheets בסקריפט של Apps.
  • כיצד להמיר רשימה של אובייקטים מסוג JSON ואת המאפיינים שלהם לגיליון מאורגן של נתונים באמצעות Apps Script.

לפני שמתחילים

זוהי גרסת הקוד הרביעית בסקריפט של Apps of Apps ב-Google Sheets. לפני התחלת שיעור ה-Codelab הזה, חשוב להקפיד להשלים את ה-codelabs הקודמים:

  1. רכיבי מאקרו ופונקציות מותאמות אישית
  2. גיליונות אלקטרוניים, גיליונות וטווחים
  3. עבודה עם נתונים

מה תצטרך להכין

  • היכרות עם הנושאים הבסיסיים של Apps Script שנבדקו בשיעורי הקוד הקודמים של הפלייליסט הזה.
  • היכרות בסיסית עם עורך Apps Script
  • היכרות בסיסית עם Google Sheets
  • יכולת לקרוא את Sheets A1 ב-Sheets
  • היכרות בסיסית עם JavaScript והString כיתה שלה

2. הגדרה

לפני שנמשיך, יש צורך בגיליון אלקטרוני עם נתונים מסוימים. כמו קודם לכן, סיפקנו גיליון נתונים שאפשר להעתיק מהתרגילים האלה. איך עושים את זה?

  1. לוחצים על הקישור הזה כדי להעתיק את גיליון הנתונים, ולאחר מכן לוחצים על יצירת עותק. הגיליון האלקטרוני החדש נמצא בתיקייה Google Drive בשם "Copy of Data format&&;.
  2. לוחצים על הכותרת של הגיליון האלקטרוני ומשנים אותו מ-"עותק של פורמט נתונים" ל-"פורמט נתונים". הגיליון אמור להיראות כך, עם מידע בסיסי על שלושת הסרטים הראשונים של מלחמת הכוכבים:

c4f49788ed82502b.png

  1. בוחרים באפשרות תוספים > Apps Script כדי לפתוח את עורך הסקריפטים.
  2. לוחצים על שם הפרויקט ב-Apps Script ומשנים אותו מ-"פרויקט ללא שם&&; ל-"פורמט נתונים." לוחצים על שינוי שם כדי לשמור את השינוי בשם הפריט.

באמצעות הגיליון האלקטרוני והפרויקט הזה, אתם מוכנים להתחיל בשיעור הקוד. יש לעבור לקטע הבא כדי להתחיל ללמוד על עיצוב בסיסי ב-Apps Script.

3. יצירת תפריט מותאם אישית

ב-Apps Script אפשר להחיל כמה שיטות עיצוב בסיסיות. התרגילים הבאים מראים כמה דרכים לעיצוב נתונים. כדי לעזור לך לשלוט בפעולות העיצוב, צור תפריט מותאם אישית עם הפריטים הדרושים לך. התהליך ליצירת תפריטים מותאמים אישית מתואר ב-Codec של עבודה עם נתונים, אבל לסכם אותו כאן שוב.

יישום

יש ליצור תפריט מותאם אישית.

  1. בעורך Apps Script, החליפו את הקוד בפרויקט הסקריפט ב:
/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the spreadsheet's user-interface object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
   .addItem('Format row header', 'formatRowHeader')
   .addItem('Format column header', 'formatColumnHeader')
   .addItem('Format dataset', 'formatDataset') 
  .addToUi();
}
  1. שומרים את פרויקט הסקריפט.
  2. בעורך הסקריפטים, בוחרים באפשרות onOpen מתוך רשימת הפונקציות ולוחצים על הפעלה. הפעולה הזו מפעילה את onOpen() כדי לבנות מחדש את תפריט הגיליון האלקטרוני, כך שאין צורך לטעון מחדש את הגיליון האלקטרוני.

בדיקת קוד

כדאי לבדוק איך הקוד הזה עובד. ב-onOpen(), השורה הראשונה משתמשת בשיטה getUi() כדי לקבל אובייקט Ui שמייצג את ממשק המשתמש של הגיליון האלקטרוני הפעיל, שאליו משויך הסקריפט הזה.

השורות הבאות יוצרות תפריט (Quick formats), מוסיפים פריטים לתפריט (Format row header, Format column header ו-Format dataset) לתפריט, ומוסיפים את התפריט לממשק הגיליון האלקטרוני. הדבר נעשה בשיטות createMenu(caption), addItem(caption, functionName) ו-addToUi(), בהתאמה.

השיטה addItem(caption, functionName) יוצרת חיבור בין תווית הפריט בתפריט לבין פונקציית Apps Script שפועלת כשבוחרים את האפשרות בתפריט. לדוגמה, בחירה בתפריט Format row header תגרום ל-Sheets לנסות להפעיל את הפונקציה formatRowHeader() (שעדיין לא קיימת).

תוצאות

בגיליון האלקטרוני, לוחצים על התפריט Quick formats כדי להציג את אפשרויות התפריט החדשות:

1d639a41f3104864.png

לחיצה על הפריטים האלה גורמת לשגיאה כי לא הטמעת את הפונקציות התואמות שלהם, אז כדאי לעשות את זה בשלב הבא.

4. עיצוב של שורת כותרת

מערכי נתונים בגיליונות אלקטרוניים כוללים לעיתים קרובות שורות כותרת כדי לזהות את הנתונים בכל עמודה. כדאי לעצב את שורות הכותרת כדי להפריד ביניהן באופן חזותי משאר הנתונים בגיליון האלקטרוני.

במעבדת הקוד הראשונה, יצרתם מאקרו לכותרת והתאמתם את הקוד. כאן אפשר לעצב שורת כותרת חדשה מאפס באמצעות Apps Script. שורת הכותרת שתיצרו תדגיש את טקסט הכותרת, תצבע את הרקע בצבע כחול-ירוק כהה, תוסיפו את הטקסט ללבן ותוסיף גבולי חלק.

יישום

כדי ליישם את פעולת העיצוב, משתמשים באותן שיטות של שירות גיליונות אלקטרוניים שבהם השתמשתם בעבר, אבל עכשיו אתם משתמשים גם בחלק משיטות העיצוב של השירות. איך עושים את זה?

  1. בעורך Apps Script, מוסיפים את הפונקציה הבאה לסוף של פרויקט הסקריפט:
/**
 * Formats top row of sheet using our header row style.
 */
function formatRowHeader() {
  // Get the current active sheet and the top row's range.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
 
  // Apply each format to the top row: bold white text,
  // blue-green background, and a solid black border
  // around the cells.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}
  1. שומרים את פרויקט הסקריפט.

בדיקת קוד

כמו משימות עיצוב רבות, קוד Apps Script כדי ליישם אותו הוא פשוט. שתי השורות הראשונות משתמשות בשיטות שראיתם בעבר כדי לקבל הפניה לגיליון הפעיל הנוכחי (sheet) ולשורה העליונה של הגיליון (headerRange). השיטה Sheet.getRange(row, column, numRows, numColumns) מציינת את השורה העליונה, כולל רק העמודות שבנתונים האלה. השיטה Sheet.getLastColumn() מחזירה את אינדקס העמודות של העמודה האחרונה שמכילה נתונים בגיליון. בדוגמה שלנו, זו העמודה E&#39 (url).

שאר הקוד מפעיל שיטות שונות של Range כדי להחיל את אפשרויות העיצוב על כל התאים ב-headerRange. כדי שיהיה קל לקרוא את הקוד, אנחנו משתמשים בשרשור של שיטות כדי להתקשר לכל אחת משיטות העיצוב בנפרד:

השיטה האחרונה כוללת כמה פרמטרים, לכן כדאי לבדוק מה כל אחד מהם עושה. ארבעת הפרמטרים הראשונים שמופיעים כאן (הכול מוגדרים כ-true) מציינים ב-Apps Script את הגבול שיש להוסיף מעל, מתחת ומצד שמאל של הטווח. הפרמטרים החמישיים והשישית (null ו-null) מפנים את Apps Script כדי להימנע משינוי קווי הגבול בטווח שנבחר. הפרמטר השביעי (null) מציין את צבע הגבול המוגדר כברירת מחדל לשחור. לבסוף, הפרמטר האחרון מציין את סוג סגנון הגבול לשימוש, ונלקח מהאפשרויות שסופקו על ידי SpreadsheetApp.BorderStyle.

תוצאות

כדי לראות את פונקציית העיצוב, אפשר לבצע את הפעולות הבאות:

  1. אם עדיין לא עשית זאת, שמור את פרויקט הסקריפט שלך בעורך הסקריפטים של Apps.
  2. לוחצים על פריט התפריט פורמטים מהירים > עיצוב כותרת שורה.

התוצאות צריכות להיראות כך:

a1a63770c2c3becc.gif

מעכשיו מתבצעת משימה של עיצוב אוטומטי. הקטע הבא מחיל את אותה שיטה כדי ליצור סגנון פורמט אחר בכותרות העמודות.

5. עיצוב כותרת עמודה

אם אתם יכולים ליצור כותרת שורה מותאמת אישית, תוכלו גם ליצור כותרת עמודה. כותרות של עמודות מגדילות את הקריאה של מערכי נתונים מסוימים. לדוגמה, אפשר לשפר את העמודה titles בגיליון האלקטרוני הזה באמצעות אפשרויות הפורמט הבאות:

  • הדגשת הטקסט
  • הפיכת הטקסט לנטוי
  • הוספת גבולות של תאים
  • הוספת היפר-קישורים, באמצעות תוכן העמודות כתובת URL. לאחר ההוספה של ההיפר-קישורים האלה, אפשר להסיר את העמודה כתובת URL כדי לנקות את הגיליון.

בשלב הבא, מיישמים את הפונקציה formatColumnHeader() כדי להחיל את השינויים האלה על העמודה הראשונה בגיליון. כדי שיהיה לכם קל יותר לקרוא את הקוד, תצטרכו להטמיע גם שתי פונקציות מסייעות.

יישום

כמו קודם, צריך להוסיף פונקציה כדי להפוך את העיצוב של כותרות העמודות לאוטומטיות. איך עושים את זה?

  1. בעורך Apps Script, יש להוסיף את הפונקציה formatColumnHeader() הבאה לסוף פרויקט הסקריפט:
/**
 * Formats the column header of the active sheet.
 */ 
function formatColumnHeader() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get total number of rows in data range, not including
  // the header row.
  var numRows = sheet.getDataRange().getLastRow() - 1;
  
  // Get the range of the column header.
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
  
  // Apply text formatting and add borders.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  // Call helper method to hyperlink the first column contents
  // to the url column contents.
  hyperlinkColumnHeaders_(columnHeaderRange, numRows); 
}
  1. יש להוסיף את הפונקציות הבאות של ה-Assistant לסוף פרויקט הסקריפט, אחרי הפונקציה formatColumnHeader():
/**
 * Helper function that hyperlinks the column header with the
 * 'url' column contents. The function then removes the column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing.
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values.
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Helper function that goes through the headers of all columns
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to find in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}
  1. שומרים את פרויקט הסקריפט.

בדיקת קוד

נבחן את הקוד בכל אחת משלוש הפונקציות האלה:

formatColumnHeader()

כפי שציפיתם, השורות הראשונות של הפונקציה הזו מגדירות משתנים המציינים את הגיליון ואת הטווח שבהם אנחנו מתעניינים.

  • הגיליון הפעיל מאוחסן בקובץ sheet.
  • מספר השורות בכותרת העמודה מחושב ונשמר ב-numRows. כאן הקוד מנוכה כך שספירת השורות לא תכלול את כותרת העמודה: title.
  • הטווח שמכסה את כותרת העמודה מאוחסן ב-columnHeaderRange.

לאחר מכן, הקוד מחיל את הגבולות ומודגש על הטווח של כותרת העמודה, בדיוק כמו ב-formatRowHeader(). כאן נעשה שימוש גם ב-Range.setFontStyle(fontStyle) כדי להפוך את הטקסט לנטוי.

הוספת ההיפר-קישורים לעמודה 'כותרת' מורכבת יותר, כך ש-formatColumnHeader() מתקשרת אל hyperlinkColumnHeaders_(headerRange, numRows) כדי לטפל במשימה. כך הקוד צריך להיות מסודר וקריא.

hyperlinkColumnHeaders_(headerRange, numRows)

פונקציית עזר זו מזהה לראשונה את אינדקסי העמודות בכותרת (בהנחה שמדובר באינדקס 1) ובעמודה url. השירות מתקשר אל columnIndexOf_('url') כדי לקבל את אינדקס העמודות של כתובת ה-URL. אם לא נמצאה עמודה url' השיטה תיסגר בלי לשנות נתונים.

הפונקציה מקבלת טווח חדש (urlRange) שמכסה את כתובות ה-URL התואמות לשורות של עמודות הכותרת. הפעולה הזו מתבצעת בשיטה Range.offset(rowOffset, columnOffset), מה שמבטיח שהטווחים יהיו זהים. הערכים בעמודה headerColumn וגם בעמודה url יאוחזרו (headerValues ו-urlValues).

לאחר מכן, הפונקציה עוברת מעל כל ערך בתא של כותרת העמודה ומחליפה אותו בנוסחה =HYPERLINK() Sheets שנוצרה עם הכותרת ועם תוכן העמודות url. הערכים של הכותרות ששונו יתווספו לאחר מכן לגיליון באמצעות Range.setValues(values).

לבסוף, כדי לשמור על ניקיון הגיליון וכדי לבטל מידע מיותר, מתבצעת קריאה ל-Sheet.deleteColumn(columnPosition) להסרת העמודה url.

columnIndexOf_(colName)

פונקציית העזרה הזו היא רק פונקציית שירות פשוטה שמחפשת בשורה הראשונה של גיליון כדי למצוא שם ספציפי. שלוש השורות הראשונות משתמשות בשיטות שכבר צפיתם בהן, כדי לקבל רשימה של שמות של עמודות משורה 1 בגיליון האלקטרוני. השמות האלה מאוחסנים במשתנה columnName.

לאחר מכן, הפונקציה בודקת כל שם לפי הסדר. אם היא מוצאת שם שתואם לשם שמחפשים, היא מפסיקה ומחזירה את האינדקס של העמודה. אם היא מגיעה לסוף רשימת השמות מבלי למצוא את השם, היא מחזירה 1- כדי לציין שהשם לא נמצא.

תוצאות

כדי לראות את פונקציית העיצוב, אפשר לבצע את הפעולות הבאות:

  1. אם עדיין לא עשית זאת, שמור את פרויקט הסקריפט שלך בעורך הסקריפטים של Apps.
  2. לוחצים על פריט התפריט פורמטים מהירים > פורמט כותרת עמודה.

התוצאות צריכות להיראות כך:

7497cf1b982aeff6.gif

מעכשיו מתבצעת אוטומטית משימת עיצוב אחרת. כותרות העמודות והשורות מוצגות בפורמט הבא, ומוסבר בו איך מעצבים את הנתונים.

6. עיצוב מערך הנתונים

עכשיו, אחרי שיש לכם כותרות, בואו נתחיל פונקציה שמעצבת את שאר הנתונים בגיליון. נשתמש באפשרויות העיצוב הבאות:

  • צבעי רקע מתחלפים (המכונים פסי צבע)
  • שינוי פורמטים של תאריכים
  • החלת הגבולות
  • להפוך את כל העמודות והשורות לאוטומטיות

עכשיו יוצרים פונקציה formatDataset() ושיטה שימושית נוספת להחלת הפורמטים האלה על נתוני הגיליון.

יישום

כמו קודם, צריך להוסיף פונקציה כדי להפוך את עיצוב הנתונים לאוטומטי. איך עושים את זה?

  1. בעורך Apps Script, יש להוסיף את הפונקציה formatDataset() הבאה לסוף פרויקט הסקריפט:
/**
 * Formats the sheet data, excluding the header row and column.
 * Applies the border and banding, formats the 'release_date'
 * column, and autosizes the columns and rows.
 */
function formatDataset() {
  // Get the active sheet and data range.
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var fullDataRange = sheet.getDataRange();

  // Apply row banding to the data, excluding the header
  // row and column. Only apply the banding if the range
  // doesn't already have banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have banding, so it's
    // safe to apply it.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply date formatting
  // to the column labeled 'release_date'.
  formatDates_( columnIndexOf_('release_date') );
  
  // Set a border around all the data, and resize the
  // columns and rows to fit.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}
  1. צריך להוסיף את פונקציית העזרה הבאה בסוף פרויקט הסקריפט, אחרי הפונקציה formatDataset():
/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The index of the column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date format for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}
  1. שומרים את פרויקט הסקריפט.

בדיקת קוד

נבחן את הקוד בכל אחת משתי הפונקציות הבאות:

formatDataset()

פונקציה זו דומה לתבנית הבאה של הפונקציות הקודמות. ראשית, הוא מקבל משתנים כדי לכלול הפניות לגיליון הפעיל (גיליון) ולטווח הנתונים (fullDataRange).

שנית, היא משתמשת בשיטה Range.offset(rowOffset, columnOffset, numRows, numColumns) כדי ליצור טווח (noHeadersRange) שמכסה את כל הנתונים בגיליון, מלבד כותרות העמודות והעמודות. לאחר מכן, הקוד מאמת אם הטווח החדש הזה כולל פסים קיימים (באמצעות Range.getBandings()). עליך לעשות זאת מפני ש-Apps Script יוצר שגיאה אם מנסים להחיל פסי צבע חדשים כאשר קיים תדר כזה. אם אין פסי צבע, הפונקציה מוסיף פסי אפור בהיר באמצעות Range.applyRowBanding(bandingTheme, showHeader, showFooter). אחרת, הפונקציה תמשיך לפעול.

השלב הבא קורא לפונקציית העזרה של formatDates_(colIndex) לעצב את התאריכים בעמודה 'release_date' (מתוארת בהמשך). העמודה צוינה באמצעות פונקציית העזרה של columnIndexOf_(colName) שהטמעתם קודם.

לבסוף, כשהעיצוב מסתיים, מוסיפים גבול נוסף (כמו קודם) ומשנים אוטומטית את הגודל של כל עמודה ושורה כך שיתאימו לנתונים שהם מכילים בשיטות Sheet.autoResizeColumns(columnPosition) ו-Sheet.autoResizeColumns(columnPosition).

formatDates_(colIndex)

פונקציית העזרה הזו מחילה פורמט תאריך ספציפי על עמודה באמצעות אינדקס העמודה שסופק. באופן ספציפי, היא מעצבת את ערכי התאריך כ-"יום בחודש, שנה (יום בשבוע)".

תחילה, הפונקציה מאמתת שאינדקס העמודות שסופק תקף (כלומר, 0 ומעלה). אם לא, הוא יחזור מבלי לעשות דבר. בדיקה זו מונעת שגיאות שעשויות להיגרם אם, לדוגמה, לגיליון אין עמודה בשם 'release_date'

לאחר אימות אינדקס העמודה, הפונקציה מקבלת את הטווח שמכסה את העמודה הזו (לא כולל שורת הכותרת שלה) ומשתמשת ב-Range.setNumberFormat(numberFormat) כדי להחיל את העיצוב.

תוצאות

כדי לראות את פונקציית העיצוב, אפשר לבצע את הפעולות הבאות:

  1. אם עדיין לא עשית זאת, שמור את פרויקט הסקריפט שלך בעורך הסקריפטים של Apps.
  2. לוחצים על פריט התפריט פורמטים מהירים > עיצוב של מערך נתונים.

התוצאות צריכות להיראות כך:

3cfedd78b3e25f3a.gif

יש לך משימת עיצוב אוטומטית נוספת. עכשיו, אחרי שפקודות העיצוב האלה זמינות, ניתן להוסיף עוד נתונים ולהחיל אותן.

7. אחזור ועיצוב נתוני API

עד עכשיו, במעבדת הקוד הזו ראית איך אפשר להשתמש ב-Apps Script כאמצעי חלופי לעיצוב הגיליון האלקטרוני. השלב הבא הוא לכתוב קוד שמאחזר נתונים מה-API ציבורי, מוסיף אותו לגיליון האלקטרוני ומעצב אותו כך שיהיה קריא.

במעבדת הקוד האחרונה למדתם איך לשלוף נתונים מ-API. גם כאן תשתמשו באותן שיטות. בתרגיל הזה, נשתמש ב-Star Wars API (SWAPI) הציבורי כדי לאכלס את הגיליון האלקטרוני שלכם. באופן ספציפי, תשתמשו ב-API כדי לקבל מידע על הדמויות העיקריות שמופיעות בשלושה סרטי המלחמה המקוריים.

הקוד יתקשר אל ה-API כדי לקבל כמות גדולה של נתוני JSON, לנתח את התגובה, למקם את הנתונים בגיליון חדש ולאחר מכן לעצב את הגיליון.

יישום

בקטע הזה, מוסיפים כמה מנות נוספות בתפריט. כל פריט בתפריט מפעיל סקריפט Wrapper שמעביר משתנים ספציפיים לפריט לפונקציה הראשית (createResourceSheet_()). צריך להטמיע את הפונקציה הזו ושלוש פונקציות מסייעות נוספות. כמו קודם, הפונקציות המסייעות עוזרות לבודד חלקים חלקיים של המשימה בצורה לוגית ולעזור בשמירה על הקריאה של הקוד.

מבצעים את הפעולות הבאות:

  1. בעורך ה-Apps Script, מעדכנים את הפונקציה onOpen() בפרויקט הסקריפט כך:
/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .addToUi();
}
  1. שומרים את פרויקט הסקריפט.
  2. בעורך הסקריפטים, בוחרים באפשרות onOpen מתוך רשימת הפונקציות ולוחצים על הפעלה. הפעולה הזו מפעילה את onOpen() כדי לבנות מחדש את תפריט הגיליון האלקטרוני עם האפשרויות החדשות שהוספת.
  3. כדי ליצור קובץ Apps Script, לצד קבצים לוחצים על 'הוספת קובץ' הוספת קובץ > Script.
  4. נותנים שם לסקריפט החדש "API" ומקישים על Enter. (סקריפט של Apps מצרף באופן אוטומטי תוסף .gs לשם של קובץ הסקריפט).
  5. מחליפים את הקוד בקובץ API.gs החדש בקוד הבא:
/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Creates a formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data exists, the sheet is overwritten with the API
 * information.
 *
 * @param {string} resourceType The type of resource.
 * @param {number} idNumber The identification number of the film.
 * @param {number} episodeNumber The Star Wars film episode number.
 *   This is only used in the sheet name.
 */
function createResourceSheet_(
    resourceType, idNumber, episodeNumber) { 
  
  // Fetch the basic film data from the API. 
  var filmData = fetchApiResourceObject_(
      "https://swapi.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data about each individually.
  var resourceUrls = filmData[resourceType];
  
  // Fetch each resource from the API individually and push
  // them into a new object list.
  var resourceDataList = []; 
  for(var i = 0; i < resourceUrls.length; i++){
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    ); 
  } 
  
  // Get the keys used to reference each part of data within
  // the resources. The keys are assumed to be identical for
  // each object since they're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's created.
  var resourceSheet = createNewSheet_(
      "Episode " + episodeNumber + " " + resourceType);
  
  // Add the API data to the new sheet, using each object
  // key as a column header. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
  
  // Format the new sheet using the same styles the
  // 'Quick Formats' menu items apply. These methods all
  // act on the active sheet, which is the one just created.
  formatRowHeader();
  formatColumnHeader();   
  formatDataset();

}
  1. מוסיפים את הפונקציות המסייעות הבאות לסוף קובץ הפרויקט של API.gs:
/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}
  1. שומרים את פרויקט הסקריפט.

בדיקת קוד

סיימת להוסיף הרבה קוד. מומלץ לעבור על כל פונקציה בנפרד כדי להבין איך הן פועלות:

onOpen()

כאן הוספת כמה מנות לתפריט Quick formats. הגדרת קו מפריד ולאחר מכן השתמשת בשיטה Menu.addSubMenu(menu) כדי ליצור מבנה תפריט מקנן עם שלושה פריטים חדשים. הפריטים החדשים מתווספים באמצעות השיטה Menu.addItem(caption, functionName).

פונקציות ראפ

כל המנות הנוספות בתפריט עובדות באופן דומה: הן מנסות ליצור גיליון עם נתונים שנשלפים מ-SWAPI. ההבדל היחיד הוא שהם מתמקדים בסרט אחר.

מומלץ לכתוב פונקציה יחידה כדי ליצור גיליון אלקטרוני, ולאפשר לפונקציה לקבל פרמטר כדי לקבוע באיזה סרט להשתמש. עם זאת, השיטה Menu.addItem(caption, functionName) לא מאפשרת להעביר פרמטרים כאשר היא מופעלת על ידי התפריט. אז איך אפשר להימנע מכתיבת אותו קוד שלוש פעמים?

התשובה היא פונקציות wrapper. אלו הן פונקציות קלות שניתן להפעילן באופן מיידי כדי להפעיל פונקציה אחרת עם פרמטרים ספציפיים.

כאן יש בקוד שלוש פונקציות wrapper: createPeopleSheetIV(), createPeopleSheetV() ו-createPeopleSheetVI(). המנות בתפריט מקושרות לפונקציות האלה. כאשר לוחצים על פריט בתפריט, פונקציית wrapper מופעלת ומפעילה מיד את הפונקציה העיקרית של הכלי ליצירת גיליונות, createResourceSheet_(resourceType, idNumber, episodeNumber), ומעבירה את הפרמטרים המתאימים לפריט התפריט. במקרה זה, פירוש הדבר הוא לבקש מהפונקציה ליצירת גיליונות ליצור גיליון מלא בנתוני תווים באחד מהסרטים של 'מלחמת הכוכבים'.

createResourceSheet_(resourceType, idNumber, episodeNumber)

זוהי הפונקציה העיקרית של הכלי ליצירת גיליונות עבור התרגיל הזה. בעזרת פונקציות מסוימות של Assistant, היא מקבלת את נתוני ה-API, מנתחת אותם, יוצרת גיליון, כותבת את נתוני ה-API של הגיליון ולאחר מכן מעצבת את הגיליון באמצעות הפונקציות שיצרתם בסעיפים הקודמים. נבחן את הפרטים:

תחילה, הפונקציה משתמשת ב-fetchApiResourceObject_(url) כדי לבקש מ-API לאחזר מידע בסיסי על הסרט. תגובת ה-API כוללת אוסף של כתובות URL שבהן הקוד יכול להשתמש כדי לקבל פרטים נוספים על אנשים ספציפיים (המכונים כאן משאבים) מהסרטים. הקוד אוסף את כל הנתונים במערך resourceUrls.

בשלב הבא, הקוד משתמש ב-fetchApiResourceObject_(url) שוב ושוב כדי לקרוא ל-API עבור כל כתובת URL של משאב ב-resourceUrls. התוצאות מאוחסנות במערך resourceDataList. כל רכיב במערך הזה הוא אובייקט שמתאר דמות אחרת מהסרט.

האובייקטים של נתוני המשאבים כוללים מספר מפתחות נפוצים הממופים למידע על תו זה. לדוגמה, המקש 'name'' ממפה את שם הדמות בסרט. אנחנו מניחים שהמפתחות של כל אובייקט בנתוני המשאב זהים, כי הם מיועדים להשתמש במבני אובייקטים נפוצים. רשימת המפתחות נדרשת מאוחר יותר, לכן הקוד מאחסן את רשימת המפתחות ב-resourceObjectKeys באמצעות שיטת Object.keys() של JavaScript.

בשלב הבא, פונקציית הבנייה יוצרת את פונקציית העזרה של createNewSheet_(name) כדי ליצור את הגיליון שבו הנתונים החדשים יוצבו. קריאה לפונקציית העזרה הזו מפעילה גם את הגיליון החדש.

לאחר יצירת הגיליון, מתבצעת קריאה לפונקציית העזרה fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) כדי להוסיף את כל נתוני ה-API לגיליון.

לבסוף, כל פונקציות העיצוב שיצרתם בעבר נקראות להחיל את אותם כללי עיצוב על הנתונים החדשים. הגיליון החדש הוא הגיליון הפעיל, ולכן הקוד יכול להשתמש שוב בפונקציות האלה ללא שינוי.

fetchApiResourceObject_(url)

פונקציית העזרה הזו דומה לפונקציית האסיסטנט של fetchBookData_(ISBN) שנעשה בה שימוש בקוד הנתונים הקודם. היא מקבלת את כתובת ה-URL הנתונה ומשתמשת בשיטה UrlFetchApp.fetch(url, params) כדי לקבל תגובה. לאחר מכן, התגובה מנתחת לאובייקט JSON באמצעות השיטות HTTPResponse.getContextText() ו-JavaScript JSON.parse(json). לאחר מכן מוחזר אובייקט JSON שמתקבל.

createNewSheet_(name)

פונקציית העזרה הזו פשוטה למדי. בשלב הראשון הוא בודק אם גיליון אלקטרוני בשם קיים קיים בגיליון האלקטרוני. אם כן, הפונקציה מפעילה את הגיליון ומחזירה אותו.

אם הגיליון לא קיים, הפונקציה יוצרת אותו באמצעות Spreadsheet.insertSheet(sheetName), מפעילה אותו ומחזירה את הגיליון החדש.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

פונקציית עזר זו אחראית על מילוי הגיליון החדש בנתוני API. הוא מקבל כפרמטרים את הגיליון החדש, את רשימת מפתחות האובייקטים, ואת רשימת האובייקטים בממשק ה-API כפרמטרים. כל מפתח אובייקט מייצג עמודה בגיליון החדש, וכל אובייקט משאב מייצג שורה.

תחילה, הפונקציה מחשבת את מספר השורות והעמודות הנדרשות להצגת נתוני ה-API החדשים. זהו הגודל של רשימת המשאבים ומפתחות, בהתאמה. הפונקציה מגדירה טווח פלט (resourceRange) שבו הנתונים ימוקמו, ותוסיף שורה נוספת שתצמיד את כותרות העמודות. המשתנה resourceValues מכיל מערך ערכים דו-ממדי שחולץ מ-resourceRange.

לאחר מכן, הפונקציה עוברת בלופ של כל מפתח אובייקט ברשימה objectKeys. המפתח מוגדר ככותרת העמודה, ולאחר מכן לולאה שנייה עוברת דרך כל אובייקט משאב. לכל צמד (שורה, עמודה), פרטי ה-API המתאימים יועתקו לרכיב resourceValues[row][column].

לאחר המילוי של resourceValues, גיליון היעד נמחק באמצעות Sheet.clear() למקרה שהוא מכיל נתונים מקליקים קודמים של פריט בתפריט. לבסוף, הערכים החדשים נכתבים בגיליון.

תוצאות

אתם יכולים לראות את התוצאות של העבודה שלכם:

  1. אם עדיין לא עשית זאת, שמור את פרויקט הסקריפט שלך בעורך הסקריפטים של Apps.
  2. לוחצים על האפשרות פורמטים מהירים > יצירת גיליון תווים > פרק 4.

התוצאות צריכות להיראות כך:

d9c472ab518d8cef.gif

עכשיו כתבת קוד כדי לייבא נתונים ל-Sheets ולעצב אותו באופן אוטומטי.

8. סיכום

ברכות על השלמת Lab זה. ראית חלק מאפשרויות העיצוב של Sheets שניתן לכלול בפרויקטים של Apps Script, ובנית אפליקציה מרשימה שמייבאת ומגדירה מערך נתונים גדול של ממשקי API.

האם שיעור ה-Lab הזה הועיל לך?

כן לא

מה למדת

  • איך להחיל פעולות פורמט שונות של Sheets עם Apps Script.
  • כיצד יוצרים תפריטי משנה באמצעות הפונקציה onOpen().
  • כיצד לעצב רשימה שאוחזרה של אובייקטים מסוג JSON לגיליון חדש של נתונים באמצעות Apps Script.

המאמרים הבאים

בשיעור ה-Lab הבא בפלייליסט הזה אנחנו מסבירים איך להשתמש ב-Apps Script כדי ליצור תצוגה חזותית של הנתונים בתרשים, ולייצא תרשימים למצגות ב-Google Slides.

תוכלו למצוא את מעבדת הקוד הבאה בתרשים ומציג נתונים ב-Slides.