‫Google משתמשת בטכנולוגיית AI כדי לתרגם תוכן לשפה המועדפת עליך. בתרגומים כאלו עשויות להיות שגיאות.

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

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

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

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

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

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

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

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

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

‫Google Apps Script ו-BigQuery

‫Google Apps Script היא פלטפורמת פיתוח של Google Workspace שפועלת ברמה גבוהה יותר מממשקי ה-API של Google REST. זו סביבת פיתוח ואירוח אפליקציות ללא שרת (serverless) שזמינה לכל רמות המיומנות של המפתחים. במילים אחרות, Apps Script היא סביבת זמן ריצה של JavaScript ללא שרתים, שמאפשרת אוטומציה, הרחבה ושילוב של 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 ומערכות חיצוניות. שירות מתקדם הוא בעצם מעטפת דקה סביב Google Workspace API או 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 Console ולוחצים על 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. אפשר לראות אותו בחלק העליון של 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 Data Analytics
בלוג 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 וכו', וגם שירותים מתקדמים אחרים. מידע נוסף זמין בסקירה הכללית של ההפניות לשירותים המובנים ולשירותים המתקדמים.