הופכים את Big Data לתובנות באמצעות Google Sheets ו-Slides

1. מניתוח של Big Data ועד למצגת שקפים

יש הרבה כלים למדעני נתונים לביצוע ניתוחים של נתונים גדולים, אבל בסופו של דבר, מנתחים עדיין צריכים להצדיק את התוצאות להנהלה. קשה להציג הרבה מספרים על נייר או במסד נתונים לבעלי עניין מרכזיים. ב-Codelab הזה של Google Apps Script ברמת ביניים נעשה שימוש ב-2 פלטפורמות של Google למפתחים (Google Workspace ו-מסוף Google Cloud) כדי לעזור לכם להפוך את השלב האחרון לאוטומטי.

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

למה בחרנו לעשות זאת?

אפליקציית הדוגמה במדריך הזה קיבלה השראה מדוגמאות הקוד הבאות:

אפליקציה לדוגמה של שירות BigQuery ב-Google Apps Script, שהיא קוד פתוח ב-GitHub.
אפליקציית הדוגמה שמוצגת בסרטון למפתחים בנושא יצירת שקפים מנתונים בגיליון אלקטרוני ופורסמה בפוסט הזה בבלוג.
אפליקציית הדוגמה שמוצגת בסדנת ה-codelab של Google Slides API.

אפליקציית הדוגמה ב-Codelab בנושא Slides API כוללת גם את BigQuery ו-Slides, אבל היא שונה מאפליקציית הדוגמה ב-Codelab הזה בכמה דרכים:

אפליקציית Node.js שלהם לעומת אפליקציית Apps Script שלנו.
הם משתמשים בממשקי REST API, ואנחנו משתמשים בשירותי Apps Script.
הם משתמשים ב-Google Drive אבל לא ב-Google Sheets, ואילו האפליקציה הזו משתמשת ב-Sheets אבל לא ב-Drive.

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

מה תלמדו

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

הדרישות

דפדפן אינטרנט עם גישה לאינטרנט
חשבון Google (יכול להיות שחשבונות Google Workspace ידרשו אישור אדמין)
היכרות בסיסית עם Google Sheets
יכולת לקרוא סימון A1 ב-Sheets
מיומנויות בסיסיות ב-JavaScript
ידע בפיתוח ב-Apps Script מועיל אבל לא חובה

2. סקר

איך תשתמשו ב-codelab או במדריך הזה?

אקרא את המאמר כדי לקבל מידע, ואולי אעביר אותו לעמיתים טכניים

אעבור על המאמר עד כמה שאוכל ואנסה לבצע כמה שיותר תרגילים

אשלים את כל ה-Codelab בכל מקרה

איך היית מדרג את חוויית השימוש שלך בכלי הפיתוח ובממשקי ה-API של Google Workspace?

מתחילים

ביניים

מומחים

איזה דירוג מגיע לדעתך לחוויית השימוש שלך ב-Apps Script?

מתחילים

ביניים

מומחים

איך היית מדרג את חוויית השימוש שלך בממשקי ה-API ובכלי הפיתוח של מסוף Cloud?

מתחילים

ביניים

מומחים

3. סקירה כללית

אחרי שהסברנו על מה מדובר ב-codelab הזה, הנה מה שתעשו:

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

נתחיל עם קצת מידע רקע על Apps Script,‏ BigQuery,‏ Sheets ו-Slides.

‫Google Apps Script ו-BigQuery

‫Google Apps Script היא פלטפורמת פיתוח של Google Workspace שפועלת ברמה גבוהה יותר מממשקי ה-API של Google REST. זו סביבת פיתוח ואירוח אפליקציות ללא שרת (serverless) שזמינה לכל רמות המיומנות של המפתחים. במילים אחרות, Apps Script היא סביבת זמן ריצה של JavaScript בלי שרת (serverless), שמאפשרת אוטומציה, הרחבה ושילוב של Google Workspace.

הוא משתמש ב-JavaScript בצד השרת, בדומה ל-Node.js, אבל הוא מתמקד בשילוב הדוק עם Google Workspace ושירותים אחרים של Google, ולא באירוח מהיר ואסינכרוני של אפליקציות מבוססות-אירועים. בנוסף, סביבת הפיתוח עשויה להיות שונה מזו שהייתם רגילים אליה. באמצעות Apps Script, אתם יכולים:

אפשר לפתח סקריפטים באמצעות כלי לעריכת קוד שמבוסס על דפדפן, אבל אפשר גם לפתח באופן מקומי כשמשתמשים ב-clasp, כלי הפריסה של שורת הפקודה ל-Apps Script.
לכתוב קוד בגרסה מיוחדת של JavaScript שמותאמת לגישה ל-Google Workspace ולשירותים אחרים של Google או לשירותים חיצוניים (באמצעות שירותי URL Fetch או JDBC של Apps Script).
לא צריך לכתוב קוד הרשאה כי Apps Script מטפל בזה בשבילכם.
לא צריך לארח את האפליקציה – היא נמצאת ופועלת בשרתים של Google בענן.

יש 2 דרכים שבהן ממשקי Apps Script פועלים עם טכנולוגיות אחרות של Google:

בתור שירות מובנה
בתור שירות מתקדם

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

כשזה אפשרי, מפתחים צריכים להשתמש בשירות מובנה כי קל יותר להשתמש בו והוא עושה יותר פעולות מאשר שירותים מתקדמים. עם זאת, לחלק מממשקי ה-API של Google אין שירותים מובנים, ולכן יכול להיות ששירות מתקדם הוא האפשרות היחידה. לדוגמה, ל-Google BigQuery אין שירות מובנה, אבל קיים BigQuery Service. שירות BigQuery הוא שירות ב-Cloud Console שמאפשר להשתמש ב-Google BigQuery API כדי להריץ שאילתות על מאגרי נתונים גדולים (לדוגמה, כמה טרה-בייט), ועדיין לקבל תוצאות תוך שניות.

גישה ל-Sheets ול-Slides מ-Apps Script

בניגוד ל-BigQuery, גם ב-Sheets וגם ב-Slides יש שירותים מובנים. יש להם גם שירותים מתקדמים לגישה לתכונות שקיימות רק ב-API. לפני שמתחילים לכתוב קוד, כדאי לעיין במסמכים של שירותי Sheets ו-Slides המובנים. שימו לב שיש גם מסמכים לשירותים המתקדמים של Sheets ושל Slides.

4. משימה 1: הפעלת BigQuery ותיעוד התוצאות ב-Sheets

מבוא

נבצע חלק גדול מה-codelab הזה באמצעות המשימה הראשונה הזו. למעשה, אחרי שתסיימו את השלב הזה, תהיו בערך באמצע הדרך של כל ה-Codelab. המדריך מחולק לכמה חלקים, ובכל אחד מהם:

יוצרים פרויקט ב-Google Apps Script ופרויקט במסוף Cloud.
מפעילים גישה לשירות המתקדם של BigQuery.
פותחים את הכלי לעריכת סקריפטים ומזינים את קוד המקור של האפליקציה.
ניווט בתהליך מתן ההרשאות לאפליקציה (OAuth2).
מריצים את האפליקציה ששולחת בקשה ל-BigQuery.
בודקים את גיליון Google Sheets החדש שנוצר באמצעות התוצאות של BigQuery.

הגדרה

כדי ליצור פרויקט Apps Script, עוברים אל script.google.com ולוחצים על פרויקט חדש.
כדי לשנות את השם של פרויקט Apps Script, לוחצים על פרויקט ללא שם, מזינים שם לפרויקט ולוחצים על שינוי שם.

לאחר מכן, תצטרכו ליצור פרויקט ב-Cloud Console כדי להריץ שאילתות על נתונים ב-BigQuery.

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

כשהיצירה של הפרויקט תסתיים, תופיע הודעה בדף. מוודאים שהפרויקט החדש מסומן ברשימת הפרויקטים בחלק העליון של הדף.
לוחצים על סמל התפריט ועוברים אל APIs & Services > מסך הסכמה ל-OAuth (קישור ישיר).
לוחצים על Internal > Create כדי ליצור אפליקציה למשתמשי Google Workspace בארגון.
בשדה שם האפליקציה מזינים Big Data Codelab.
מזינים כתובות אימייל ליצירת קשר בשדות תמיכה במשתמשים ופרטים ליצירת קשר עם המפתח.
לוחצים על שמירה והמשך > שמירה והמשך.
לוחצים על סמל האפשרויות הנוספות בסרגל הניווט ובוחרים באפשרות הגדרות הפרויקט (קישור ישיר).
מעתיקים את הערך שמופיע בקטע מספר הפרויקט. (בהמשך נעשה שימוש בשדה נפרד מזהה פרויקט ב-Codelab).

