פונקציות מותאמות אישית ב-Google Sheets

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

תחילת העבודה

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

הנה פונקציה מותאמת אישית פשוטה, בשם DOUBLE, שמכפילה ערך קלט ב-2:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

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

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

כדי לכתוב פונקציה בהתאמה אישית:

1. יוצרים או פותחים גיליון אלקטרוני ב-Google Sheets.
2. בוחרים באפשרות תוספים > Apps Script.
3. מוחקים את כל הקוד בכלי לעריכת סקריפטים. כדי להשתמש בפונקציה DOUBLE שמופיעה למעלה, פשוט מעתיקים את הקוד ומדביקים אותו בכלי לעריכת סקריפטים.
4. למעלה, לוחצים על סמל השמירה save.

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

קבלת פונקציה בהתאמה אישית מ Google Workspace Marketplace

Google Workspace Marketplace מציע כמה פונקציות מותאמות אישית כתוספים ל-Google Sheets. כדי להשתמש בתוספים האלה או לבדוק אותם:

1. יוצרים או פותחים גיליון אלקטרוני ב-Google Sheets.
2. למעלה, לוחצים על תוספים > הורדת תוספים.
3. אחרי שחלון Google Workspace Marketplace נפתח, לוחצים על תיבת החיפוש בפינה השמאלית העליונה.
4. מקלידים 'פונקציה בהתאמה אישית' ומקישים על Enter.
5. אם מוצאים תוסף של פונקציה בהתאמה אישית שמעניין אתכם, לוחצים על התקנה כדי להתקין אותו.
6. יכול להיות שתוצג תיבת דו-שיח שבה תתבקשו לאשר את השימוש בתוסף. אם כן, קוראים את ההודעה בעיון ולוחצים על אישור.
7. התוסף יהיה זמין בגיליון האלקטרוני. כדי להשתמש בתוסף בגיליון אלקטרוני אחר, פותחים את הגיליון האלקטרוני השני ובחלק העליון לוחצים על תוספים > ניהול תוספים. מאתרים את התוסף שרוצים להשתמש בו ולוחצים על סמל האפשרויות more_vert > שימוש במסמך הזה.

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

אחרי שכותבים פונקציה בהתאמה אישית או מתקינים פונקציה מ-Google Workspace Marketplace, קל להשתמש בה כמו בפונקציה מובנית:

1. לוחצים על התא שבו רוצים להשתמש בפונקציה.
2. מקלידים סימן שווה (=) ואחריו את שם הפונקציה וערך קלט כלשהו – לדוגמה, =DOUBLE(A1) – ומקישים על Enter.
3. בתא יוצג הערך Loading... לרגע, ואז התוצאה.

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

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

מתן שמות

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

• השם של פונקציה בהתאמה אישית צריך להיות שונה מהשמות של פונקציות מובנות כמו SUM().
• השם של פונקציה מותאמת אישית לא יכול להסתיים בקו תחתון (_), שמציין פונקציה פרטית ב-Apps Script.
• השם של פונקציה מותאמת אישית צריך להיות מוצהר באמצעות התחביר function myFunction(), ולא var myFunction = new Function().
• אין חשיבות לאותיות רישיות, למרות שבדרך כלל שמות של פונקציות בגיליון אלקטרוני הם באותיות רישיות.

ארגומנטים

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

• אם קוראים לפונקציה עם הפניה לתא בודד כארגומנט (למשל =DOUBLE(A1)), הארגומנט יהיה הערך של התא.

• אם קוראים לפונקציה עם הפניה לטווח של תאים כארגומנט (למשל =DOUBLE(A1:B10)), הארגומנט יהיה מערך דו-ממדי של ערכי התאים. לדוגמה, בצילום המסך שלמטה, הארגומנטים ב-=DOUBLE(A1:B2) מפורשים על ידי Apps Script כ-double([[1,3],[2,4]]). שימו לב שצריך לשנות את דוגמת הקוד של DOUBLE שלמעלה כדי לקבל מערך כקלט.

  
  

• הארגומנטים של פונקציה מותאמת אישית חייבים להיות דטרמיניסטיים. כלומר, אסור להשתמש בפונקציות מובנות של גיליון אלקטרוני שמחזירות תוצאה שונה בכל פעם שהן מבצעות חישוב – כמו NOW() או RAND() – כארגומנטים בפונקציה מותאמת אישית. אם פונקציה מותאמת אישית מנסה להחזיר ערך שמבוסס על אחת מהפונקציות המובנות הדינמיות האלה, היא תציג את הערך Loading... ללא הגבלת זמן.

ערכים מוחזרים

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

• אם פונקציה מותאמת אישית מחזירה ערך, הערך מוצג בתא שממנו בוצעה הקריאה לפונקציה.
• אם פונקציה מותאמת אישית מחזירה מערך דו-ממדי של ערכים, הערכים גולשים לתאים סמוכים כל עוד התאים האלה ריקים. אם הפעולה הזו תגרום למערך להחליף את התוכן הקיים בתאים, הפונקציה המותאמת אישית תציג שגיאה במקום זאת. דוגמה מופיעה בקטע בנושא אופטימיזציה של פונקציות בהתאמה אישית.
• פונקציה בהתאמה אישית לא יכולה להשפיע על תאים אחרים מלבד התאים שהיא מחזירה להם ערך. במילים אחרות, פונקציה מותאמת אישית לא יכולה לערוך תאים שרירותיים, אלא רק את התאים שמהם היא נקראת ואת התאים הסמוכים להם. כדי לערוך תאים שרירותיים, אפשר להשתמש בתפריט מותאם אישית כדי להפעיל פונקציה במקום זאת.
• קריאה לפונקציה מותאמת אישית חייבת להסתיים תוך 30 שניות. אם לא, בתא יופיע הערך #ERROR! וההערה בתא תהיה Exceeded maximum execution time (line 0).

סוגי נתונים

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

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

השלמה אוטומטית

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

פונקציות מותאמות אישית יופיעו ברשימה הזו אם הסקריפט שלהן כולל תג JsDoc, כמו בדוגמה DOUBLE() שבהמשך.@customfunction

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

הגדרות מתקדמות

שימוש בשירותים של Google Apps Script

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

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

אם הפונקציה המותאמת אישית מחזירה את הודעת השגיאה You do not have permission to call X service., השירות דורש הרשאת משתמש ולכן אי אפשר להשתמש בו בפונקציה מותאמת אישית.

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

שיתוף

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

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

אופטימיזציה

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

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

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

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

בגישה שלמעלה נעשה שימוש בשיטת map של אובייקט Array ב-JavaScript כדי להחיל שיטה על המערך הדו-ממדי של התאים ולקבל כל שורה. לאחר מכן, נעשה שימוש חוזר ב-map כדי להחזיר את הערך של כל תא כפול. הפונקציה מחזירה מערך דו-ממדי שמכיל את התוצאות. כך תוכלו להשתמש בפונקציה DOUBLE רק פעם אחת, אבל היא תחשב את הערכים של מספר גדול של תאים בבת אחת, כמו שרואים בצילום המסך שלמטה. (אפשר להשיג את אותה התוצאה באמצעות הצהרות if nested במקום הקריאה map).

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

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

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