פונקציות מותאמות אישית ב-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. מקלידים "function custom" ומקישים על 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 שניות. אחרת, השגיאה תציג בתא: 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;
}

מתקדמת

שימוש בשירותי Apps Script

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

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

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

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