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

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

מהו Apps Script?

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

התכונות ב-Apps Script כוללות את:

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

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

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

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

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

מה תלמדו

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

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

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

יש לעבור לקטע הבא כדי לקבל מידע נוסף על תוכן הקוד הזה.

2. מבוא

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

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

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

בהמשך תוכלו לקרוא אילו מושגים ודרישות נכללים ב-Codelab הזה.

מה תלמדו

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

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

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

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

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

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

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

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

5b8aded1bb349ecf.png

עותק של הגיליון האלקטרוני לדוגמה שבו תוכלו להשתמש נמצא בתיקיית Google Drive בשם "עותק של 10 הסרטים הרווחיים ביותר (2018)."

יצירת מאקרו

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

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

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

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

c59f2f12317352d2.gif

  1. בוחרים שורה 1.

1d782ee30c66a02b.gif

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

f7e7abaf76e338c7.png

  1. יש לשנות את השורה העליונה's לצבע הטקסט משחור ללבן.

d5e630acbe83148.png

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

97cb244ffebe8953.png

  1. לוחצים על שמירה בתיבת הדו-שיח של המאקרו. בתיבת דו-שיח חדשה תופיע בקשה לתת שם לרכיב המאקרו. מזינים את השם "כותרת" ולוחצים על שמירה.

b4610a54340da518.gif

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

4ed7fbed18ea3681.png

הפעלה של מאקרו

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

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

927c012b4e11475b.png

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

c3aadaef52a609bf.png

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

cfe36fcf833d0bd7.gif

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

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

7c7130a4a697bd92.png

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

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

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

עורך הסקריפטים

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

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

ניתוח של macro.gs

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

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

5d653a69a0897adf.png

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

/** @OnlyCurrentDoc */

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

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

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.

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

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

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

spreadsheet.getActiveSheet().setFrozenRows(1);

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

הקטע הבא מתמקד במניפולציה על הקוד של הפונקציה 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);
}
  1. כדי לשמור את הסקריפט, לוחצים על 'שמירה' שמירה.
  2. כדי לשנות את שם הפרויקט, מזינים "רכיבי מאקרו ופונקציות מותאמות אישית; בתור שם הפרויקט החדש ולוחצים על שינוי שם.
  3. כדי ליצור גיליון, ב-Sheets לוחצים על סמל הוספת הגיליון 9c9b0c19bf317e7f.png.

927c012b4e11475b.png

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

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

8a58ba02535b2b9c.png

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

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

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

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

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

  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);
}
  1. כדי לשמור את הסקריפט, לוחצים על 'שמירה' שמירה.
  2. ברשימת הפונקציות, בוחרים באפשרות Header ולוחצים על הפעלה.

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

bbd26f7c8e35039.png

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

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

  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);
}
  1. כדי לשמור את הסקריפט, לוחצים על 'שמירה' שמירה.
  2. ברשימת הפונקציות, בוחרים באפשרות Header ולוחצים על הפעלה.

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

2eaf2fb4879e1b36.png

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

5. קוד הסקריפט הראשון שלך: פונקציות מותאמות אישית

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

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

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

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

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

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

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

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

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

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

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

fc1421cb1c456e52.gif

  1. סמנו את העמודה "Worldwide_Gross (Swiss francs)" בתא I1.

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

  1. ב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;
}

זהו הקוד שימיר את דולר ארה"ב לפרנק שוויצרי. נסו לפעול לפי ההוראות שבהמשך כדי להפעיל פונקציה מותאמת אישית ב-Sheets.

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

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

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

3cf46560d6cea0de.gif

בעמודה ' כעת' מוצגות המרות של פרנק פרנק שוויצרי של ערכי דולר ארה"ב בעמודה H.

7fc06b3d7e3e2a9.png

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

d8680ab6efae97ac.gif

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

בשלב הבא, מתמקדים בקוד בפונקציה USDTOCHF():

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

כפי שצוין קודם, USDTOCHF() מקבל את המטבעות של המספרים המספריים, מכפיל אותם בשער חליפין קבוע, ומחזיר ערך שממיר לפרנץ' שוויצרי במשתנה המספרי 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;
}
  1. כדי לשמור את הסקריפט, לוחצים על 'שמירה' שמירה.

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

20e4bfb7f0a994ea.png

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

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

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

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

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

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

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

6. סיכום

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

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

כן לא

מה כללנו?

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

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

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

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