יסודות של Apps Script עם Google Sheets #1: פקודות מאקרו ופונקציות בהתאמה אישית

1. ברוכים הבאים ל-Apps Script

מה זה Apps Script?

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

התכונות של Apps Script כוללות:

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

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

שירות הגיליונות האלקטרוניים

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

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

מה תלמדו

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

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

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

בקטע הבא מוסבר על התוכן של ה-codelab הזה.

2. מבוא

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

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

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

בהמשך מפורטים המושגים והדרישות שקשורים ל-codelab הזה.

מה תלמדו

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

מה נדרש

• היכרות בסיסית עם JavaScript
• היכרות בסיסית עם Google Sheets
• יכולת לקרוא Sheets A1 Notation

סיימתם את ההקדמות. כדי להתחיל לעבוד עם פקודות מאקרו, עוברים לקטע הבא.

3. יצירת מאקרו ב-Sheets

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

בקטע הזה מוסבר איך ליצור מאקרו ב-Sheets. בקטע הבא מוסבר איך יוצרים מאקרו באמצעות Apps Script.

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

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

עותק של הגיליון האלקטרוני לדוגמה לשימושכם ממוקם בתיקיית Google Drive שלכם, והוא נקרא 'Copy of Top 10 Highest Grossing Films (2018)'.

יצירת מאקרו

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

1. לוחצים על התא A1 כדי להציב את הסמן בשורה. זו שורת הכותרת.
2. בתפריט, בוחרים באפשרות תוספים > רכיבי מאקרו > הקלטת מאקרו.

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

3. בתיבת הדו-שיח 'מאקרו', בוחרים באפשרות הפניה יחסית.

4. בוחרים באפשרות שורה 1.

5. משנים את צבע המילוי של השורה העליונה מלבן למג'נטה כהה 3.

6. משנים את צבע הטקסט בשורה העליונה משחור ללבן.

7. כדי להדגיש את הטקסט, מקישים על Ctrl+B (או על Cmd+B ב-macOS).
8. כדי להקפיא את השורה העליונה, בוחרים באפשרות תצוגה > הקפאה > שורה אחת.

9. לוחצים על שמירה בתיבת הדו-שיח של המאקרו. בתיבת דו-שיח חדשה תתבקשו לתת שם למאקרו. מזינים את השם Header (כותרת) ולוחצים על שמירה.

באמצעות ממשק המשתמש של Sheets, יצרתם מאקרו שמתמחה בעיצוב כותרות.

הפעלת המאקרו

כדי להחיל את פקודת המאקרו החדשה ב-Sheets, פועלים לפי ההוראות הבאות:

1. כדי ליצור גיליון, לוחצים על 'הוספת גיליון' .

2. בגיליון החדש, מוסיפים טקסט לתאים A1:C2. אתם יכולים להשתמש בדוגמאות הבאות של קלט:

3. מדגישים את השורה הראשונה.

4. כדי להחיל את המאקרו על האזור שנבחר, לוחצים על תוספים> רכיבי מאקרו> כותרת.
5. מאשרים את המאקרו לפי ההוראות במסך.

6. חוזרים על שלב 4 כדי להריץ שוב את המאקרו (ההרשאה מפסיקה את ההרצה הראשונה).

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

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

4. פקודות מאקרו בכלי לעריכת סקריפטים

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

הכלי לעריכת סקריפטים

אחרי שיוצרים מאקרו, אפשר לראות את הקוד שלו. כדי לראות את סקריפט המאקרו, לוחצים על תוספים > Apps Script כדי לפתוח את עורך הקוד של Apps Script בדפדפן.

עורך הסקריפטים מאפשר לכם לכתוב קוד ב-Apps Script ולהריץ את הסקריפטים האלה בשרתי Google.

ניתוח של macros.gs

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

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

השורה הראשונה היא הערה שמשפיעה על ההרשאה:

/** @OnlyCurrentDoc */

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

כדי להבין איך Apps Script מייצג את ההוראות של פקודת המאקרו, אפשר לעיין בפונקציה:

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
};

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

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