בשלב הבא, מקשרים את הפרויקט ב-Apps Script לפרויקט במסוף Cloud.

עוברים לכלי לעריכת סקריפטים ולוחצים על Project Settings (הגדרות הפרויקט) .
בקטע Google Cloud Platform (GCP) Project (פרויקט Google Cloud Platform (GCP)), לוחצים על Change project (שינוי הפרויקט).
מזינים את מספר הפרויקט ולוחצים על Set project (הגדרת הפרויקט).
לאחר מכן לוחצים על עורך כדי להתחיל להוסיף את השירות המתקדם של BigQuery.
לצד Services (שירותים), לוחצים על Add a service (הוספת שירות).
בתיבת הדו-שיח 'הוספת שירות', בוחרים באפשרות BigQuery API ולוחצים על הוספה.

השלב האחרון הוא להפעיל את BigQuery API ב-Cloud Console.

כדי לעשות את זה, עוברים אל מסוף Cloud ולוחצים על APIs & Services > Dashboard. (חשוב לוודא שאתם עדיין באותו פרויקט שיצרתם בשלב 3).
לוחצים על Enable APIs and Services.
מחפשים את BigQuery, בוחרים באפשרות BigQuery API (לא BigQuery Data Transfer API) ולוחצים על Enable כדי להפעיל אותו.

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

העלאה והפעלה של האפליקציה

בכלי לעריכת סקריפטים, מחליפים את בלוק הקוד myFunction() שמוגדר כברירת מחדל בקוד הבא:

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a Sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to Sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}

לוחצים על סמל השמירה .
לצד Code.gs, לוחצים על סמל האפשרויות הנוספות > שינוי השם. שינוי הכותרת מCode.gs לbq-sheets-slides.js.
בשלב הבא נבדוק את הקוד שמבצע שאילתה ב-BigQuery וכותב את התוצאות בגיליון אלקטרוני ב-Google Sheets. אפשר לראות אותו בחלק העליון של runQuery():

SELECT
    LOWER(word) AS word,
    SUM(word_count) AS count
FROM [bigquery-public-data:samples.shakespeare]
GROUP BY word
ORDER BY count
DESC LIMIT 10

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

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

עוברים לפרויקט ב-Cloud Console, לוחצים על סמל האפשרויות הנוספות בסרגל הניווט ובוחרים באפשרות הגדרות הפרויקט.
מעתיקים את הערך שמופיע בקטע מזהה הפרויקט.
חוזרים לכלי לעריכת סקריפטים, מאתרים את המשתנה PROJECT_ID ב-bq-sheets-slides.js ומוסיפים את הערך.
לוחצים על שמירה > הפעלה.
כדי להמשיך, לוחצים על בדיקת הרשאות.

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

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

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

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

אחרי שיופיע ביומן הביצועים הכיתוב 'הביצוע הסתיים', עוברים אל Google Drive ‏ (drive.google.com) ומחפשים את הגיליון האלקטרוני ב-Google Sheets שנקרא 'Most common words in all of Shakespeare's works' (המילים הנפוצות ביותר בכל היצירות של שייקספיר) או השם שהקציתם למשתנה QUERY_NAME, אם עדכנתם אותו:
פותחים את הגיליון האלקטרוני כדי לראות את 10 המילים הנפוצות ביותר ואת הספירה הכוללת שלהן, ממוינות בסדר יורד:

סיכום משימה 1

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

הקוד שבו השתמשתם בשלב bq-sheets-slides.js נמצא גם בתיקייה step1 במאגר GitHub של ה-Codelab הזה בכתובת github.com/googlecodelabs/bigquery-sheets-slides. הקוד הזה מבוסס על הדוגמה המקורית הזו בדף השירותים המתקדמים של BigQuery, שבה מופעלת שאילתה קצת שונה שמחזירה את המילים הכי פופולריות עם 10 תווים או יותר שבהן השתמש שייקספיר. אפשר לראות דוגמה גם במאגר GitHub שלו.

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

אפשר גם להריץ שאילתות באמצעות הדף BigQuery ב-Cloud Console לפני שמריצים אותן ב-Apps Script. כדי למצוא אותו, לוחצים על סמל התפריט ועוברים אל BigQuery UI > SQL workspace (קישור ישיר). לדוגמה, כך השאילתה שלנו מופיעה בממשק הגרפי של BigQuery:

BigQueryUI

5. משימה 2: יצירת תרשים ב-Google Sheets

