דף זה תורגם על ידי Cloud Translation API.

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

1. מבוא

ברוכים הבאים לחלק הרביעי של סדרת סרטוני הדרכה בנושא יסודות Apps Script עם Google Sheets.

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

מה תלמדו

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

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

זוהי סדנת הקוד הרביעית בפלייליסט Fundamentals of Apps Script with Google Sheets (יסודות של Apps Script עם Google Sheets). לפני שמתחילים את ה-codelab הזה, חשוב להשלים את ה-codelabs הקודמים:

מה נדרש

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

2. הגדרה

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

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

בוחרים באפשרות תוספים > Apps Script כדי לפתוח את עורך הסקריפטים.
לוחצים על שם הפרויקט ב-Apps Script ומשנים אותו מ-Untitled project (פרויקט ללא שם) ל-Data Formatting (עיצוב נתונים). לוחצים על שינוי שם כדי לשמור את השינוי בשם.

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

3. יצירת תפריט בהתאמה אישית

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

הטמעה

הגיע הזמן ליצור תפריט בהתאמה אישית.

בעורך 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();
}

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

בדיקת קוד

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

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

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

תוצאות

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

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

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

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

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

הטמעה

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

בעורך 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);

}

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

בדיקת קוד

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

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

התג Range.setFontWeight(fontWeight) משמש להגדרת עובי הגופן כמודגש.
‫Range.setFontColor(color) משמש להגדרת צבע הגופן ללבן.
התג Range.setBackground(color) משמש להגדרת צבע הרקע לכחול-ירוק כהה.
‫setBorder(top, left, bottom, right, vertical, horizontal, color, style) מוסיף גבול שחור רציף מסביב לתאים בטווח.

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

תוצאות

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

אם עדיין לא עשיתם זאת, שומרים את פרויקט הסקריפט בעורך Apps Script.
לוחצים על פריט התפריט Quick formats > Format row header (עיצובים מהירים > עיצוב כותרת שורה).

הערה: אם אתם משתמשים בחשבון gmail.com, יכול להיות שבפעם הראשונה שתשתמשו בסקריפט יוצג לכם תיבת דו-שיח עם ההודעה 'האפליקציה הזו לא אומתה'. ‫Google משתמשת בזה כדי להזהיר משתמשים שאולי משתמשים בקוד ממחברים לא מוכרים או לא מהימנים. אם תיבת הדו-שיח הזו מוצגת, אפשר להמשיך כי אתם כתבתם את הסקריפט. פועלים לפי ההוראות במסך כדי להמשיך בהרשאת הסקריפט:

בתיבת הדו-שיח של האפליקציה שלא אומתה, לוחצים על מתקדם.
לוחצים על מעבר אל 'שינוי נתונים ותפריטים בהתאמה אישית' (לא בטוח).
לוחצים על אישור.

במהלך ה-codelab הזה, יכול להיות שתקבלו כמה בקשות להרשאות. מידע נוסף על התהליך הזה זמין במאמר בנושא הרשאה לשירותי Google.

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

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

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

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

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

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

הטמעה

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

בעורך 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); 
}

מוסיפים את פונקציות העזר הבאות לסוף פרויקט הסקריפט, אחרי הפונקציה 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; 
}

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

בדיקת קוד

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

`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)`

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

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

תוצאות

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

אם עדיין לא עשיתם זאת, שומרים את פרויקט הסקריפט בעורך Apps Script.
לוחצים על פריט התפריט Quick formats > Format column header (פורמטים מהירים > עיצוב כותרת העמודה).

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

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

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

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

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

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

הטמעה

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

בעורך 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());
}

מוסיפים את פונקציית העזר הבאה בסוף פרויקט הסקריפט, אחרי הפונקציה 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)");
}

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

בדיקת קוד

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

`formatDataset()`

הפונקציה הזו פועלת לפי דפוס דומה לפונקציות הקודמות של פורמטים שכבר הטמעתם. קודם כל, הוא מקבל משתנים להחזקת הפניות לגיליון הפעיל (sheet) ולטווח הנתונים (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) כדי להחיל את העיצוב.

תוצאות

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

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

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

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

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

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

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

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

הטמעה

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

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

בעורך 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();
}

שומרים את פרויקט הסקריפט.
בכלי לעריכת סקריפטים, בוחרים באפשרות onOpen מתוך רשימת הפונקציות ולוחצים על Run (הפעלה). הפעולה הזו מריצה את onOpen() כדי לבנות מחדש את תפריט הגיליון האלקטרוני עם האפשרויות החדשות שהוספתם.
כדי ליצור קובץ Apps Script, לוחצים על סמל הוספת קובץ לצד Files (קובצים) > Script (סקריפט).
נותנים לסקריפט החדש את השם API ולוחצים על Enter. ‫(Apps Script מוסיף אוטומטית את הסיומת .gs לשם קובץ הסקריפט).
מחליפים את הקוד בקובץ 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();

}

מוסיפים את פונקציות העזר הבאות לסוף קובץ הפרויקט של הסקריפט 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);
}

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

בדיקת קוד

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

`onOpen()`

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

פונקציות עוטפות

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

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

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

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

`createResourceSheet_(resourceType, idNumber, episodeNumber)`

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

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

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

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

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

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

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

`fetchApiResourceObject_(url)`

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

`createNewSheet_(name)`

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

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

`fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)`

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

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

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

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

תוצאות

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

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

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

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

8. סיכום

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

האם ה-codelab הזה היה מועיל?