var spreadsheet = SpreadsheetApp.getActive();

בדוגמה הזו, הפונקציה getActive() מחזירה אובייקט שמייצג את קובץ הגיליון האלקטרוני הפעיל הנוכחי ב-Sheets ומגדירה אותו למשתנה החדש spreadsheet.

var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();

השורות האלה תואמות לפעולה של לחיצה על השורה הראשונה כדי להדגיש אותה. הפעולה הזו נקראת הפעלה. השורה הראשונה שומרת את הגיליון הנוכחי במשתנה sheet, והשורה השנייה מקבלת את השורה הראשונה כולה באמצעות השיטה getRange() ואז קוראת ל-activate() כדי להפעיל אותה. השורה הראשונה מצוינת באמצעות מספרי השורה והעמודה הספציפיים. הקריאה spreadsheet.getCurrentCell().getRow() מחזירה את מספר השורה הנוכחית, ואילו הקריאה sheet.getMaxColumns() מחזירה את המספר המקסימלי של העמודות בגיליון.

spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');

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

• הפונקציה getActiveRangeList() מחזירה את RangeList הפעיל הנוכחי ב-spreadsheet. במקרה הזה, זו פשוט השורה הראשונה שהשורה הקודמת הפעילה.
• השיטות setBackground(color) ו- setFontColor(color) משנות את מאפייני הצבע של התאים בטווח הפעיל.
• ‫setFontWeight(fontWeight) משנה את עובי הגופן של התאים בטווח הפעיל.

לבסוף, השורה האחרונה מקפיאה את השורה הראשונה של המאקרו:

spreadsheet.getActiveSheet().setFrozenRows(1);

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

בקטע הבא נתמקד בשינוי הקוד של הפונקציה Header() כדי להראות איך אפשר להשתמש בעורך הסקריפטים כדי להתאים אישית עוד יותר את פקודות המאקרו.

התאמה אישית של פקודות מאקרו באמצעות Apps Script

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

שינוי התאים שהושפעו

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

1. בכלי לעריכת סקריפטים, מחליפים את sheet.getMaxColumns() ב-10. העריכה הזו משנה את הטווח של התאים שהמאקרו משפיע עליהם בגיליון האלקטרוני.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
    /* sheet.getMaxColumns() replaced with 10.*/
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

2. כדי לשמור את התסריט, לוחצים על סמל השמירה .
3. כדי לשנות את שם הפרויקט, מזינים 'Macros and Custom Functions' (מאקרו ופונקציות בהתאמה אישית) בתור שם הפרויקט החדש ולוחצים על שינוי שם.
4. כדי ליצור גיליון, לוחצים על סמל הוספת גיליון ב-Sheets.

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

בגיליון החדש אמורה להופיע התוצאה הבאה:

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

בשלב הבא נלמד איך להתאים אישית את צבעי המאקרו.

שינוי הצבעים של מאקרו

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

ההוראות האלה מתמקדות בשינוי צבע הרקע של המאקרו:

1. ב-Sheets, חוזרים לגיליון שמכיל את הנתונים המקוריים (גיליון 1).
2. לוחצים על השורה הראשונה כדי להדגיש אותה.
3. בעורך הסקריפטים, מחליפים את צבע הרקע #4c1130 בצבע #afeeee. הערכים האלה מייצגים צבעים שונים באמצעות סימון הקסדצימלי.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
    /* #4c1130 replaced with #afeeee.*/
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

4. כדי לשמור את התסריט, לוחצים על סמל השמירה .
5. ברשימת הפונקציות, בוחרים באפשרות Header ולוחצים על הרצה.

ב-Sheets, צבע המילוי של הרקע של 10 העמודות הראשונות בשורה הראשונה משתנה לצבע טורקיז מותאם אישית:

