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

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

אחזור פונקציה בהתאמה אישית מה- Google Workspace Marketplace

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

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

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

אחרי שכותבים פונקציה בהתאמה אישית או מתקינים פונקציה מ-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‏ @customfunction, כמו בדוגמה DOUBLE() שבהמשך.

/**
 * 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 כדי לתרגם ביטוי באנגלית לספרדית.

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

שירותים נתמכים	הערות
מטמון	פועל, אבל לא שימושי במיוחד בפונקציות בהתאמה אישית
HTML	אפשר ליצור קובצי HTML, אבל אי אפשר להציג אותם (שימושי רק במקרים נדירים)
JDBC
שפה
נעילה	פועל, אבל לא שימושי במיוחד בפונקציות בהתאמה אישית
מפות	אפשר לחשב מסלולים, אבל לא להציג מפות
נכסים	getUserProperties() מקבל רק את המאפיינים של הבעלים של הגיליון האלקטרוני. עורכים של גיליונות אלקטרוניים לא יכולים להגדיר מאפייני משתמשים בפונקציה בהתאמה אישית.
גיליון אלקטרוני	קריאה בלבד (אפשר להשתמש ברוב השיטות של get*(), אבל לא ב-set*()). אי אפשר לפתוח גיליונות אלקטרוניים אחרים (SpreadsheetApp.openById() או SpreadsheetApp.openByUrl()).
אחזור כתובת URL
כלי תחזוקה
XML

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

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

שיתוף

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

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

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

אופטימיזציה

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

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