יסודות של Apps Script עם Google Sheets #2: גיליונות אלקטרוניים, גיליונות וטווחים

1. מבוא

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

מה תלמדו

  • איך גיליונות אלקטרוניים, גיליונות וטווחים מיוצגים ב-Apps Script.
  • איך ניגשים לגיליון האלקטרוני הפעיל (פתוח), יוצרים אותו ומשנים את השם שלו באמצעות המחלקה SpreadsheetApp והמחלקה Spreadsheet.
  • איך משנים את השם של גיליון ואת הכיוון של עמודה או שורה בטווח, באמצעות המחלקה Sheet.
  • איך מציינים, מפעילים, מעבירים וממיינים קבוצה של תאים או טווח נתונים באמצעות המחלקה Range.

לפני שמתחילים

זוהי סדנת הקוד השנייה בפלייליסט Fundamentals of Apps Script with Google Sheets (יסודות של Apps Script עם Google Sheets). לפני שמתחילים, חשוב להשלים את ה-codelab הראשון: Macros and Custom Functions (פקודות מאקרו ופונקציות בהתאמה אישית).

מה נדרש

  • הבנה של הנושאים הבסיסיים ב-Apps Script שמוסברים בסדנת ה-codelab הקודמת בפלייליסט הזה.
  • היכרות בסיסית עם עורך הקוד של Apps Script
  • היכרות בסיסית עם Google Sheets
  • יכולת לקרוא Sheets A1 Notation
  • היכרות בסיסית עם JavaScript והמחלקה String שלה

בקטע הבא מוצגים סוגי הליבה של שירות Spreadsheet.

2. מבוא לשירות הגיליון האלקטרוני

ארבע מחלקות מהוות את הבסיס לשירות Spreadsheet: ‏ SpreadsheetApp,‏ Spreadsheet,‏ Sheet ו- Range. בקטע הזה מתוארים הסוגים האלה ומפורט השימוש בהם.

המחלקות SpreadsheetApp

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

גיליונות אלקטרוניים, גיליונות והכיתות שלהם

במונח גיליון אלקטרוני ב-Sheets מתייחסים לקובץ Google Sheets (שמאוחסן ב-Google Drive) שמכיל נתונים מאורגנים בשורות ובעמודות. גיליון אלקטרוני נקרא לפעמים 'גיליון Google', כמו שמסמך נקרא 'מסמך Google'.

אפשר להשתמש במחלקה Spreadsheet כדי לגשת לנתונים בקובץ Google Sheets ולשנות אותם. אפשר להשתמש במחלקה הזו גם לפעולות אחרות ברמת הקובץ, כמו הוספת משתפי פעולה.

f00cc1a9eb606f77.png

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

39dbb10f83e3082.png

לסיכום, המחלקה Spreadsheet פועלת על אוסף הגיליונות ומגדירה קובץ Google Sheets ב-Google Drive. המחלקות Sheet פועלות על גיליונות ספציפיים בגיליון אלקטרוני.

המחלקות Range

ברוב הפעולות של מניפולציה של נתונים (לדוגמה, קריאה, כתיבה או עיצוב של נתונים בתאים) צריך להגדיר לאילו תאים הפעולה מתייחסת. אפשר להשתמש במחלקה Range כדי לבחור קבוצות ספציפיות של תאים בגיליון. מופעים של המחלקה הזו מייצגים טווח – קבוצה של תא אחד או יותר סמוכים בגיליון. אפשר לציין טווחים לפי מספר השורה והעמודה שלהם, או באמצעות סימון A1.

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

3. הגדרה

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

5376f721894b10d9.png

עותק של הגיליון האלקטרוני לדוגמה לשימושכם ממוקם בתיקיית Google Drive שלכם, והשם שלו הוא 'עותק של גיליון אלקטרוני ללא שם'. משתמשים בגיליון האלקטרוני הזה כדי להשלים את התרגילים של ה-codelab הזה.

תזכורת: כדי לפתוח את עורך הסקריפטים מ-Google Sheets, לוחצים על תוספים > Apps Script.

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

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

4. גישה לגיליונות אלקטרוניים ושינוי שלהם

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

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

שינוי השם של הגיליון האלקטרוני הפעיל

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

  1. בכלי לעריכת סקריפטים, מחליפים את בלוק הקוד myFunction() שמוגדר כברירת מחדל בקוד הבא:
function renameSpreadsheet() {   var mySS = SpreadsheetApp.getActiveSpreadsheet();   mySS.rename("2017 Avocado Prices in Portland, Seattle"); }
  1. כדי לשמור את התסריט, לוחצים על סמל השמירה הצלה.
  2. כדי לשנות את השם של פרויקט Apps Script, לוחצים על Untitled project, מזינים את השם החדש של הפרויקט, Avocado prices, ולוחצים על Rename.
  3. כדי להריץ את הסקריפט, בוחרים באפשרות renameSpreadsheet מתוך רשימת הפונקציות ולוחצים על הרצה.
  4. מאשרים את המאקרו לפי ההוראות במסך. אם מוצגת ההודעה 'האפליקציה הזו לא אומתה', לוחצים על מתקדם ואז על מעבר למחירי האבוקדו (לא בטוח). במסך הבא לוחצים על אישור.

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