אם משנים את הסימון של צבע הקסדצימלי בפרמטרים של setBackground(color) מ-#4c1130 (מג'נטה כהה 3) ל-#afeeee (טורקיז בהיר, אפשרות שלא נגישה בתפריט הצבעים שמוגדר כברירת מחדל ב-Sheets), משנים את מאפיין הצבע של הרקע של המאקרו.

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

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

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
  .setFontColor('#191970')/* #ffffff replaced with #191970.*/
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

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

חוזרים ל-Sheets ורואים שצבע הטקסט של שורת הכותרת הוא עכשיו כחול כהה.

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

5. כתיבת הסקריפט הראשון: פונקציות מותאמות אישית

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

בקטע הזה מוסבר איך ליצור פונקציה מותאמת אישית ב-Apps Script שמבצעת המרה של ערך כספי.

יצירת קובץ סקריפט

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

1. כדי ליצור קובץ Apps Script, חוזרים לעורך הסקריפטים.
2. לצד Files (קבצים), לוחצים על Add a file (הוספת קובץ) > Script (סקריפט).
3. נותנים שם לקובץ הסקריפט החדש customFunctions ומקישים על Enter. ‫(Apps Script מוסיף אוטומטית את הסיומת .gs לשם קובץ הסקריפט).

בתוך העורך מופיעה כרטיסייה חדשה בשם customFunctions.gs.

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

המרת דולר אמריקני לפרנק שווייצרי

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

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

1. ב-Sheets, לוחצים לחיצה ימנית על עמודה H.
2. בתפריט שמופיע, לוחצים על הוספת 1 מימין.

3. נותנים לעמודה את התווית Worldwide_Gross (Swiss francs) בתא I1.

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

4. ב-customFunctions.gs, מחליפים את הקוד של myFunction() בקוד הבא:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The converted total of Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

זהו הקוד שימיר דולר ארה"ב לפרנק שוויצרי. נסו את ההוראות שבהמשך כדי להריץ פונקציה בהתאמה אישית בגיליונות אלקטרוניים.

5. כדי לשמור את התסריט, לוחצים על סמל השמירה .
6. ב-Sheets, בוחרים בתא I2.
7. בסרגל הפונקציות, מזינים =USDTOCHF(H2).

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

8. מזיזים את הסמן לפינה השמאלית התחתונה של התא I2 ולוחצים על התיבה הכחולה הקטנה (הסמן אמור להפוך ל- כשמצביעים על התיבה הכחולה).
9. גוררים את התיבה הכחולה כלפי מטה כדי לסמן את הטווח I3:I11.

בעמודה I מופיעים עכשיו ערכי ההמרה של הדולר האמריקאי לפרנק שווייצרי, שמופיעים בעמודה H.

כל הכבוד, יצרת את הפונקציה הראשונה בהתאמה אישית. בקטע הבא מוסבר הקוד שמרכיב את USDTOCHF().

ניתוח של USDTOCHF()

ההערות הראשוניות מפרטות את מטרת הקוד:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */

בלוקים של הערות כמו זה משמשים לעיתים קרובות בתכנות כדי להסביר מה הפונקציות עושות.

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

באמצעות ההערות, Apps Script משתמש ב-JSDoc כדי לעזור לכם לתעד את הקוד וליצור רמזים להשלמה אוטומטית. בהמשך מוסבר איך כל הערה שמשמשת ב-USDTOCHF() עוזרת לכם בפיתוח של Apps Script:

• ‫@param: אפשר להשתמש בהערה @param כדי לתאר כל פרמטר שמועבר לפונקציה.
• ‫@return: אפשר להשתמש בהערה @return כדי לתאר את מה שהפונקציה מחזירה.
• ‫@customfunction: תמיד צריך להוסיף את @customfunction לתגובת התיעוד של כל פונקציה מותאמת אישית. ההערה הזו מודיעה ל-Sheets להשלים אוטומטית את הפונקציה המותאמת אישית, בדיוק כמו ש-Sheets משלים אוטומטית פונקציות מובנות כשמזינים שם של פונקציה בתא, כמו שרואים בהמשך:

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

עכשיו נתמקד בקוד בפונקציה USDTOCHF():

