היסודות של סקריפט של Apps עם Google Sheets #2: גיליונות אלקטרוניים, גיליונות וטווחים
מידע על Codelab זה
1. מבוא
אנחנו שמחים שהצטרפת לחלק השני של Apps Scripts עם פלייליסט Google Codecode. מעבדת הקוד הקודמת התמקדה בקונספטים של עורך הסקריפטים, פקודות מאקרו ופונקציות מותאמות אישית. Codelab הזה מתעמק בשירות הגיליון האלקטרוני שאפשר להשתמש בו כדי לקרוא, לכתוב ולשנות נתונים ב-Google Sheets.
מה תלמדו
- כיצד גיליונות אלקטרוניים, גיליונות וטווחים מיוצגים ב-Apps Script.
- איך ניגשים לגיליון האלקטרוני הפעיל (הפתוח) בעזרת השם
SpreadsheetAppוהכיתהSpreadsheet, ומשנים את השם שלו. - איך משנים שם של גיליון וכיוון של עמודה/שורה בטווח של
Sheet? - איך לציין, להפעיל, להעביר ולמיין קבוצת תאים או טווח נתונים באמצעות המחלקה
Range.
לפני שמתחילים
זוהי שיעור הקוד השני ברכיב הבסיסי ב-Apps Script עם פלייליסט ב-Google Sheets. לפני שמתחילים, חשוב להשלים את ה-codelab הראשון: רכיבי מאקרו ופונקציות מותאמות אישית.
מה תצטרך להכין
- הבנה בנושאים הבסיסיים של Apps Script שנבדקו בשיעור הקוד הקודם של הפלייליסט הזה.
- היכרות בסיסית עם עורך Apps Script
- היכרות בסיסית עם Google Sheets
- יכולת לקרוא את Sheets A1 ב-Sheets
- היכרות בסיסית עם JavaScript וה
Stringכיתה שלה
בקטע הבא מפורטות מחלקות הליבה של שירות הגיליונות האלקטרוניים.
2. מבוא לשירות הגיליונות האלקטרוניים
יש ארבע כיתות שכוללת את הבסיס של שירות הגיליון האלקטרוני: SpreadsheetApp , Spreadsheet , Sheet ו-Range. בקטע הזה מתוארים הכיתות האלה ובאילו מטרות הן משמשות.
הכיתה ב-SheetsApp
לפני שאתם מתעמקים בגיליונות אלקטרוניים, בגיליונות ובטווחים, עליכם לבדוק את מחלקת האב שלהם: SpreadsheetApp. סקריפטים רבים מתחילים בקריאה של SpreadsheetApp שיטות, מאחר שהם יכולים לספק את נקודת הגישה הראשונית לקבצים שלכם ב-Google Sheets. אפשר לחשוב על SpreadsheetApp כמחלקה הראשית של שירות הגיליונות האלקטרוניים. הקורס SpreadsheetApp לא נבדק כאן לעומק. עם זאת, בהמשך מעבדה זו תוכלו למצוא דוגמאות ותרגילים שיעזרו לכם להבין את הכיתה הזו.
גיליונות אלקטרוניים, גיליונות אלקטרוניים והכיתות שלהם
מונח ב-Sheets הוא גיליון אלקטרוני והוא קובץ של Google Sheets (המאוחסן ב-Google Drive) שמכיל נתונים המאורגנים לפי שורות ועמודות. גיליון אלקטרוני נקרא לפעמים 'Google Sheets'' באופן דומה למסמך שמכונה 'Google Docs'.
אפשר להשתמש בכיתה Spreadsheet כדי לגשת לנתוני קבצים ב-Google Sheets ולשנות אותם. אפשר להשתמש בכיתה הזו גם לפעולות אחרות ברמת הקובץ, כמו הוספת שותפי עריכה.
גיליון** מייצג את הדף הספציפי של גיליון אלקטרוני, שנקרא לפעמים "tab." כל גיליון אלקטרוני יכול להכיל גיליון אחד או יותר. אפשר להשתמש בכיתה Sheet** כדי לגשת לנתונים ולהגדרות ברמת הגיליון ולשנות אותם, למשל כדי להעביר שורות או עמודות נתונים.
לסיכום, הסיווג Spreadsheet פועל באוסף הגיליונות ומגדיר קובץ Google Sheets ב-Google Drive. הכיתה Sheet פועלת בגיליונות בודדים בתוך גיליון אלקטרוני.
הכיתה 'טווח'
רוב פעולות מניפולציה של נתונים (לדוגמה, קריאה, כתיבה או עיצוב של נתוני תאים) מחייבות להגדיר את התאים שעליהם תתבצע הפעולה. אפשר להשתמש ברמה Range כדי לבחור קבוצות ספציפיות של תאים בגיליון. מופעים של הכיתה הזו מייצגים טווח — קבוצה של תאים סמוכים או יותר בגיליון. ניתן לציין טווחים לפי מספר השורה והעמודה שלהם או על ידי שימוש בפורמט A1.
שאר קוד Lab מציג דוגמאות לסקריפטים שעובדים עם הכיתות האלה והשיטות שלהם.
3. הגדרה
לפני שנמשיך, יש צורך בגיליון אלקטרוני עם נתונים מסוימים. סיפקנו בשבילך קובץ: לוחצים על הקישור הזה כדי להעתיק את גיליון הנתונים ואז לוחצים על יצירת עותק.
עותק של הגיליון האלקטרוני לדוגמה לשימושך נמצא בתיקייה Google Drive בשם "עותק של גיליון אלקטרוני ללא שם." אפשר להשתמש בגיליון האלקטרוני הזה כדי להשלים את תרגילי הקוד הזה.
תזכורת: כדי לפתוח את עורך הסקריפטים מ-Google Sheets, לוחצים על תוספים > Apps Script.
כשפותחים פרויקט Apps Script בעורך הסקריפטים בפעם הראשונה, עורך הסקריפט יוצר עבורכם פרויקט סקריפט וגם קובץ סקריפט.
בקטע הבא נסביר איך להשתמש בכיתה Spreadsheet כדי לשפר את הגיליון האלקטרוני.
4. גישה אל גיליונות אלקטרוניים ושינוי שלהם
בקטע הזה נסביר איך להשתמש בכיתות SpreadsheetApp ו-Spreadsheet כדי לגשת לגיליונות אלקטרוניים ולשנות אותם. ספציפית, התרגילים מלמדים איך לשנות את השם של גיליון אלקטרוני ולשכפל גיליונות בתוך גיליון אלקטרוני.
מדובר בפעולות פשוטות, אבל לעיתים קרובות הן חלק מתהליך עבודה גדול ומורכב יותר. אחרי שתבינו איך להפוך את המשימות האלה לאוטומטיות באמצעות קוד סקריפט, תוכלו ללמוד בקלות רבה יותר איך לבצע אוטומציה של פעולות מורכבות יותר.
שינוי השם של הגיליון האלקטרוני הפעיל
נניח שרצית לשנות את השם המוגדר כברירת מחדל, "עותק של גיליון אלקטרוני ללא שם " לכותרת שמשקפת טוב יותר את המטרה של הגיליון האלקטרוני. אפשר לעשות זאת עם הכיתות SpreadsheetApp ו-Spreadsheet.
- בעורך הסקריפטים, צריך להחליף את קטע הקוד של
myFunction()המוגדר כברירת מחדל בקוד הבא:
function renameSpreadsheet() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
- כדי לשמור את הסקריפט, לוחצים על 'שמירה'
. - כדי לשנות את השם של פרויקט Apps Script, לוחצים על פרויקט ללא שם, מזינים את ה&מחירים של Avocado; בתור שם הפרויקט החדש ולוחצים על שינוי שם.
- כדי להפעיל את הסקריפט, בוחרים באפשרות
renameSpreadsheetמתוך רשימת הפונקציות ולוחצים על הפעלה. - מאשרים את רכיב המאקרו לפי ההוראות במסך. אם מופיעה ההודעה &"האפליקציה הזו לא&&39;לא מאומתת", לוחצים על מתקדם ואז על מעבר למחירים של אבוקדו (לא בטוח). במסך הבא יש ללחוץ על אישור.
לאחר הפעלת הפונקציה, שם הקובץ של הגיליון האלקטרוני אמור להשתנות:
נבחן את הקוד שהזנת. השיטה getActiveSpreadsheet() מחזירה אובייקט שמייצג את הגיליון האלקטרוני הפעיל. כלומר, העותק של הגיליון האלקטרוני של האימון שיצרתם. אובייקט הגיליון האלקטרוני הזה מאוחסן במשתנה mySS. קריאה ל-rename(newName) ב-mySS משנה את השם של קובץ הגיליון האלקטרוני ב-Google Drive ל-"2017 מחירי Avocado בפורטלנד, סיאטל."
מכיוון שהמשתנה mySS הוא גיליון אלקטרוני, אפשר להפוך את הקוד שלך לנקי ויעיל יותר. לשם כך, יש להתקשר למספר Spreadsheet ב-mySS במקום להתקשר ל-getActiveSpreadsheet() שוב ושוב.
שכפול הגיליון הפעיל
בגיליון האלקטרוני הנוכחי, יש רק גיליון אחד בגיליון. אפשר להתקשר לשיטה Spreadsheet.duplicateActiveSheet() כדי ליצור עותק של הגיליון:
- יש להוסיף את הפונקציה החדשה הבאה מתחת לפונקציה
renameSpreadsheet()שכבר נמצאת בפרויקט הסקריפט שלך:
function duplicateAndOrganizeActiveSheet() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var duplicateSheet = mySS.duplicateActiveSheet();
}
- שומרים את פרויקט הסקריפט.
- כדי להפעיל את הסקריפט, בוחרים באפשרות
duplicateAndOrganizeActiveSheetמתוך רשימת הפונקציות ולוחצים על הפעלה.
חוזרים אל Sheets כדי לצפות ב&מירכאות חדשים. עותק של גיליון Sheets נוסף לגיליון האלקטרוני.
בפונקציה החדשה הזו, השיטה duplicateActiveSheet() יוצרת, מפעילה ומחזירה את הגיליון האלקטרוני בגיליון האלקטרוני. הגיליון שנוצר מאוחסן ב-duplicateSheet, אך הקוד עדיין לא עושה דבר עם המשתנה הזה.
בקטע הבא, צריך להשתמש בכיתה Sheet כדי לשנות את השם של הגיליון הכפול ולעצב אותו.
5. עיצוב הגיליון באמצעות סיווג גיליון
הכיתה Sheet מספקת שיטות שמאפשרות לסקריפטים לקרוא ולעדכן גיליונות. בקטע הזה אפשר ללמוד איך לשנות את הרוחב והשם של גיליון ושל שיטות בגיליון Sheet.
שינוי השם של הגיליון
שינוי השם של גיליונות הוא שינוי השם של גיליון אלקטרוני כך: renameSpreadsheet(). צריך להפעיל רק קריאה אחת בשיטה.
- ב-Google Sheets, לוחצים על הגיליון
Sheet_Originalכדי להפעיל אותו. - ב-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());
}
- שומרים את הפונקציה ומפעילים אותה.
ב-Google Sheets נוצר גיליון כפול עם שם חדש בזמן הפעלת הפונקציה:
בקוד שנוסף, השיטה setName(name) משנה את השם של duplicateSheet, באמצעות getSheetID() כדי לקבל את מספר המזהה הייחודי של הגיליון. האופרטור + מקשר את מזהה הגיליון לסוף המחרוזת של "Sheet_".
שינוי עמודות ושורות בגיליון
אפשר גם להשתמש בכיתה Sheet כדי לעצב את הגיליון. לדוגמה, אנחנו יכולים לעדכן את הפונקציה duplicateAndOrganizeActiveSheet() גם כדי לשנות את הגודל של עמודות בגיליון הכפול ולהוסיף שורות שהוקפאו:
- ב-Google Sheets, לוחצים על הגיליון
Sheet_Originalכדי להפעיל אותו. - ב-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);
}
- שומרים את הפונקציה ומפעילים אותה.
ב-Google Sheets נוצר עותק של גיליון אלקטרוני, משנים את השם, מפעילים אותו ומציגים אותו בפורמט הבא:
הקוד שהוספתם משתמש ב-autoResizeColumns(startColumn, numColumns) כדי לשנות את הגודל של עמודות הגיליון כדי שיהיה קל יותר לקרוא אותו. השיטה setFrozenRows(rows) קופאת את מספר השורות הנתון (שתיים במקרה זה), כדי ששורות הכותרת יהיו גלויות בזמן שהקורא גולל למטה בגיליון האלקטרוני.
בקטע הבא תלמדו על טווחים ומניפולציה בסיסית של נתונים.
6. סידור מחדש של הנתונים באמצעות הכיתה 'טווח'
הכיתה Range והשיטות שלה מספקות את רוב אפשרויות השינוי והעיצוב בשירות הגיליונות האלקטרוניים.
סעיף זה מציג מניפולציה בסיסית של נתונים עם טווחים. תרגילים אלה מתמקדים באופן השימוש בטווחים ב-Apps Script, ואילו מעבדות קוד אחרות בפלייליסט הזה מספקות מידע מעמיק יותר על מניפולציה של נתונים ועל עיצוב הנתונים.
העברת טווחים
ניתן להפעיל ולהעביר טווחים של נתונים באמצעות שיטות הכיתה וסימון A1, קיצור של זיהוי קבוצות ספציפיות של תאים בגיליונות אלקטרוניים. אם אתם צריכים להכיר מחדש את תיאור ה-A1, תוכלו להיעזר בו.
יש לעדכן את השיטה של duplicateAndOrganizeActiveSheet() כדי להעביר גם נתונים מסוימים:
- ב-Google Sheets, לוחצים על הגיליון
Sheet_Originalכדי להפעיל אותו. - ב-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"));
}
- שומרים את הפונקציה ומפעילים אותה.
כשמריצים את הפונקציה הזו, המערכת יוצרת גיליון אלקטרוני, מפעילה גיליון אלקטרוני ומפעילה אותו. בנוסף, התוכן של עמודה F מועבר לעמודה C:
הקוד החדש משתמש בשיטה getRange(a1Notation) כדי לזהות את טווח הנתונים להעברה. כשמזינים את הסימן A1 &מירכאות;F2:F", כפרמטר השיטה', מציינים את עמודה F (לא כולל F1). אם הטווח שצוין קיים, השיטה getRange(a1Notation) מחזירה את המופע של Range. הקוד מאחסן את המופע במשתנה myRange כדי שיהיה לך קל להשתמש בו.
לאחר זיהוי הטווח, השיטה moveTo(target) מקבלת את התוכן של myRange (גם הערכים וגם הפורמט) ומזיזה אותם. היעד (עמודה ג') מצוין באמצעות סימון A1 "C2" מדובר בתא יחיד, ולא בעמודה. כשמעבירים נתונים, אין צורך להתאים את הגדלים לטווחי היעד וליעד. Apps Script פשוט מיישר את התא הראשון של כל אחד מהם.
מיון טווחים
הסיווג Range מאפשר לקרוא, לעדכן ולארגן קבוצות של תאים. לדוגמה, אפשר למיין טווח נתונים באמצעות השיטה Range.sort(sortSpecObj):
- ב-Google Sheets, לוחצים על הגיליון
Sheet_Originalכדי להפעיל אותו. - ב-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);
}
- שומרים את הפונקציה ומפעילים אותה.
עכשיו, בנוסף לפורמט הקודם, הפונקציה ממיינת את כל הנתונים בטבלה באמצעות פרטי המחיר בעמודה C:
הקוד החדש משתמש בפרמטר getRange(a1Notation) כדי לציין טווח חדש, המכסה את A3:D55 (כל הטבלה מלבד כותרות העמודות). הקוד מתקשר לשיטה sort(sortSpecObj) כדי למיין את הטבלה. כאן, הפרמטר sortSpecObj הוא מספר העמודה למיון. השיטה ממיינת את הטווח כך שערכי העמודות שצוינו יהיו מהנמוך ביותר לגבוה ביותר (ערכים במגמת עלייה). לשיטה sort(sortSpecObj) יש דרישות מורכבות מורכבות יותר, אבל אין צורך בהן כאן. במסמכי העזר בנושא שיטות אפשר לראות את כל הדרכים השונות שבהן תוכלו להתקשר לטווחי מיון.
מזל טוב, השלמת בהצלחה את כל התרגילים ב-Codelab. הקטע הבא סוקר את הנקודות העיקריות של קוד Lab זה ומציג תצוגה מקדימה של קוד Lab הבא בפלייליסט הזה.
7. סיכום
ואז סיימת את שיעור ה-Lab הזה. עכשיו אתם יכולים להשתמש בכיתות ובתנאים הבסיסיים של שירות הגיליונות האלקטרוניים ב-Apps Script.
מעכשיו אפשר לעבור למעבדת הקוד הבאה.
האם שיעור ה-Lab הזה הועיל לך?
מה כללנו?
- כיצד גיליונות אלקטרוניים, גיליונות וטווחים מיוצגים ב-Apps Script.
- שימושים בסיסיים בכיתות
SpreadsheetApp,Spreadsheet,Sheetו-Range.
המאמרים הבאים
שיעור ה-Lab הבא בפלייליסט הזה מספקת מידע מעמיק יותר לגבי קריאה, כתיבה ושינוי של נתונים בגיליון אלקטרוני.
רוצים למצוא את שיעור ה-Lab הבא שלכם? תמצאו אותו במאמר עבודה עם נתונים.