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

ב-Google Sheets יש מאות פונקציות מובנות כמו AVERAGE,‏ SUM ו-VLOOKUP. אם הפונקציות האלה לא מספיקות לצרכים שלכם, אתם יכולים להשתמש ב-Apps Script כדי לכתוב פונקציות בהתאמה אישית, ואז להשתמש בהן ב-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 ואין לכם זמן ללמוד, כדאי לבדוק בחנות התוספים של Google Workspace אם מישהו אחר כבר יצר את הפונקציה המותאמת אישית שאתם צריכים.

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

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

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

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

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

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

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

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

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

לוחצים על התא שבו רוצים להשתמש בפונקציה.
מקלידים סימן שווה (=), ואחריו את שם הפונקציה וערך קלט כלשהו – לדוגמה, =DOUBLE(A1) – ומקישים על Enter.
בתא מוצג לזמן קצר הערך 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... ללא הגבלת זמן.
כדי להפעיל חישוב מחדש, צריך להעביר הפניה לתא או לטווח תאים ישירות כארגומנט לפונקציה המותאמת אישית. אחרת, הפונקציה המותאמת אישית לא תחושב מחדש עד שתערכו את הפונקציה או תשנו את הערך של תא שהפונקציה מפנה אליו. אם משתמשים בשיטה getValue בפונקציות מותאמות אישית, חשוב לדעת שהטווח שאליו מתייחסים לא מועבר ישירות כארגומנט לפונקציה המותאמת אישית.

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

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

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

סוגי נתונים

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

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

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

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

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

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

שירותי Apps Script

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

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

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

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

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

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

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

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

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

אופטימיזציה

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

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

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