function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

כמו שצוין קודם, הפונקציה USDTOCHF() לוקחת את המשתנה המספרי dollars, מכפילה אותו בשער חליפין קבוע ומחזירה ערך שהומר לפרנק שווייצרי במשתנה המספרי swissFrancs. פרמטר הקלט הוא הערך שמופיע בתא שצוין כשמוסיפים את הפונקציה המותאמת אישית לתא. בדוגמה הזו, סכומי הדולרים של הקלט מגיעים מעמודה H. ערך הפלט swissFrancs מוצב בתא של הפונקציה (עמודה I בדוגמה הזו).

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

שרשור של קידומת למחרוזת

נניח שרציתם שהפלט המספרי של הפונקציה USDTOCHF() יכלול את הקידומת של הפרנק השווייצרי CHF. אפשר לעשות את זה באמצעות Apps Script על ידי שימוש באופרטור השרשור (+),, כמו שמוצג בהוראות הבאות:

1. בכלי לעריכת סקריפטים, מעדכנים את ההערה @return כדי להחזיר מחרוזת במקום מספר.
2. משנים את return swissFrancs ל-return 'CHF' + swissFrancs.

האופרטור + מוסיף את המחרוזת CHF לתחילת הערך שמופיע ב-swissFrancs. הקוד אמור להיראות כך:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {string} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99;
  return 'CHF' + swissFrancs;
}

3. כדי לשמור את התסריט, לוחצים על סמל השמירה .

המחרוזת של הפרנק השווייצרי מופיעה עכשיו לפני הערכים בעמודה I:

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

מתקדם: אחזור נתונים חיצוניים

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

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

function USDTOCHF(dollars){
  // Gets a cache that is common to all users of the script.
  var cache = CacheService.getScriptCache();

  // Accesses the memory location (rates.CHF) of the script cache.
  var rate = cache.get('rates.CHF');

  // If a cache miss occurs, the program fetches the current
  // CHF rate from an API and stores the rate in the cache
  // for later convenience.
  if (!rate) {
    var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
    var result = JSON.parse(response.getContentText());
    rate = result.rates.CHF;
    cache.put('rates.CHF', rate);
  }
  // Converts dollars to CHF according to the latest rate.
  var swissFrancs = dollars * rate;
  // Returns the CHF value.
  return 'CHF' + swissFrancs;
}

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

הנחיות לגבי פונקציות מותאמות אישית

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

• אל תיצרו פונקציות מותאמות אישית שנדרש עבורן אישור משתמש. במקום זאת, כדאי ליצור פונקציות מותאמות אישית לביצוע משימות פשוטות יותר, כמו חישוב נתוני מדגם, עריכת טקסט וכו'. אפשר לעבור אל שימוש בשירותי Apps Script.
• אל תתנו לפונקציה מותאמת אישית שם זהה לשם של פונקציה מובנית אחרת, ואל תסיימו את השם בקו תחתון. מעיינים בהנחיות למתן שמות.
• אל תעבירו ארגומנטים של משתנים לפונקציות מותאמות אישית. אפשר להעביר רק ערכים דטרמיניסטיים (קבועים) לפונקציות מותאמות אישית כארגומנטים. העברת ארגומנטים של משתנים, כמו התוצאה של =RAND(), תשבור את הפונקציה המותאמת אישית. הנחיות בנושא ארגומנטים
• אל תיצרו פונקציות שייקח יותר מ-30 שניות להשלים אותן. אם הפונקציה תימשך יותר זמן, תתרחש שגיאה, לכן חשוב לשמור על קוד הפונקציה פשוט ומוגבל בהיקף. מומלץ שהחישובים שמתבצעים בפונקציות בהתאמה אישית יהיו פשוטים ככל האפשר. הנחיות בנושא ערכי החזרה

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

6. סיכום

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

מה נכלל

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

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

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

אפשר למצוא את ה-codelab הבא בכתובת גיליונות אלקטרוניים, Sheets וטווחים.