כן

לא

מה למדתם

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

מונחי מפתח:

פעיל (סטטוס): מציין שהגיליון האלקטרוני, הגיליון, הטווח או התא שצוינו הם אלה שהמשתמש בגיליון האלקטרוני צופה בהם או מסמן אותם כרגע.
תא פעיל: התא המודגש היחיד בגיליון הפעיל, שהסמן נמצא בו.
טווח פעיל: קבוצה של תאים אחד או יותר שמודגשים כרגע בגיליון הפעיל.
‫API: ממשק תכנות יישומים הוא שירות שתוכניות או סקריפטים יכולים להפעיל כדי לבקש מידע או לבצע פעולות מסוימות.
הרשאה: התהליך שבו המשתמש מעניק הרשאות כדי לאפשר ל-Apps Script לגשת לנתוני המשתמש בשירותי Google הרלוונטיים או לבצע פעולות אחרות בשם המשתמש.
סקריפט שקשור למאגר: כל סקריפט שקשור למסמך של Google Workspace ונוצר ממנו, כמו גיליון אלקטרוני של Google או מסמך Google Docs.
פונקציית עזר: פונקציה, בדרך כלל פרטית, שפונקציה אחרת קוראת לה כדי להשלים משימת משנה קטנה. פונקציות עזר משמשות כשצריך להשלים את משימת המשנה הרבה פעמים במקומות שונים.
‫JSON: פורמט קובץ להגדרת אובייקטים של נתונים והמאפיינים שלהם מתוך טקסט.
‫onOpen(): אם מוגדר בסקריפט, זהו טריגר פשוט שמופעל בכל פעם שקובץ נפתח או נטען מחדש.
פונקציות פרטיות: פונקציה שלא ניתן להפעיל כחלק מספרייה או על ידי לקוחות באמצעות תקשורת בין שרת ללקוח. השמות של פונקציות פרטיות ב-Apps Script מסתיימים ב-_.
טווח**: קבוצה של תא אחד או יותר סמוכים בגיליון. הסיווג Range** מאפשר לקרוא ולעדכן טווחים בגיליון.
עורך קובצי סקריפט: עורך הקוד בדפדפן ל-Apps Script.
טריגרים פשוטים: סוג משנה של טריגרים ב-Apps Script עם שמות שמורים של פונקציות טריגר והגבלות מסוימות.
גיליון**: דף יחיד בגיליון אלקטרוני. לפעמים משמש לציון קובץ Google Sheets ב-Google Drive. המחלקות Sheet** מאפשרות לכם לגשת לגיליונות ולשנות אותם.
‫SpreadsheetApp: המחלקה הזו משמשת כמחלקת האב של שירות הגיליונות האלקטרוניים, והיא מספקת נקודת התחלה לקוד שקורא נתונים מ-Google Sheets או מבצע בהם מניפולציות.
גיליון אלקטרוני**: קובץ Google Sheets שנמצא ב-Google Drive. המחלקות Spreadsheet** מאפשרות לכם לגשת לגיליונות אלקטרוניים ולשנות אותם.
שירות הגיליונות האלקטרוניים: שירות Apps Script שמאפשר לסקריפטים ליצור קבצים של Google Sheets, לגשת אליהם ולשנות אותם.
גורמים מפעילים: אירוע שמשויך לפונקציית Apps Script. כשהאירוע (למשל, פתיחת גיליון אלקטרוני) מתרחש, הטריגר מופעל ומריץ אוטומטית את הפונקציה המשויכת של Apps Script.
שירות אחזור של כתובות אתרים: שירות Apps Script שמאפשר לסקריפטים להתחבר לנקודות קצה (endpoints) של כתובות URL כדי לשלוח בקשות ולקבל תשובות.
פונקציות עוטפות: פונקציות עזר פשוטות שמשמשות לקריאה של פונקציות מורכבות יותר, תוך העברת קבוצה ספציפית של פרמטרים.

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

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

ה-Codelab הבא זמין בכתובת יצירת תרשים והצגת נתונים ב-Slides.

יסודות Apps Script עם Google Sheets #4: עיצוב נתונים קל לארגן דפים בעזרת אוספים אפשר לשמור ולסווג תוכן על סמך ההעדפות שלך.

1. מבוא

מה תלמדו

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

מה נדרש

2. הגדרה

3. יצירת תפריט בהתאמה אישית

הטמעה

בדיקת קוד

תוצאות

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

הטמעה

בדיקת קוד

תוצאות

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

הטמעה

בדיקת קוד

formatColumnHeader()

hyperlinkColumnHeaders_(headerRange, numRows)

columnIndexOf_(colName)

תוצאות

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

הטמעה

בדיקת קוד

formatDataset()

formatDates_(colIndex)

תוצאות

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

הטמעה

בדיקת קוד

onOpen()

פונקציות עוטפות

createResourceSheet_(resourceType, idNumber, episodeNumber)

fetchApiResourceObject_(url)

createNewSheet_(name)

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

תוצאות

8. סיכום

האם ה-codelab הזה היה מועיל?

מה למדתם

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

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

`formatColumnHeader()`

`hyperlinkColumnHeaders_(headerRange, numRows)`

`columnIndexOf_(colName)`

`formatDataset()`

`formatDates_(colIndex)`

`onOpen()`

`createResourceSheet_(resourceType, idNumber, episodeNumber)`

`fetchApiResourceObject_(url)`

`createNewSheet_(name)`

`fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)`