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

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

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

יש הרבה כלים שבאמצעותם מדעני נתונים יכולים לבצע ניתוחי 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.

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

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

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

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

אפליקציית Node.js שלהם לעומת אפליקציית Apps Script שלנו.
הם משתמשים בממשקי API בארכיטקטורת REST בזמן שאנחנו משתמשים בשירותי 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

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

מתחילים

בינונית

בקיאים

איזה דירוג מגיע לדעתך לחוויה שלך עם Apps Script באופן ספציפי?

מתחילים

בינונית

בקיאים

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

מתחילים

בינונית

בקיאים

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

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

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

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

Google Apps Script ו-BigQuery

Google Apps Script היא פלטפורמת פיתוח של Google Workspace שפועלת ברמה גבוהה יותר מממשקי ה-API בארכיטקטורת REST של Google. מדובר בסביבה ללא שרת (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 בענן.

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

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

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

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

גישה אל Sheets ואל שקפים מ-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, לוחצים על Untitled project, מזינים שם לפרויקט ולוחצים על Rename.

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

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

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

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

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

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

לשם כך, צריך לעבור אל מסוף Cloud וללחוץ על APIs & שירותים > מרכז השליטה. (צריך לוודא שאתם עדיין נמצאים באותו הפרויקט שיצרתם בשלב 3).
לוחצים על Enable APIs and Services.
מחפשים את המילה big query, בוחרים את 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, לוחצים על סמל האפשרויות הנוספות בסרגל הניווט ובוחרים באפשרות Project settings (הגדרות הפרויקט).
מעתיקים את הערך שמופיע בקטע Project ID.
חוזרים לעורך App Script, מאתרים את המשתנה PROJECT_ID ב-bq-sheets-slides.js ומוסיפים את הערך.
לוחצים על שמירה > הפעלה.
לוחצים על בדיקת ההרשאות כדי להמשיך.

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

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

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

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

כשביומן הביצוע יהיה כתוב 'הביצוע הושלם', נכנסים ל-Google Drive (drive.google.com) ומחפשים את הגיליון האלקטרוני ב-Google Sheets עם השם "המילים הנפוצות ביותר בכל היצירות של שייקספיר". (או השם שהקציתם למשתנה 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 (קישור ישיר). לדוגמה, כך השאילתה שלנו מופיעה בממשק הגרפי של BigQuery:

BigQueryUI

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

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

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

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

אלה הפעולות שעשיתם מבחינה טכנית. באופן כללי, עברתם מניתוח Big Data לתוצאה שאפשר להציג לבעלי עניין – והכול באופן אוטומטי באמצעות קוד. אנחנו מקווים שהדוגמה הזו תעניק לכם השראה להתאים אותה אישית לפרויקטים שלכם. בסיום שיעור ה-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
Twitter: Google Workspace Developers (@workspacedevs)

Codelabs אחרים

רמה למתחילים

[Google Sheets] העקרונות הבסיסיים של Apps Script עם Google Sheets
[API ל-REST] שימוש ב-Google Workspace וב- ממשקי Google API לגישה לקבצים תיקיות ב-Google Drive

רמה בינונית

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

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

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

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