Google Sheets API מאפשר ליצור ולעדכן טבלאות צירים בגיליונות אלקטרוניים. הדוגמאות בדף הזה ממחישות איך אפשר לבצע פעולות נפוצות בטבלת צירים באמצעות Sheets API.
הדוגמאות האלה מוצגות בפורמט של בקשות HTTP ניטרליות מבחינת שפה. על מנת ללמוד כיצד ליישם עדכון ברצף בשפות שונות באמצעות ספריות הלקוח של Google API, קראו את המאמר עדכון גיליונות אלקטרוניים.
בדוגמאות האלה, ערכי ה-placeholder SPREADSHEET_ID
ו-SHEET_ID
מציינים איפה הייתם מספקים את המזהים האלה. המזהה של הגיליון האלקטרוני מופיע בכתובת ה-URL של הגיליון האלקטרוני. אפשר לאתר את מזהה הגיליון באמצעות השיטה spreadsheets.get
. הטווחים מצוינים באמצעות סימון A1. טווח לדוגמה הוא Sheet1!A1:D5.
בנוסף, ה-placeholder SOURCE_SHEET_ID
מציין את הגיליון עם נתוני המקור. בדוגמאות האלה זו הטבלה שמופיעה בקטע נתוני מקור של טבלת צירים.
נתוני מקור של טבלת צירים
לצורך הדוגמאות האלה, נניח שהגיליון הראשון שבו נעשה שימוש כולל את המקור הבא של נתוני 'מכירות' בגיליון הראשון (Sheet1). המחרוזות בשורה הראשונה הן תוויות של העמודות הנפרדות. כדי לראות דוגמאות לקריאה מגיליונות אחרים בגיליון האלקטרוני, קראו את המאמר סימון A1.
A | B | C | D | ה. | F | G | |
1 | Item Category | מספר הדגם | עלות | כמות | אזור | אנשי מכירות | תאריך משלוח |
2 | גלגל ענק | W-24 | 82 ש"ח | 4 | מערב | בת' | 01.03.2016 |
3 | דלת | D-01X | $15.00 | 2 | דרום | אמיר | 15.03.2016 |
4 | מנוע | ENG-0134 | 400.00 ש"ח | 1 | צפון | כרמן | 20.03.2016 |
5 | מסגרת | FR-0B1 | 34.00$ | 8 | מזרח | חנה | 12.03.2016 |
6 | חלונית | P-034 | 24.00 ש"ח | 4 | צפון | דווין | 02.04.2016 |
7 | חלונית | P-052 | 46.00 ש"ח | 7 | מזרח | אריק | 16.05.2016 |
8 | גלגל ענק | W-24 | 82 ש"ח | 11 | דרום | Sheldon | 30.04.2016 |
9 | מנוע | ENG-0161 | $330.00 | 2 | צפון | גלי | 02.07.2016 |
10 | דלת | D-01Y | 116 ש"ח | 6 | מערב | ארמנדו | 13.03.2016 |
11 | מסגרת | FR-0B1 | 34.00$ | 9 | דרום | יוליאנה | 27.02.2016 |
12 | חלונית | P-102 | 12.00 ש"ח | 15 | מערב | כרמן | 18.04.2016 |
13 | חלונית | P-105 | 32.00 ש"ח | 13 | מערב | גלי | 20.06.2016 |
14 | מנוע | ENG-0211 | 1122 ש"ח | 1 | צפון | אמיר | 21.06.2016 |
15 | דלת | D-01X | $15.00 | 2 | מערב | ארמנדו | 03.07.2016 |
16 | מסגרת | FR-0B1 | 34.00$ | 6 | דרום | כרמן | 15.07.2016 |
17 | גלגל ענק | W-25 | 80.00 ILS | 8 | דרום | חנה | 02.05.2016 |
18 | גלגל ענק | W-11 | 116 ש"ח | 13 | מזרח | אריק | 19.05.2016 |
19 | דלת | D-05 | 70 ש"ח | 7 | מערב | בת' | 28.06.2016 |
20 | מסגרת | FR-0B1 | 34.00$ | 8 | צפון | Sheldon | 30.03.2016 |
הוספה של טבלת צירים
דוגמת הקוד הבאה של spreadsheets.batchUpdate
מראה איך להשתמש ב-UpdateCellsRequest
כדי ליצור טבלת צירים מנתוני המקור, לעגן אותה בתא A50 בגיליון שצוין ב-SHEET_ID
.
בבקשה מגדירים את טבלת הצירים עם המאפיינים הבאים:
- קבוצת ערכים אחת (כמות) שמציינת את מספר המכירות. מכיוון שיש רק קבוצת ערכים אחת, שתי ההגדרות האפשריות של
valueLayout
מקבילות. - שתי קבוצות של שורות (קטגוריית פריט ומספר דגם). מיון ראשון מתבצע בסדר עולה של כמות הכוללת מהאזור 'מערב'. לכן,
"מנוע" (ללא מכירות ממערב) מופיע מעל "דלת" (עם 15 מכירות מערביות). הקבוצה מספר מודל מסודרת בסדר יורד של סך המכירות בכל האזורים, כך שהשדה "W-24" (15 מכירות) מופיע מעל W-25 (8 מכירות). כדי לעשות זאת, מגדירים את השדה
valueBucket
לערך{}
. - קבוצת עמודות אחת (אזור) שממיינת בסדר עולה של רוב המכירות.
שוב, הערך של
valueBucket
הוא{}
. בעמודה "צפון" יש את סך המכירות הנמוך ביותר, ולכן היא מופיעה בעמודה Region הראשונה.
פרוטוקול הבקשה מוצג בהמשך.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID
:batchUpdate
{ "requests": [ { "updateCells": { "rows": [ { "values": [ { "pivotTable": { "source": { "sheetId":SOURCE_SHEET_ID
, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": 20, "endColumnIndex": 7 }, "rows": [ { "sourceColumnOffset": 0, "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } }, { "sourceColumnOffset": 1, "showTotals": true, "sortOrder": "DESCENDING", "valueBucket": {} } ], "columns": [ { "sourceColumnOffset": 4, "sortOrder": "ASCENDING", "showTotals": true, "valueBucket": {} } ], "values": [ { "summarizeFunction": "SUM", "sourceColumnOffset": 3 } ], "valueLayout": "HORIZONTAL" } } ] } ], "start": { "sheetId":SHEET_ID
, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }
הבקשה יוצרת טבלת צירים באופן הבא:
הוספה של טבלת צירים עם ערכים מחושבים
דוגמת הקוד הבאה של spreadsheets.batchUpdate
מראה איך להשתמש ב-UpdateCellsRequest
כדי ליצור טבלת צירים עם קבוצת ערכים מחושבת מנתוני המקור, תוך עיגון בתא A50 בגיליון שצוין ב-SHEET_ID
.
בבקשה מגדירים את טבלת הצירים עם המאפיינים הבאים:
- שתי קבוצות של ערכים (כמות ומחיר כולל). הראשון מציין את
מספר המכירות. הערך השני הוא ערך מחושב שמבוסס על המכפלה של עלות החלק ועל מספר המכירות הכולל, לפי הנוסחה הבאה:
=Cost*SUM(Quantity)
. - שלוש קבוצות של שורות (קטגוריית פריט, מספר מודל ועלות).
- קבוצת עמודות אחת (אזור).
- הקבוצות של השורות והעמודות ממוינות לפי שם (ולא לפי כמות) בכל קבוצה, לפי סדר האלפבית של הטבלה. כדי לעשות זאת, משמיטים את השדה
valueBucket
מה-PivotGroup
. - כדי לפשט את מראה הטבלה, הבקשה מסתירה סכומי ביניים בכל קבוצות השורות והעמודות, מלבד אלה שבשורה הראשית.
- בבקשה הוגדר הערך
VERTICAL
בשדהvalueLayout
, כדי לשפר את המראה של הטבלה. הערךvalueLayout
חשוב רק אם יש 2 קבוצות ערכים או יותר.
פרוטוקול הבקשה מוצג בהמשך.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID
:batchUpdate
{ "requests": [ { "updateCells": { "rows": [ { "values": [ { "pivotTable": { "source": { "sheetId":SOURCE_SHEET_ID
, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": 20, "endColumnIndex": 7 }, "rows": [ { "sourceColumnOffset": 0, "showTotals": true, "sortOrder": "ASCENDING" }, { "sourceColumnOffset": 1, "showTotals": false, "sortOrder": "ASCENDING", }, { "sourceColumnOffset": 2, "showTotals": false, "sortOrder": "ASCENDING", } ], "columns": [ { "sourceColumnOffset": 4, "sortOrder": "ASCENDING", "showTotals": true } ], "values": [ { "summarizeFunction": "SUM", "sourceColumnOffset": 3 }, { "summarizeFunction": "CUSTOM", "name": "Total Price", "formula": "=Cost*SUM(Quantity)" } ], "valueLayout": "VERTICAL" } } ] } ], "start": { "sheetId":SHEET_ID
, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }
הבקשה יוצרת טבלת צירים באופן הבא:
מחיקת טבלת צירים
דוגמת הקוד הבאה spreadsheets.batchUpdate
מראה איך להשתמש ב-UpdateCellsRequest
כדי למחוק טבלת צירים (אם קיימת) שמעוגנת בתא A50 בגיליון שצוין על ידי SHEET_ID
.
UpdateCellsRequest
יכול להסיר טבלת צירים על ידי הוספת "pivotTable" בפרמטר fields
, וגם להשמיט את השדה pivotTable
בתא העוגן.
פרוטוקול הבקשה מוצג בהמשך.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID
:batchUpdate
{
"requests": [
{
"updateCells": {
"rows": [
{
"values": [
{}
]
}
],
"start": {
"sheetId": SHEET_ID
,
"rowIndex": 49,
"columnIndex": 0
},
"fields": "pivotTable"
}
}
]
}
עריכה של עמודות ושורות בטבלת הצירים
דוגמת הקוד הבאה של spreadsheets.batchUpdate
מראה איך להשתמש ב-UpdateCellsRequest
כדי לערוך את טבלת הצירים שנוצרה ב-Add a Pivot Table.
לא ניתן לשנות בנפרד קבוצות משנה של השדה pivotTable
במשאב CellData
באמצעות הפרמטר fields
. כדי לערוך, צריך לספק את כל השדה pivotTable
. בעיקרון, כדי לערוך טבלת צירים צריך להחליף אותה בטבלה חדשה.
הבקשה מבצעת את השינויים הבאים בטבלת הצירים המקורית:
- מסירה את קבוצת השורות השנייה מטבלת הצירים המקורית (מספר המודל).
- מוסיף קבוצת עמודות (איש מכירות). העמודות ממוינות בסדר יורד לפי המספר הכולל של מכירות ב-Panel. "Carmen" (מכירות של Panel 15) מופיע משמאל ל-Jessie (13 מכירות של Panel).
- העמודה מכווץת את העמודה לכל Region, חוץ מהעמודה West, ומסתירה את הקבוצה Salesperson באותו אזור. כדי לעשות זאת, מגדירים את
collapsed
לערךtrue
בעמודהvalueMetadata
בעמודה Region.
פרוטוקול הבקשה מוצג בהמשך.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID
:batchUpdate
{ "requests": [ { "updateCells": { "rows": [ { "values": [ { "pivotTable": { "source": { "sheetId":SOURCE_SHEET_ID
, "startRowIndex": 0, "startColumnIndex": 0, "endRowIndex": 20, "endColumnIndex": 7 }, "rows": [ { "sourceColumnOffset": 0, "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } } ], "columns": [ { "sourceColumnOffset": 4, "sortOrder": "ASCENDING", "showTotals": true, "valueBucket": {}, "valueMetadata": [ { "value": { "stringValue": "North" }, "collapsed": true }, { "value": { "stringValue": "South" }, "collapsed": true }, { "value": { "stringValue": "East" }, "collapsed": true } ] }, { "sourceColumnOffset": 5, "sortOrder": "DESCENDING", "showTotals": false, "valueBucket": { "buckets": [ { "stringValue": "Panel" } ] }, } ], "values": [ { "summarizeFunction": "SUM", "sourceColumnOffset": 3 } ], "valueLayout": "HORIZONTAL" } } ] } ], "start": { "sheetId":SHEET_ID
, "rowIndex": 49, "columnIndex": 0 }, "fields": "pivotTable" } } ] }
הבקשה יוצרת טבלת צירים באופן הבא:
קריאת הנתונים של טבלת הצירים
דוגמת הקוד הבאה של spreadsheets.get
מראה איך לקבל נתונים של טבלת צירים מגיליון אלקטרוני. פרמטר השאילתה fields
מציין שיש להחזיר רק את הנתונים של טבלת הצירים (בניגוד לנתוני הערך של התא).
פרוטוקול הבקשה מוצג בהמשך.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID
?fields=sheets(properties.sheetId,data.rowData.values.pivotTable)
התגובה מורכבת ממשאב Spreadsheet
שמכיל אובייקט Sheet
עם רכיבי SheetProperties
. יש גם מערך של רכיבי GridData
שמכילים מידע על ה-PivotTable
.
המידע בטבלת הצירים נמצא במשאב CellData
של הגיליון בתא שבו הטבלה מעוגנת (כלומר, הפינה השמאלית העליונה של הטבלה). אם בשדה מסוים מוגדר ערך ברירת המחדל, הוא לא ייכלל בתשובה.
בדוגמה הזו, בגיליון הראשון (SOURCE_SHEET_ID
) מופיעים נתוני המקור של הטבלה הגולמית, והגיליון השני (SHEET_ID
) כולל טבלת הצירים שמוצמדת ל-B3. הסוגריים המסולסלים הריקים מציינים גיליונות או תאים שלא מכילים נתונים של טבלת הצירים. לידיעתך, הבקשה הזו מחזירה גם את מזהי הגיליונות.
{ "sheets": [ { "data": [{}], "properties": { "sheetId":SOURCE_SHEET_ID
} }, { "data": [ { "rowData": [ {}, {}, { "values": [ {}, { "pivotTable": { "columns": [ { "showTotals": true, "sortOrder": "ASCENDING", "sourceColumnOffset": 4, "valueBucket": {} } ], "rows": [ { "showTotals": true, "sortOrder": "ASCENDING", "valueBucket": { "buckets": [ { "stringValue": "West" } ] } }, { "showTotals": true, "sortOrder": "DESCENDING", "valueBucket": {}, "sourceColumnOffset": 1 } ], "source": { "sheetId":
SOURCE_SHEET_ID
, "startColumnIndex": 0, "endColumnIndex": 7, "startRowIndex": 0, "endRowIndex": 20 }, "values": [ { "sourceColumnOffset": 3, "summarizeFunction": "SUM" } ] } } ] } ] } ], "properties": { "sheetId":
SHEET_ID
} } ], }