226c7bc3c2fbf33e.png

בואו נסתכל על הקוד שהזנת. השיטה getActiveSpreadsheet() מחזירה אובייקט שמייצג את הגיליון האלקטרוני הפעיל, כלומר את העותק של הגיליון האלקטרוני של התרגיל שיצרתם. אובייקט הגיליון האלקטרוני הזה מאוחסן במשתנה mySS. הפעלת rename(newName) ב-mySS משנה את השם של קובץ הגיליון האלקטרוני ב-Google Drive ל-"2017 Avocado Prices in Portland, Seattle".

מכיוון שהמשתנה mySS הוא הפניה לגיליון האלקטרוני, אפשר להפוך את הקוד לנקי ויעיל יותר על ידי קריאה לשיטות Spreadsheet ב-mySS במקום לקרוא ל-getActiveSpreadsheet() שוב ושוב.

שכפול הגיליון הפעיל

בגיליון האלקטרוני הנוכחי יש רק גיליון אחד. אתם יכולים להתקשר לשיטה Spreadsheet.duplicateActiveSheet() כדי ליצור עותק של הגיליון:

  1. מוסיפים את הפונקציה החדשה הבאה מתחת לפונקציה renameSpreadsheet() שכבר נמצאת בפרויקט הסקריפט:
function duplicateAndOrganizeActiveSheet() {   var mySS = SpreadsheetApp.getActiveSpreadsheet();   var duplicateSheet = mySS.duplicateActiveSheet(); }
  1. שומרים את פרויקט הסקריפט.
  2. כדי להריץ את הסקריפט, בוחרים באפשרות duplicateAndOrganizeActiveSheet מתוך רשימת הפונקציות ולוחצים על הרצה.

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

d24f9f4ae20bf7d4.gif

בפונקציה החדשה הזו, ה-method ‏ duplicateActiveSheet() יוצר, מפעיל ומחזיר את הגיליון המשוכפל בגיליון האלקטרוני. גיליון התוצאות מאוחסן ב-duplicateSheet, אבל הקוד עדיין לא עושה שום דבר עם המשתנה הזה.

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

5. עיצוב הגיליון באמצעות המחלקה Sheet

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

שינוי השם של הגיליון

שינוי השם של גיליונות הוא פשוט כמו שינוי השם של הגיליון האלקטרוני ב-renameSpreadsheet(). נדרשת רק קריאה אחת לשיטה.

  1. ב-Google Sheets, לוחצים על הגיליון Sheet_Original כדי להפעיל אותו.
  2. ב-Apps Script, משנים את הפונקציה duplicateAndOrganizeActiveSheet() כך שתהיה זהה לפונקציה הבאה:
function duplicateAndOrganizeActiveSheet() {   var mySS = SpreadsheetApp.getActiveSpreadsheet();   var duplicateSheet = mySS.duplicateActiveSheet();    // Change the name of the new sheet.   duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId()); }
  1. שומרים ומריצים את הפונקציה.

ב-Google Sheets, נוצר גיליון כפול ומשנים את השם שלו כשמריצים את הפונקציה:

91295f42354f62e7.gif

בקוד שנוסף, השיטה setName(name) משנה את השם של duplicateSheet, באמצעות getSheetID() כדי לקבל את מספר המזהה הייחודי של הגיליון. האופרטור + משרשר את מזהה הגיליון לסוף המחרוזת "Sheet_".

שינוי העמודות והשורות בגיליון

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

  1. ב-Google Sheets, לוחצים על הגיליון Sheet_Original כדי להפעיל אותו.
  2. ב-Apps Script, משנים את הפונקציה duplicateAndOrganizeActiveSheet() כך שתהיה זהה לפונקציה הבאה:
function duplicateAndOrganizeActiveSheet() {   var mySS = SpreadsheetApp.getActiveSpreadsheet();   var duplicateSheet = mySS.duplicateActiveSheet();    // Change the name of the new sheet.   duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());    // Format the new sheet.   duplicateSheet.autoResizeColumns(1, 5);   duplicateSheet.setFrozenRows(2); }
  1. שומרים ומריצים את הפונקציה.

ב-Google Sheets, נוצר גיליון משוכפל, הוא מקבל שם חדש, מופעל ומעוצב:

2e57c917ab157dad.gif

הקוד שהוספתם משתמש ב- autoResizeColumns(startColumn, numColumns) כדי לשנות את הגודל של העמודות בגיליון, כך שיהיה קל יותר לקרוא אותו. השיטה setFrozenRows(rows) מקפיאה את מספר השורות שצוין (שתיים במקרה הזה), כך ששורות הכותרת נשארות גלויות בזמן שהקורא גולל למטה בגיליון האלקטרוני.

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

6. שינוי הסדר של הנתונים באמצעות המחלקה Range

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

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

העברת טווחים

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

בואו נעדכן את duplicateAndOrganizeActiveSheet() השיטה כדי להעביר גם חלק מהנתונים:

  1. ב-Google Sheets, לוחצים על הגיליון Sheet_Original כדי להפעיל אותו.
  2. ב-Apps Script, משנים את הפונקציה duplicateAndOrganizeActiveSheet() כך שתהיה זהה לפונקציה הבאה:
function duplicateAndOrganizeActiveSheet() {   var mySS = SpreadsheetApp.getActiveSpreadsheet();   var duplicateSheet = mySS.duplicateActiveSheet();    // Change the name of the new sheet.   duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());    // Format the new sheet.   duplicateSheet.autoResizeColumns(1, 5);   duplicateSheet.setFrozenRows(2);    // Move column F to column C.   var myRange = duplicateSheet.getRange("F2:F");   myRange.moveTo(duplicateSheet.getRange("C2")); }
  1. שומרים ומריצים את הפונקציה.

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

10ea483aec52457e.gif

הקוד החדש משתמש בשיטה getRange(a1Notation) כדי לזהות את טווח הנתונים להעברה. אם מזינים את הסימון A1 ‏'F2:F' כפרמטר של השיטה, מציינים את עמודה F (לא כולל F1). אם הטווח שצוין קיים, השיטה getRange(a1Notation) מחזירה את המופע Range שלו. הקוד שומר את המופע במשתנה myRange כדי להקל על השימוש.

אחרי שמזהים את הטווח, השיטה moveTo(target) לוקחת את התוכן של myRange (הערכים והעיצוב) ומעבירה אותו. היעד (עמודה C) מצוין באמצעות סימון A1 ‏'C2'. זהו תא בודד ולא עמודה. כשמעבירים נתונים, לא צריך להתאים את הגדלים לטווחים של היעד והמקור. ב-Apps Script, התאמה מתבצעת רק לתא הראשון בכל אחד מהגיליונות.

מיון טווחים

המחלקות Range מאפשרות לקרוא, לעדכן ולארגן קבוצות של תאים. לדוגמה, אפשר למיין טווח נתונים באמצעות השיטה Range.sort(sortSpecObj):

  1. ב-Google Sheets, לוחצים על הגיליון Sheet_Original כדי להפעיל אותו.
  2. ב-Apps Script, משנים את הפונקציה duplicateAndOrganizeActiveSheet() כך שתהיה זהה לפונקציה הבאה:
function duplicateAndOrganizeActiveSheet() {   var mySS = SpreadsheetApp.getActiveSpreadsheet();   var duplicateSheet = mySS.duplicateActiveSheet();    // Change the name of the new sheet.   duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());    // Format the new sheet.   duplicateSheet.autoResizeColumns(1, 5);   duplicateSheet.setFrozenRows(2);    // Move column F to column C.   var myRange = duplicateSheet.getRange("F2:F");   myRange.moveTo(duplicateSheet.getRange("C2"));    // Sort all the data using column C (Price information).   myRange = duplicateSheet.getRange("A3:D55");   myRange.sort(3); }
  1. שומרים ומריצים את הפונקציה.

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

a6cc9710245fae8d.png

הקוד החדש משתמש ב- getRange(a1Notation) כדי לציין טווח חדש שכולל את התאים A3:D55 (כל הטבלה, לא כולל כותרות העמודות). הקוד קורא לשיטה sort(sortSpecObj) כדי למיין את הטבלה. במקרה הזה, הפרמטר sortSpecObj הוא מספר העמודה שלפיה יתבצע המיון. השיטה ממיינת את הטווח כך שערכי העמודה שצוינה יהיו מהנמוך לגבוה (ערכים עולים). השיטה sort(sortSpecObj) יכולה לבצע דרישות מיון מורכבות יותר, אבל לא צריך אותן כאן. ב מסמכי העזרה בנושא שיטות מפורטות כל הדרכים השונות למיון טווחים.

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

7. סיכום

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

אפשר לעבור אל ה-codelab הבא.

האם ה-codelab הזה היה מועיל?

כן לא

מה נכלל

  • איך גיליונות אלקטרוניים, גיליונות וטווחים מיוצגים ב-Apps Script.
  • כמה שימושים בסיסיים במחלקות SpreadsheetApp,‏ Spreadsheet,‏ Sheet ו-Range.

המאמרים הבאים

ב-Codelab הבא בפלייליסט הזה מוסבר בפירוט איך לקרוא, לכתוב ולשנות נתונים בגיליון אלקטרוני.

אפשר למצוא את ה-codelab הבא במאמר עבודה עם נתונים.