1. מבוא
ברוכים הבאים לחלק החמישי בפלייליסט של Codelab בנושא יסודות של Apps Script עם Google Sheets. ב-codelab הזה נלמד איך להשתמש בשירות הגיליונות האלקטרוניים ב-Apps Script כדי ליצור תרשים של מערך נתונים. בנוסף, תלמדו איך להשתמש בשירות Slides כדי לייצא את התרשים למצגת חדשה ב-Google Slides.
מה תלמדו
- איך יוצרים תרשים קו באמצעות Apps Script.
- איך מייצאים תרשימים למצגת חדשה ב-Slides.
- איך מציגים תיבות דו-שיח למשתמש ב-Sheets.
לפני שמתחילים
זוהי סדנת ה-codelab החמישית בפלייליסט Fundamentals of Apps Script with Google Sheets (יסודות של Apps Script עם Google Sheets). לפני שמתחילים את ה-codelab הזה, חשוב להשלים את ה-codelabs הקודמים:
- פקודות מאקרו ופונקציות מותאמות אישית
- גיליונות אלקטרוניים, גיליונות וטווחים
- עבודה עם נתונים
- עיצוב נתונים
מה נדרש
- הבנה של הנושאים הבסיסיים ב-Apps Script שמוסברים ב-codelabs הקודמים בפלייליסט הזה.
- היכרות בסיסית עם עורך הקוד של Apps Script
- היכרות בסיסית עם Google Sheets
- יכולת לקרוא Sheets A1 Notation
- היכרות בסיסית עם JavaScript והמחלקה
Stringשלה
2. הגדרה
לפני שממשיכים, צריך גיליון אלקטרוני עם נתונים. כמו קודם, צירפנו גיליון נתונים שאפשר להעתיק לתרגילים האלה. כך עושים את זה:
- לוחצים על הקישור הזה כדי להעתיק את גיליון הנתונים ואז לוחצים על יצירת עותק. הגיליון האלקטרוני החדש ממוקם בתיקייה של Google Drive ונקרא 'עותק של תאריכים ושערי חליפין של דולר ארה"ב'.
- לוחצים על שם הגיליון האלקטרוני ומשנים אותו מ-Copy of Dates and USD Exchange Rates (עותק של תאריכים ושערי חליפין של דולר ארה"ב) ל-Dates and USD Exchange Rates (תאריכים ושערי חליפין של דולר ארה"ב). הגיליון צריך להיראות כך, עם מידע בסיסי על שערי חליפין שונים של דולר ארה"ב בתאריכים שונים:
- כדי לפתוח את עורך הסקריפטים, לוחצים על תוספים> Apps Script.
כדי לחסוך לכם זמן, הוספנו לגיליון האלקטרוני הזה קטע קוד להגדרת תפריט בהתאמה אישית. יכול להיות שהתפריט הופיע כשעותק הגיליון האלקטרוני שלכם נפתח:
אחרי שיוצרים את הגיליון האלקטרוני והפרויקט, אפשר להתחיל את ה-codelab. בקטע הבא נסביר על גרפים ועל טריגרים שמבוססים על זמן.
3. יצירת תרשים ב-Sheets באמצעות Apps Script
נניח שרוצים לעצב תרשים ספציפי כדי להציג מערך נתונים. אפשר להשתמש ב-Apps Script כדי ליצור תרשימים, לערוך אותם ולהוסיף אותם ל-Google Sheets. כשתרשים מוכל בגיליון אלקטרוני, הוא נקרא תרשים מוטמע.
תרשימים משמשים להמחשה חזותית של סדרת נתונים אחת או יותר. בתרשימים מוטמעים, הנתונים שמוצגים בדרך כלל מגיעים מתוך הגיליון האלקטרוני. בדרך כלל, כשמעדכנים את הנתונים בגיליון האלקטרוני, התרשים מתעדכן אוטומטית גם כן ב-Sheets.
אתם יכולים להשתמש ב-Apps Script כדי ליצור תרשימים מוטמעים בהתאמה אישית מאפס, או כדי לעדכן תרשימים קיימים. בקטע הזה מוסבר איך ליצור תרשימים מוטמעים ב-Sheets באמצעות Apps Script והשירות Spreadsheet.
הטמעה
בגיליון האלקטרוני של העתק הנתונים, מערך הנתונים Dates and Exchange Rates (תאריכים ושערי חליפין) מציג את שערי החליפין (לדולר ארה"ב אחד) של מטבעות שונים בתאריכים שונים. תטמיעו פונקציית Apps Script שיוצרת תרשים להמחשת חלק מהנתונים האלה.
כך עושים את זה:
- בעורך Apps Script, מוסיפים את הפונקציה הבאה לסוף הסקריפט
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);
}
- שומרים את פרויקט הסקריפט.
בדיקת קוד
הקוד שהוספתם מטמיע את הפונקציה שמופעלת על ידי פריט התפריט Chart "Dates and USD Exchange Rates dataset" כדי ליצור תרשים קו בסיסי. בואו נבדוק את הקוד.
בשורה הראשונה מגדירים את שלושת המשתנים הבאים:
-
sheet: הפניה לגיליון הפעיל הנוכחי. -
chartDataRange: טווח הנתונים שרוצים להציג. הקוד משתמש בסימון A1 כדי לציין שהטווח מכסה את התאים A2 עד F102 בגיליון שנקרא Dates and USD Exchange Rates dataset. הגדרת שם ספציפי לגיליון מבטיחה שפריט התפריט יפעל גם אם גיליון אחר פעיל, כי הטווח תמיד מכסה את מיקום הנתונים. התחלה בשורה 2 אומרת שאנחנו כוללים את כותרות העמודות, וניצור תרשים רק של 100 התאריכים (השורות) האחרונים. -
hAxisOptions: אובייקט JavaScript בסיסי שכולל מידע על הגדרות מסוימות שהקוד משתמש בהן כדי להגדיר את המראה של הציר האופקי. לדוגמה, הם מגדירים את התוויות של הטקסט בציר האופקי בהטיה של 60 מעלות, ומגדירים את מספר קווי הרשת האנכיים ל-12.
בשורה הבאה נוצר אובייקט line chart builder. תרשימים מוטמעים ב-Apps Script בנויים באמצעות תבנית עיצוב של Builder. הסבר מלא על דפוס העיצוב הזה הוא מעבר להיקף של ה-codelab הזה, אז בשלב הזה חשוב להבין ששירות Spreadsheet מספק כמה מחלקות EmbeddedChartBuilder. כדי ליצור תרשים, הקוד יוצר קודם אובייקט מוטמע של כלי ליצירת תרשימים, משתמש בשיטות שלו כדי להגדיר את הגדרות התרשים, ואז קורא לשיטה build() כדי ליצור את האובייקט הסופי EmbeddedChart. הקוד שלכם אף פעם לא משנה את האובייקט EmbeddedChart ישירות, כי כל הגדרות התרשים מנוהלות באמצעות מחלקות ה-builder.
שירות הגיליון האלקטרוני מספק מחלקה ראשית EmbeddedChartBuilder ומספר מחלקות צאצא של כלי בנייה (כמו EmbeddedLineChartBuilder) שיורשות ממנה. מחלקות הצאצא מאפשרות ל-Apps Script לספק את שיטות ההגדרה של תרשימי ה-Builder שרלוונטיות רק לסוגים מסוימים של תרשימים. לדוגמה, המחלקה EmbeddedPieChartBuilder מספקת את השיטה set3D() שרלוונטית רק לתרשימי עוגה.
בקוד, השורה הזו יוצרת את משתנה אובייקט ה-builder lineChartBuilder:
var lineChartBuilder = sheet.newChart().asLineChart();
הקוד קורא לשיטה Sheet.newChart() כדי ליצור אובייקט EmbeddedChartBuilder, ואז משתמש ב-EmbeddedChartBuilder.asLineChart() כדי להגדיר את סוג ה-builder ל-EmbeddedLineChartBuilder.
הקוד יוצר את התרשים באמצעות lineChartBuilder. החלק הזה של הקוד הוא רק סדרה של קריאות לשיטות כדי להגדיר את הגדרות התרשים, ואחריו קריאה ל-build() כדי ליצור את התרשים. כפי שראיתם ב-codelab הקודם, הקוד משתמש בשרשור שיטות כדי שהקוד יהיה קריא. הסבר על קריאות השיטה:
-
addRange(range): הגדרת טווח הנתונים שמוצג בתרשים. -
setPosition(anchorRowPos, anchorColPos, offsetX, offsetY): קובע את המיקום של התרשים בגיליון. הקוד כאן מוסיף את הפינה הימנית העליונה של התרשים לתא H5. -
setTitle(title): הגדרת שם התרשים. -
setNumHeaders(headers): קובעים כמה שורות או עמודות בטווח הנתונים צריכות להיחשב ככותרות. בדוגמה הזו, הקוד משתמש בשורה הראשונה בטווח הנתונים ככותרות, כלומר הטקסט בשורה הזו משמש כתוויות לסדרות הנתונים הבודדות בתרשים. -
setLegendPosition(position): העברת המקרא של התרשים לצד שמאל של התרשים. השיטה הזו משתמשת ב-enum Charts.Position כפרמטר. -
setOption(option, value): הגדרת אפשרויות מורכבות לתרשים. בדוגמה הזו, הקוד מגדיר את האפשרותhAxisלאובייקטhAxisOptions. יש כמה אפשרויות שאפשר להגדיר באמצעות השיטה הזו. האפשרויות והערכים האפשריים לכל סוג תרשים מתועדים בגלריית התרשימים של Charts API. לדוגמה, האפשרויות שאפשר להגדיר לתרשימי קו מתועדות במאמר אפשרויות הגדרה של תרשימי קו. השיטהsetOption(option, value)היא נושא מתקדם, ולכן מומלץ להימנע משימוש בה עד שתהיו בטוחים יותר ביצירת תרשימים ב-Apps Script. -
build(): יוצר ומחזיר אובייקט מסוגEmbeddedChartבאמצעות ההגדרות שלמעלה.
לבסוף, הקוד קורא ל- Sheet.insertChart(chart) כדי להציב את התרשים שנוצר בגיליון הפעיל.
תוצאות
כדי לראות את פונקציית העיצוב בפעולה:
- אם עדיין לא עשיתם זאת, שומרים את פרויקט הסקריפט בעורך Apps Script.
- לוחצים על פריט התפריט הצגת מערך הנתונים > תרשים 'תאריכים ושערי חליפין של דולר ארה"ב'.
הסקריפט מוסיף עכשיו תרשים חדש משמאל לנתונים:
כל הכבוד, יצרתם תרשים קו מוטמע באמצעות Apps Script. בקטע הבא מוסבר איך לייצא את התרשים ל-Google Slides.
4. ייצוא התרשימים ל-Slides
אחד היתרונות הגדולים של Apps Script הוא שאפשר להעביר נתונים בקלות מאפליקציה אחת של Google Workspace לאפליקציה אחרת. לרוב האפליקציות האלה יש שירות ייעודי ב-Apps Script, בדומה לשירות הגיליון האלקטרוני. לדוגמה, ל-Gmail יש את שירות Gmail, ל-Google Docs יש את שירות המסמכים ול-Google Slides יש את שירות Slides. בעזרת כל השירותים המובנים האלה, אתם יכולים לחלץ נתונים מאפליקציה אחת, לעבד אותם ולכתוב את התוצאה באפליקציה אחרת.
בקטע הזה מוסבר איך לייצא כל תרשים מוטמע בגיליון אלקטרוני ב-Google לתוך מצגת חדשה ב-Google Slides. ב-Sheets מוצגות גם שתי דרכים להצגת הודעות מותאמות אישית למשתמשים.
הטמעה
כאן תטמיעו את הפונקציה שמופעלת על ידי פריט התפריט הצגת מערך הנתונים > ייצוא תרשימים ל-Slides. כך עושים את זה:
- בעורך Apps Script, מוסיפים את הפונקציה הבאה לסוף הסקריפט
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: קבלת התרשימים
השורות הראשונות מחפשות בגיליון האלקטרוני הפעיל את כל התרשימים המוטמעים, ומקבצות אותם במערך charts. השורות האלה משתמשות בשיטה Spreadsheet.getSheets() ובשיטה Sheet.getCharts() כדי לקבל רשימות של גיליונות ותרשימים. השיטה Array.concat() ב-JavaScript משמשת להוספת רשימת התרשימים מכל גיליון ל-charts.
2: בדיקה שיש תרשימים לייצוא
הקוד בודק אם יש תרשימים לייצוא. כדי להימנע מיצירת מצגת ריקה, אם אין תרשימים, הקוד יוצר במקום זאת הודעת טוסט באמצעות Spreadsheet.toast(message). זהו חלון קטן שמופיע בפינה השמאלית התחתונה של Sheets, נשאר למשך כמה שניות ואז נעלם:
אם אין תרשימים לייצא, הקוד יוצר את הודעת הטוסט ויוצא בלי לעשות שום דבר אחר. אם יש תרשימים לייצא, הקוד ממשיך ליצור מצגת בכמה השורות הבאות.
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 service של 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():
- אם עדיין לא עשיתם זאת, שומרים את פרויקט הסקריפט בעורך Apps Script.
- פותחים את הגיליון האלקטרוני ולוחצים על פריט התפריט הצגת מערך הנתונים > תרשים 'תאריכים ומערך נתוני שערי חליפין בדולר ארה"ב' כדי ליצור תרשים לייצוא. הוא יופיע כשהוא מעוגן לתא H5 בגיליון הפעיל.
- לוחצים על פריט התפריט הצגת מערך נתונים > ייצוא תרשימים ל-Slides. יכול להיות שתתבקשו לאשר מחדש את הסקריפט.
- הסקריפט אמור לעבד את הבקשה ולהציג את תיבת הדו-שיח המותאמת אישית.
- כדי לפתוח את המצגת החדשה ב-Slides, לוחצים על הקישור Dates and USD Exchange Rates Presentation (מצגת עם תאריכים ושערי חליפין של דולר ארה"ב):
אם רוצים, אפשר גם להוסיף עוד תרשימים לגיליון האלקטרוני ולבחור מחדש את פריט התפריט כדי ליצור מצגת עם כמה שקפים.
עכשיו אפשר לייצא תרשימים שנוצרו ב-Sheets למצגת ב-Slides. אפשר גם לכתוב קוד כדי ליצור תיבת דו-שיח בהתאמה אישית.
סיימתם את התרגיל האחרון ב-codelab הזה. עוברים לקטע הבא כדי לחזור על מה שלמדתם.
5. סיכום
כל הכבוד, סיימתם את ה-codelab הזה ואת כל הפלייליסט של ה-codelab בנושא יסודות Apps Script עם Google Sheets. אתם יכולים להשתמש בעקרונות שמוסברים בפלייליסט הזה כדי להרחיב את חוויית השימוש ב-Sheets ולחקור את היכולות של Apps Script.
האם ה-codelab הזה היה מועיל?
מה למדתם
- איך יוצרים תרשים קו מוטמע באמצעות Apps Script.
- איך מציגים למשתמש הודעות קצרות ודיאלוגים מותאמים אישית ב-Sheets.
- איך מייצאים תרשים למצגת חדשה ב-Slides.
המאמרים הבאים
סיימתם את הפלייליסט הזה. עם זאת, יש עוד הרבה מידע על Apps Script.
תוכלו לעיין במקורות המידע הבאים:
- תיעוד למפתחים של Apps Script
- מדריכים ל-Apps Script
- מאמרי עזרה בנושא Apps Script
- שאלות לגבי Apps Script ב-StackOverflow
- דוגמאות לקוד Apps Script ב-GitHub
כתיבת סקריפטים מהנה!