פונקציות מותאמות אישית ב-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. אם מוצאים תוסף של פונקציה מותאמת אישית שמעניין אתכם, לוחצים על Install כדי להתקין אותו.
6. יכול להיות שתופיע תיבת דו-שיח עם הודעה שהתוסף דורש הרשאה. אם כן, צריך לקרוא את ההודעה בעיון וללחוץ על Allow.
7. התוסף יהיה זמין בגיליון האלקטרוני. כדי להשתמש בתוסף בגיליון אלקטרוני אחר, פותחים את הגיליון האלקטרוני השני ולוחצים למעלה על תוספים > ניהול תוספים. מאתרים את התוסף שבו רוצים להשתמש ולוחצים על options > שימוש במסמך הזה.

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

אחרי שכותבים פונקציה מותאמת אישית או התקנתה מ-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 שניות. אחרת, בתא הזה תוצג השגיאה: Internal error executing the custom function.

סוגי נתונים

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

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

שירותים נתמכים	הערות
מטמון	פועלת, אבל לא שימושית במיוחד בפונקציות מותאמות אישית
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 כדי לפתוח את עורך הסקריפטים, ואז מעתיקים את טקסט הסקריפט מהגיליון האלקטרוני המקורי ומדביקים אותו בעורך הסקריפטים של גיליון אלקטרוני אחר.
• כדי ליצור עותק של הגיליון האלקטרוני שמכיל את הפונקציה המותאמת אישית, לוחצים על File > Create a copy. כשמעתיקים גיליון אלקטרוני, מועתקים גם סקריפטים שמצורפים אליו. כל מי שיש לו גישה לגיליון האלקטרוני יכול להעתיק אותו. (שותפי עריכה שיש להם רק גישת צפייה לא יכולים לפתוח את עורך הסקריפטים בגיליון האלקטרוני המקורי. עם זאת, כשהוא יוצר עותק, הוא הופך לבעלים של העותק ולראות את הסקריפט).
• מפרסמים את הסקריפט כתוסף עריכה של 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;
}

הגישה שלמעלה משתמשת בשיטת המפה של אובייקט 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;
}

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