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

בניגוד לרוב הסוגים האחרים של סקריפטים של 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;
}

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

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