הרחבת Google Sheets

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

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

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

שנתחיל?

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

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

קריאת נתונים

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

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

function logProductInfo() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

צפייה ביומנים

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

כתיבת נתונים

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

function addProduct() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

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

תפריטים וממשקי משתמש בהתאמה אישית

אתם יכולים להתאים אישית את Sheets על ידי הוספת תפריטים מותאמים אישית, תיבות דו-שיח וסרגלי צד. במדריך לתפריטים מוסבר איך ליצור תפריטים. במדריך לשירות HTML מוסבר איך להתאים אישית את התוכן של תיבת דו-שיח.

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

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

חיבור ל-Google Forms

אפשר לקשר את Google Forms ל-Sheets דרך השירותים Forms ו-Spreadsheet. התכונה הזו יוצרת באופן אוטומטי טופס ב-Google Forms על סמך נתונים בגיליון אלקטרוני. ב-Apps Script אפשר גם להשתמש בטריגרים, כמו onFormSubmit, כדי לבצע פעולה ספציפית אחרי שמשתמש מגיב לטופס. כדי לקבל מידע נוסף על קישור של Sheets ל-Forms, אפשר לנסות את המדריך למתחילים לניהול תשובות ב-Forms שאורכו 5 דקות.

עיצוב נתונים

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

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

אימות נתונים

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

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  const cell = SpreadsheetApp.getActive().getRange('B4');
  const rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

לפרטים נוספים על עבודה עם כללי אימות נתונים, אפשר לעיין במאמרים בנושא SpreadsheetApp.newDataValidation, DataValidationBuilder ו-Range.setDataValidation.

תרשימים

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

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];

  const chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

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

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

פונקציה בהתאמה אישית דומה לפונקציה מובנית בגיליון אלקטרוני כמו =SUM(A1:A5), אבל אתם מגדירים את ההתנהגות של הפונקציה באמצעות Apps Script. לדוגמה, אפשר ליצור פונקציה בהתאמה אישית, in2mm(), שממירה ערך מאינצ'ים למילימטרים, ואז להשתמש בנוסחה בגיליון האלקטרוני על ידי הקלדת =in2mm(A1) או =in2mm(10) בתא.

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

פקודות מאקרו

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

תוספים ל-Google Sheets

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

ביצועים ומדרגיות

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

הקפידו על שיטות מומלצות: כדאי לקרוא את המדריך לשיטות מומלצות כדי לקבל טיפים לשימוש בפעולות אצווה ולצמצום הקריאות לשירות.
אופטימיזציה של נוסחאות: אם הגיליון האלקטרוני פועל לאט בגלל נוסחאות מורכבות (כמו VLOOKUP,‏ ARRAYFORMULA או IMPORTRANGE), כדאי להשתמש ב-Apps Script כדי לבצע את החישובים האלה בזיכרון ולכתוב את התוצאות בחזרה במנות.
כדאי לשקול חלופות למסד הנתונים: במקרים של מערכי נתונים גדולים מאוד (קרוב ל-10 מיליון תאים) או הזנת נתונים בתדירות גבוהה (למשל, הרבה טפסים מקושרים), כדאי להשתמש ב-Google Cloud SQL באמצעות JDBC או ב-BigQuery.

טריגרים

סקריפטים שמקשרים לקובץ Sheets יכולים להשתמש בטריגרים פשוטים כמו הפונקציות onOpen() ו-onEdit() כדי להגיב אוטומטית כשמשתמש עם גישת עריכה לגיליון האלקטרוני פותח או עורך את הגיליון האלקטרוני. בדומה לטריגרים פשוטים, טריגרים שניתנים להתקנה מאפשרים ל-Sheets להריץ פונקציה באופן אוטומטי כשמתרחש אירוע מסוים. עם זאת, טריגרים שניתנים להתקנה גמישים יותר מטריגרים פשוטים, והם תומכים באירועים הבאים: פתיחה, עריכה, שינוי, שליחת טופס וטריגרים מבוססי-זמן (שעון).

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