המטרה של runQuery() היא להשתמש ב-BigQuery ולשלוח את תוצאות הנתונים שלו לגיליון אלקטרוני ב-Google Sheets. בשלב הבא צריך ליצור תרשים באמצעות הנתונים. ניצור פונקציה חדשה בשם createColumnChart() שקוראת לשיטה newChart() של Sheets.

בעורך Apps Script, מוסיפים את הפונקציה createColumnChart() ל-bq-sheets-slides.js אחרי runQuery(). הקוד מקבל את הגיליון ומבקש תרשים עמודות עם כל הנתונים. טווח הנתונים מתחיל בתא A2 כי השורה הראשונה מכילה את כותרות העמודות.

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) Sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in Sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the Sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

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

  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());

  // NEW: Return the spreadsheet object for later use.
  return spreadsheet;
}

יוצרים פונקציה createBigQueryPresentation() כדי להפעיל את runQuery() ואת createColumnChart(). השיטה המומלצת היא להפריד באופן לוגי בין הפונקציונליות של BigQuery לבין הפונקציונליות של יצירת תרשימים:

/**
 * Runs the query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  createColumnChart(spreadsheet);
}

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

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl()); // MOVED HERE
  createColumnChart(spreadsheet);
}

אחרי השינויים שלמעלה (למעט PROJECT_ID), הקובץ bq-sheets-slides.js אמור להיראות כך. הקוד הזה מופיע גם ב-step2 במאגר GitHub.

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a sheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a columnar chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on Sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  createColumnChart(spreadsheet);
}

בכלי לעריכת סקריפטים, שומרים את פרויקט הסקריפט. לאחר מכן בוחרים באפשרות createBigQueryPresentation מרשימת הפונקציות ולוחצים על Run (הפעלה). אחרי שהפעולה מסתיימת, נוצר גיליון אלקטרוני נוסף ב-Google Drive, אבל הפעם יש תרשים בגיליון לצד הנתונים:

גיליון עם תרשים

6. משימה 3: העברת נתוני התוצאות למצגת

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

בעורך Apps Script, מוסיפים את הפונקציה createSlidePresentation() ל-bq-sheets-slides.js אחרי createColumnChart(). כל העבודה על מצגת השקפים מתבצעת בפונקציה הזו. נתחיל ביצירת מצגת ואז נוסיף כותרת וכותרת משנה לשקף הכותרת שמוגדר כברירת מחדל.

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
  * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

השלב הבא ב-createSlidePresentation() הוא לייבא את נתוני התאים מגיליון אלקטרוני ב-Google Sheets אל מצגת השקפים החדשה. מוסיפים את קטע הקוד הזה לפונקציה:

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

השלב האחרון ב-createSlidePresentation() הוא להוסיף עוד שקף, לייבא את התרשים מהגיליון האלקטרוני ולהחזיר את האובייקט Presentation. מוסיפים את קטע הקוד הזה לפונקציה:

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

עכשיו, אחרי שהפונקציה הושלמה, נסתכל שוב על החתימה שלה. הפרמטר createSlidePresentation() מחייב פרמטרים של גיליון אלקטרוני ושל אובייקט תרשים. כבר ביצענו התאמה ב-runQuery() כדי להחזיר את האובייקט Spreadsheet, אבל אנחנו צריכים לבצע שינוי דומה ב-createColumnChart() כדי שהוא יחזיר אובייקט של תרשים (EmbeddedChart). חוזרים אל createColumnChart() ומוסיפים את קטע הקוד הבא לסוף הפונקציה:

  // NEW: Return the chart object for later use.
  return chart;
}

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

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet); // UPDATED
  var deck = createSlidePresentation(spreadsheet, chart); // NEW
  Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}

שומרים ומריצים את createBigQueryPresentation() שוב. אבל לפני שהיא תפעל, האפליקציה שלכם צריכה עוד קבוצת הרשאות מהמשתמש כדי להציג ולנהל מצגות ב-Google Slides. אחרי שתבדקו ותאשרו את ההרשאות האלה, האפליקציה תפעל כמו קודם.
עכשיו, בנוסף לגיליון האלקטרוני שנוצר, אמורה להתקבל גם מצגת חדשה ב-Slides עם 3 שקפים (שם, טבלת נתונים, תרשים נתונים), כמו שמוצג בהמשך:

7. סיכום

מעולה, יצרתם אפליקציה שמשתמשת בשני הצדדים של Google Cloud. הוא מבצע בקשה ל-Google BigQuery שיוצרת שאילתה באחד ממערכי הנתונים הציבוריים שלו, יוצר גיליון אלקטרוני ב-Google Sheets לאחסון התוצאות, מוסיף תרשים שמבוסס על הנתונים, ולבסוף יוצר מצגת ב-Google Slides שכוללת את הנתונים ואת תוצאות התרשים מהגיליון האלקטרוני.

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

אחרי השינויים מהמשימה האחרונה (חוץ מ-PROJECT_ID), הקובץ bq-sheets-slides.js אמור להיראות כך:

/**
 * Copyright 2018 Google LLC
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// File name for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud Console Project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');

/**
 * Runs a BigQuery query; puts results into a spreadsheet. You must turn on
 * the BigQuery advanced service before you can run this code.
 * @see https://developers.google.com/apps-script/advanced/bigquery#run_query
 * @see https://github.com/googleworkspace/apps-script-samples/blob/master/advanced/bigquery.gs
 *
 * @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
 */
