היסודות של Apps Script עם Google Sheets #5: נתונים מתרשימים וממצגות ב-Slides

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

1. מבוא

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

מה תלמדו

  • כיצד ליצור תרשים קו עם Apps Script.
  • איך מייצאים תרשימים במצגת חדשה ב-Slides.
  • איך להציג תיבות דו-שיח למשתמש ב-Sheets.

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

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

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

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

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

2. הגדרה

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

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

45a3e8814ecb07fc.png

  1. כדי לפתוח את עורך הסקריפטים, לוחצים על תוספים&gt. סקריפט של אפליקציות.

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

9b9caf6c1e9de34b.png

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

3. יצירת תרשים ב-Sheets באמצעות Apps Script

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

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

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

יישום

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

איך עושים את זה?

  1. בעורך הסקריפטים של Apps, מוסיפים את הפונקציה הבאה לסוף של סקריפט הסקריפט Code.gs' אחרי הפונקציה onOpen():
/**
 * Creates and inserts an embedded
 * line chart into the active sheet.
 */
function createEmbeddedLineChart() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var chartDataRange = sheet.getRange(
    'Dates and USD Exchange Rates dataset!A2:F102');
  var hAxisOptions = {
    slantedText: true,
    slantedTextAngle: 60,
    gridlines: {
      count: 12
    }
  };
  
  var lineChartBuilder = sheet.newChart().asLineChart();
  var chart = lineChartBuilder
    .addRange(chartDataRange)
    .setPosition(5, 8, 0, 0)
    .setTitle('USD Exchange rates')
    .setNumHeaders(1)
    .setLegendPosition(Charts.Position.RIGHT)
    .setOption('hAxis', hAxisOptions)
    .setOption("useFirstColumnAsDomain", true)
    .build();
 
  sheet.insertChart(chart);  
}
  1. שומרים את פרויקט הסקריפט.

בדיקת קוד

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

השורות הראשונות מגדירות את שלושת המשתנים הבאים:

  • sheet: הפניה לגיליון הפעיל הנוכחי.
  • chartDataRange: טווח הנתונים שאנחנו רוצים להציג באופן חזותי. הקוד משתמש בסימון A1 כדי לציין שהטווח מכסה תאים A2 עד F102 בגיליון שנקרא מערך נתונים של שערי תאריכים ודולר ארה"ב. כשמציינים את שם הגיליון באופן ספציפי, אנחנו מוודאים שפריט התפריט פועל גם אם גיליון אחר פעיל, מאחר שהטווח תמיד מכסה את מיקום הנתונים. החל משורה 2, אנחנו כוללים את כותרות העמודות ואנחנו מראים רק את 100 התאריכים האחרונים (שורות).
  • hAxisOptions: אובייקט JavaScript בסיסי שכולל מידע על ההגדרות שבהן הקוד משתמש כדי להגדיר את המראה של הציר האופקי. בפרט, הם מגדירים את תוויות הטקסט בציר אופקי בהטיה של 60 מעלות, וגם מגדירים את מספר קווי הרשת האנכיים ל-12.

השורה הבאה יוצרת אובייקט בתרשים תרשים. תרשימים מוטמעים ב-Apps Script נוצרים באמצעות תבנית עיצוב של Build. הסבר מלא על דפוס העיצוב הזה נמצא מחוץ למסגרת מעבדת הקוד הזו, ולכן בשלב זה רק הבנת את השירות Spreadsheet מספק כמה קורסים מסוג EmbeddedChartBuilder. כדי ליצור תרשים, הקוד צריך ליצור אובייקט מוטמע של תרשים, ולהשתמש בשיטות שלו כדי לקבוע את הגדרות התרשים. לאחר מכן הוא קורא לשיטת build() כדי ליצור את האובייקט הסופי EmbeddedChart. הקוד שלך לעולם לא משנה את האובייקט EmbeddedChart ישירות כי כל תצורת התרשים מנוהלת דרך הכיתות של Builder.

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

בקוד שלך, השורה הזו יוצרת את משתנה אובייקט הכלי lineChartBuilder:

var lineChartBuilder = sheet.newChart().asLineChart();

