פונקציות מותאמות אישית ב-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. אם מוצאים תוסף של פונקציה מותאמת אישית שמעניין אתכם, לוחצים על התקנה. כדי להתקין אותו.
  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 שניות. אם הוא לא מוגדר, התא #ERROR! וההערה בתא היא Exceeded maximum execution time (line 0).

סוגי נתונים

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

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

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

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