function runQuery() {
  // Replace sample with your own BigQuery query.
  var request = {
    query:
        'SELECT ' +
            'LOWER(word) AS word, ' +
            'SUM(word_count) AS count ' +
        'FROM [bigquery-public-data:samples.shakespeare] ' +
        'GROUP BY word ' +
        'ORDER BY count ' +
        'DESC LIMIT 10'
  };
  var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
  var jobId = queryResults.jobReference.jobId;

  // Wait for BigQuery job completion (with exponential backoff).
  var sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
  }

  // Get all results from BigQuery.
  var rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  // Return null if no data returned.
  if (!rows) {
    return Logger.log('No rows returned.');
  }

  // Create the results spreadsheet.
  var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
  var sheet = spreadsheet.getActiveSheet();

  // Add headers to sheet.
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name.toUpperCase();
  });
  sheet.appendRow(headers);

  // Append the results.
  var data = new Array(rows.length);
  for (var i = 0; i < rows.length; i++) {
    var cols = rows[i].f;
    data[i] = new Array(cols.length);
    for (var j = 0; j < cols.length; j++) {
      data[i][j] = cols[j].v;
    }
  }

  // Start storing data in row 2, col 1
  var START_ROW = 2;      // skip header row
  var START_COL = 1;
  sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);

  // Return the spreadsheet object for later use.
  return spreadsheet;
}

/**
 * Uses spreadsheet data to create a column chart.
 * @param {Spreadsheet} Spreadsheet containing results data
 * @returns {EmbeddedChart} Visualizes the results
 * @see https://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
 */
function createColumnChart(spreadsheet) {
  // Retrieve the populated (first) sheet.
  var sheet = spreadsheet.getSheets()[0];
  // Data range in sheet is from cell A2 to B11.
  var START_CELL = 'A2';  // skip header row
  var END_CELL = 'B11';
  // Place chart on sheet starting on cell E5.
  var START_ROW = 5;      // row 5
  var START_COL = 5;      // col E
  var OFFSET = 0;

  // Create & place chart on the sheet using above values.
  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.COLUMN)
     .addRange(sheet.getRange(START_CELL + ':' + END_CELL))
     .setPosition(START_ROW, START_COL, OFFSET, OFFSET)
     .build();
  sheet.insertChart(chart);
  
  // Return the chart object for later use.
  return chart;
}

/**
 * Create presentation with spreadsheet data and a chart
 * @param {Spreadsheet} Spreadsheet containing results data
 * @param {EmbeddedChart} Sheets chart to embed on a slide
 * @returns {Presentation} Slide deck with the results
 * @see https://developers.google.com/apps-script/reference/slides/presentation
 */
function createSlidePresentation(spreadsheet, chart) {
  // Create the presentation.
  var deck = SlidesApp.create(QUERY_NAME);

  // Populate the title slide.
  var [title, subtitle] = deck.getSlides()[0].getPageElements();
  title.asShape().getText().setText(QUERY_NAME);
  subtitle.asShape().getText().setText('using Google Cloud Console and Google Workspace APIs:\n' +
    'Google Apps Script, BigQuery, Sheets, Slides');

  // Data range to copy is from cell A1 to B11
  var START_CELL = 'A1';  // include header row
  var END_CELL = 'B11';
  // Add the table slide and insert an empty table on it with
  // the dimensions of the data range; fails if the sheet is empty.
  var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  var sheetValues = spreadsheet.getSheets()[0].getRange(
      START_CELL + ':' + END_CELL).getValues();
  var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);

  // Populate the table with spreadsheet data.
  for (var i = 0; i < sheetValues.length; i++) {
    for (var j = 0; j < sheetValues[0].length; j++) {
      table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
    }
  }

  // Add a chart slide and insert the chart on it.
  var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
  chartSlide.insertSheetsChart(chart);

  // Return the presentation object for later use.
  return deck;
}

