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

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

תחילת העבודה

פונקציות בהתאמה אישית נוצרות באמצעות JavaScript סטנדרטי. אם אתם משתמשים חדשים ב-JavaScript, Codecademy מציע קורס מעולה למתחילים. (הערה: הקורס הזה לא פותח על ידי Google ולא משויך ל-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. התוסף יהיה זמין בגיליון האלקטרוני. כדי להשתמש בתוסף בגיליון אלקטרוני אחר, פותחים את הגיליון האלקטרוני השני ולוחצים על תוספים > ניהול תוספים בחלק העליון. מוצאים את התוסף שבו רוצים להשתמש ולוחצים על סמל האפשרויות > Use in this document.

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

אחרי שכותבים פונקציה מותאמת אישית או מתקינים פונקציה מ-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

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

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

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