הקוד קורא לשיטה Sheet.newChart() כדי ליצור אובייקט EmbeddedChartBuilder, ולאחר מכן משתמש ב-EmbeddedChartBuilder.asLineChart() כדי להגדיר את סוג הכלי ליצירת EmbeddedLineChartBuilder.

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

  • addRange(range): מגדיר את טווח הנתונים שהתרשים מציג.
  • setPosition(anchorRowPos, anchorColPos, offsetX, offsetY): קובע את מיקום התרשים. כאן, הקוד מתווסף לפינה השמאלית העליונה של התרשים בתא H5.
  • setTitle(title): מגדיר את כותרת התרשים.
  • setNumHeaders(headers): קובעים בכמה שורות או עמודות בטווח הנתונים יש להתייחס ככותרות. כאן, הקוד משתמש בשורה הראשונה בטווח הנתונים ככותרות, כלומר הטקסט שבשורה משמש כתוויות לסדרת הנתונים הנפרדת בתרשים.
  • setLegendPosition(position): העברה של מקרא התרשים לצד ימין של התרשים. השיטה הזו משתמשת בפרמטר Charts.Location כפרמטר.
  • setOption(option, value): מגדיר אפשרויות מורכבות של תרשימים. כאן, הקוד מגדיר את האפשרות hAxis לאובייקט hAxisOptions. יש כמה אפשרויות להגדרה הזו. האפשרויות והערכים האפשריים לכל סוג תרשים מתועדים בגלריית התרשימים של התרשימים. לדוגמה, האפשרויות להגדרה של תרשימי קו תתועד באפשרויות להגדרת תרשים קו. השיטה setOption(option, value) היא נושא מתקדם, לכן כדאי להימנע משימוש בה עד שתתרגלו ליצור תרשימים ב-Apps Script.
  • build(): יצירה והחזרת אובייקט EmbeddedChart לפי ההגדרות שלמעלה.

לבסוף, הקוד מתקשר אל Sheet.insertChart(chart) כדי למקם את התרשים המובנה בגיליון הפעיל.

תוצאות

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

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

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

bbf856699b6d2b45.gif

מזל טוב, יצרתם תרשים קו מוטמע עם Apps Script. בקטע הבא נסביר איך לייצא את התרשים ל-Google Slides.

4. ייצוא התרשימים ל-Slides

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

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

יישום

מכאן מטמיעים את הפונקציה שנקראת תפריט מערך נתונים נוכחי > ייצוא תרשימים ל-Slides. איך עושים את זה?

  1. בעורך הסקריפטים של Apps, מוסיפים את הפונקציה הבאה לסוף של סקריפט הסקריפט Code.gs' אחרי הפונקציה createEmbeddedLineChart():
/**
 * Create a Slides presentation and export
 * all the embedded charts in this spreadsheet
 * to it, one chart per slide.
 */
function exportChartsToSlides() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Fetch a list of all embedded charts in this
  // spreadsheet.
  var charts = [];
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    charts = charts.concat(sheets[i].getCharts());
  }
  
  // If there aren't any charts, display a toast
  // message and return without doing anything
  // else.
  if (charts.length == 0) {
    ss.toast('No charts to export!');
    return;
  }
  
  // Create a Slides presentation, removing the default
  // title slide.
  var presentationTitle =
    ss.getName() + " Presentation";
  var slides = SlidesApp.create(presentationTitle);
  slides.getSlides()[0].remove();  
  
  // Add charts to the presentation, one chart per slide.
  var position = {left: 40, top: 30};
  var size = {height: 340, width: 430};
  for (var i = 0; i < charts.length; i++) {
    var newSlide = slides.appendSlide();
    newSlide.insertSheetsChart(
      charts[i],
      position.left,
      position.top,
      size.width,
      size.height);   
  }
  
  // Create and display a dialog telling the user where to
  // find the new presentation.
  var slidesUrl = slides.getUrl();
  var html = "<p>Find it in your home Drive folder:</p>"
      + "<p><a href=\"" + slidesUrl + "\" target=\"_blank\">"
      + presentationTitle + "</a></p>";
  
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(html)
      .setHeight(120)
      .setWidth(350),
      "Created a presentation!"
  );
}
  1. שומרים את פרויקט הסקריפט.