/**
 * Runs a BigQuery query, adds data and a chart to a spreadsheet,
 * and adds the data and chart to a new slide presentation.
 */
function createBigQueryPresentation() {
  var spreadsheet = runQuery();
  Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
  var chart = createColumnChart(spreadsheet);
  var deck = createSlidePresentation(spreadsheet, chart);
  Logger.log('Results slide deck created: %s', deck.getUrl());
}

אפשר למצוא את דוגמת הקוד הזו גם בתיקייה final במאגר GitHub.

8. מקורות מידע נוספים

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

מקורות מידע על האפליקציה הזו

מאמרי עזרה

סרטונים

חדשות ועדכונים

הבלוג של Google Cloud Platform
בלוג ניתוח הנתונים של Google Cloud
בלוג Google Developers
Twitter: ‏ Google Developers ‏ (@googledevs)
בלוג המפתחים של Google Workspace
טוויטר: Google Workspace Developers (@workspacedevs)

Codelabs נוספים

רמה למתחילים

‫[Google Sheets] Fundamentals of Apps Script with Google Sheets
‫[REST APIs] שימוש בממשקי Google Workspace ו-Google API כדי לגשת לקבצים ולתיקיות ב-Google Drive

רמה בינונית

[Apps Script] CLASP כלי שורת פקודה של Apps Script
‫[Apps Script] Google Workspace Add-ons for Gmail
‫[Apps Script] בוטים בהתאמה אישית ל-Hangouts Chat
‫[REST APIs] שימוש ב-Google Sheets ככלי ליצירת דוחות באפליקציה
‫[REST APIs] יצירת מצגות ב-Google Slides באמצעות BigQuery API

9. השלב הבא: אתגרי תכנות

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

אפליקציה – לא רוצים להיות מוגבלים לשימוש ב-JavaScript או בהגבלות שמטיל Apps Script? העברה של האפליקציה הזו לשפת התכנות המועדפת עליך שמשתמשת בממשקי REST API ל-Google BigQuery, ‏Sheets ו-Slides.
‫BigQuery – אפשר להתנסות בשאילתה אחרת למערך הנתונים של שייקספיר שמעניין אתכם. שאילתה לדוגמה נוספת זמינה באפליקציית הדוגמה המקורית של Apps Script BigQuery.
‫BigQuery – אפשר להתנסות במערכי נתונים ציבוריים אחרים של BigQuery כדי למצוא מערך נתונים שרלוונטי יותר לכם.
‫BigQuery – קודם לכן הזכרנו שאילתות אחרות שאפשר ליצור באמצעות היצירות של שייקספיר או טבלאות אחרות של נתונים ציבוריים. אפשר למצוא אותן בדף האינטרנט הזה ובמאגר הזה ב-GitHub.
‫Sheets – אפשר להתנסות בסוגים אחרים של תרשימים בגלריית התרשימים.
‫Sheets ו-BigQuery – שימוש במערך נתונים גדול של גיליון אלקטרוני משלכם. בשנת 2016, צוות BigQuery הציג תכונה שמאפשרת למפתחים להשתמש בגיליון אלקטרוני כמקור נתונים. מידע נוסף זמין במאמר שילוב של Google BigQuery עם Google Drive.
‫Slides (שקפים) – הוספת שקפים אחרים למצגת שנוצרה, כמו תמונות או נכסים אחרים שקשורים לניתוח הביג דאטה. כאן אפשר למצוא את מסמכי העזר של שירות Slides המובנה.
‫Google Workspace – שימוש בשירותים אחרים של Google Workspace או בשירותי Google מובנים מתוך Apps Script. לדוגמה, Gmail, יומן Google,‏ Docs,‏ Drive, מפות Google,‏ Analytics,‏ YouTube וכו', וגם שירותים מתקדמים אחרים. מידע נוסף זמין בסקירה הכללית של ההפניות לשירותים המובנים ולשירותים המתקדמים.