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

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

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

איך מתחילים

הפונקציות המותאמות אישית נוצרות באמצעות JavaScript רגיל. אם מעולם לא השתמשתם ב-JavaScript, הכלי Codecademy מציע קורס מצוין למתחילים. (הערה: הקורס הזה לא פותח על ידי Google והוא לא משויך ל-Google).

זוהי פונקציה מותאמת אישית פשוטה, שנקראת DOUBLE, שמכפילה ערך קלט ב-2:

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. מקלידים "custom function" ומקישים על 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 הופכים לאובייקטים תאריך ב-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

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

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

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

אופטימיזציה

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

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