בדיקת קוד

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

1: קבלת התרשימים

השורות הראשונות מחפשים בגיליון האלקטרוני הפעיל את כל התרשימים המוטמעים, שאוספות אותן במערך charts. השורות האלה משתמשות בשיטה Spreadsheet.getSheets() ובשיטה Sheet.getCharts() כדי לקבל רשימות של גיליונות ותרשימים. השיטה Array.concat() של JavaScript משמשת לצירוף רשימת התרשימים מכל גיליון ל-charts.

2: בודקים שיש תרשימים לייצוא

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

db7e87dcb8010bef.gif

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

3: יצירת מצגת

המשתנה presentationTitle נוצר כדי לכלול את שם הקובץ החדש של המצגת. הוא מוגדר כשם הגיליון האלקטרוני ' עם " Presentation" בסוף המחרוזת. לאחר מכן, הקוד מתקשר לשיטת השירות של Slides SlidesApp.create(name) כדי ליצור מצגת.

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

4: ייצוא התרשימים

בקטע הבא, הקוד מגדיר את position ו-size של אובייקטים ב-JavaScript כדי להגדיר את המיקום של תרשימים שיובאו בשקף ואת גודל התרשים (בפיקסלים).

הקוד מופיע בלופ של כל תרשים ברשימת התרשימים. לכל תרשים, נוצר newSlide עם Presentation.appendSlide(), שמוסיף את השקף לסוף המצגת. משתמשים בשיטה Slide.insertSheetsChart(sourceChart, left, top, width, height) כדי לייבא את התרשים לשקף, עם position ו-size שצוינו.

5: שיתוף מיקום המצגת

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

כדי ליצור תיבת דו-שיח בהתאמה אישית, הקוד צריך להיות ה-HTML שמגדיר את התוכן שלו. הנתון הזה מופיע במשתנה html. התוכן מכיל פסקה קצרה והיפר-קישור. ההיפר-קישור הוא המשתנה presentationTitle, המקושר לכתובת ה-URL של המצגת שסופקה על ידי Presentation.getUrl(). ההיפר-קישור משתמש גם במאפיין target="_blank" כך שהמצגת תיפתח בכרטיסייה חדשה בדפדפן במקום בתיבת הדו-שיח.

ה-HTML מנותח לאובייקט HtmlOutput בשיטת HtmlService.createHtmlOutput(html). האובייקט HtmlOutput מאפשר לקוד להגדיר את גודל תיבת הדו-שיח המותאמת אישית באמצעות HtmlOutput.setHeight(height) ו-HtmlOutput.setWidth(width).

לאחר יצירת htmlOutput, הקוד משתמש בשיטה Ui.showModalDialog(htmlOutput, title) כדי להציג את תיבת הדו-שיח עם הכותרת הנתונה.

תוצאות

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

  1. אם עדיין לא עשית זאת, שמור את פרויקט הסקריפט שלך בעורך הסקריפטים של Apps.
  2. פותחים את הגיליון האלקטרוני ולוחצים על תפריט מערך הנתונים > Chart "Dates and USD ExchangeRates" כדי ליצור תרשים לייצוא. הוא יופיע בעגינה לתא H5 בגיליון הפעיל.
  3. לוחצים על פריט התפריט הצגת מערך נתונים &gt וייצוא תרשימים ל-Slides. ייתכן שתתבקשו לאשר מחדש את הסקריפט.
  4. אתם אמורים לראות את הסקריפט מעבד את הבקשה ומציג את תיבת הדו-שיח המותאמת אישית.
  5. כדי לפתוח את המצגת החדשה של Slides, לוחצים על הקישור תאריכים ושער החליפין של דולר ארה"ב:

51326ceaeb3e49b2.gif

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

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

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

5. סיכום

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

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

כן לא

מה למדת

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

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

השלמת את הפלייליסט הזה. עם זאת, עדיין יש מידע נוסף על Apps Script.

יש לנו כמה מקורות מידע שיעזרו לכם:

שליחת סקריפט מהנה!

האם הפלייליסט הזה של Codelab הועיל לך?

כן לא

רוצה לראות ערכות קוד של Apps Script נוספות בעתיד?

כן לא