Google Sheets API מאפשר ליצור טבלאות צירים ולעדכן אותן בגיליונות אלקטרוניים. הדוגמאות בדף הזה ממחישות איך אפשר ליצור טבלת צירים נפוצה פעולות באמצעות ה-API של Sheets.
הדוגמאות האלה מוצגות בצורה של בקשות HTTP ניטרלי. כדי ללמוד איך להטמיע עדכון בכמות גדולה בשפות שונות באמצעות על ספריות הלקוח של Google API, ראו עדכון גיליונות אלקטרוניים.
בדוגמאות האלה, ערכי ה-placeholders SPREADSHEET_ID
ו-SHEET_ID
מציין איפה תציינו את המזהים האלה. הגיליון האלקטרוני נמצא
ID בכתובת ה-URL של הגיליון האלקטרוני. אפשר לקבל
את מזהה הגיליון באמצעות
spreadsheets.get
.
מציינים טווחים באמצעות סימון A1.
הטווח לדוגמה הוא Sheet1!A1:D5.
כמו כן, ה-placeholder SOURCE_SHEET_ID
מציין את הגיליון
בנתוני המקור. בדוגמאות האלו, זו הטבלה שמופיעה בקטע ציר
נתוני המקור של הטבלה.
נתוני מקור של טבלת צירים
בדוגמאות האלה, נניח שהגיליון האלקטרוני שבו אתם משתמשים כולל את המקור הבא "מכירות" בגיליון הראשון ("גיליון1"). המחרוזות בשורה הראשונה של העמודות הנפרדות. כדי לראות דוגמאות לקריאה ממקורות אחרים גיליונות בגיליון האלקטרוני, ראו סימון A1.
A | B | C | D | E | F | G | |
1 | קטגוריית פריט | מספר הדגם | עלות | כמות | אזור | אנשי מכירות | תאריך משלוח |
2 | גלגל ענק | W-24 | 82.00 ש"ח | 4 | מערב | בת' | 01.03.2016 |
3 | דלת | D-01X | $15.00 | 2 | דרום | אמיר | 15.03.2016 |
4 | מנוע | ENG-0134 | 400.00 ש"ח | 1 | צפון | Carmen | 20.03.2016 |
5 | מסגרת | FR-0B1 | 136.00 ש"ח | 8 | מזרח | חנה | 12.03.2016 |
6 | חלונית | P-034 | 24.00 ש"ח | 4 | צפון | Devyn | 02.04.2016 |
7 | חלונית | P-052 | 48.00 ש"ח | 7 | מזרח | אריק | 16.05.2016 |
8 | גלגל ענק | W-24 | 82.00 ש"ח | 11 | דרום | Sheldon | 30.04.2016 |
9 | מנוע | ENG-0161 | $330.00 | 2 | צפון | גלי | 02.07.2016 |
10 | דלת | D-01Y | $29.00 | 6 | מערב | Armando | 13.03.2016 |
11 | מסגרת | FR-0B1 | 136 ש"ח | 9 | דרום | Yuliana | 27.02.2016 |
12 | חלונית | P-102 | 12 ש"ח | 15 | מערב | Carmen | 18.04.2016 |
13 | חלונית | P-105 | 34.00 ש"ח | 13 | מערב | גלי | 20.06.2016 |
14 | מנוע | ENG-0211 | 1,132 ש"ח | 1 | צפון | אמיר | 21.06.2016 |
15 | דלת | D-01X | $15.00 | 2 | מערב | Armando | 3.07.2016 |
16 | מסגרת | FR-0B1 | 136.00 ש"ח | 6 | דרום | Carmen | 15.07.2016 |
17 | גלגל ענק | W-25 | $20.00 | 8 | דרום | חנה | 02.05.2016 |
18 | גלגל ענק | W-11 | $29.00 | 13 | מזרח | אריק | 19.05.2016 |
19 | דלת | D-05 | 70.80 ש"ח | 7 | מערב | בת' | 28.06.2016 |
20 | מסגרת | FR-0B1 | 136.00 ש"ח | 8 | צפון | Sheldon | 30.03.2016 |
הוספה של טבלת צירים
הבאים
spreadsheets.batchUpdate
דוגמת הקוד מראה איך להשתמש
UpdateCellsRequest
ליצור טבלת צירים מנתוני המקור, לעגן אותה לתא A50
גיליון שצוין על ידי SHEET_ID
.
הבקשה מגדירה את טבלת הצירים עם המאפיינים הבאים:
- קבוצת ערכים אחת (כמות) שמציינת את מספר המכירות. מאז
יש רק קבוצת ערכים אחת, שני הערכים האפשריים
valueLayout
ההגדרות מקבילות. - שתי קבוצות של שורות (קטגוריית פריט ומספר המודל). המיון הראשון מתבצע
ערך עולה של הכמות הכוללת מ'מערב' אזור. לכן,
"מנוע" (ללא מכירות במערב) מופיעה מעל 'דלת' (עם 15 מכירות מערב).
קבוצת מספר המודל ממוינת בסדר יורד של סך כל המכירות בכל
ו-"W-24" (15 מכירות) מופיע מעל "W-25" (8 מכירות). סיימתי
על ידי הגדרה של
valueBucket
לשדה{}
. - קבוצת עמודות אחת (Region) שממיינת בסדר עולה של רוב המכירות.
שוב,
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
- כדי לפשט את המראה של הטבלה, הבקשה מסתירה את סכומי הביניים עבור כל הקבוצות של השורות והעמודות הראשיות.
- הבקשה מגדירה
valueLayout
אלVERTICAL
כדי לשפר את מראה הטבלה.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
יכול להסיר טבלת צירים על ידי הוספת טבלת צירים באזור
הפרמטר 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
כדי לערוך את טבלת הצירים שנוצרה בהוספה של טבלת צירים.
קבוצות המשנה של
pivotTable
בשדה
מקור מידע על CellData
לא ניתן לשנות בנפרד עם הפרמטר fields
. כדי לבצע עריכות,
יש לספק את כל השדה pivotTable
. למעשה, עריכת טבלת צירים
דורש להחליף אותו במכשיר חדש.
הבקשה מבצעת את השינויים הבאים בטבלת הצירים המקורית:
- הסרה של קבוצת השורות השנייה מטבלת הצירים המקורית (מספר מודל).
- הוספה של קבוצת עמודות (Salesperson). העמודות ממוינות בסדר יורד לפי המספר הכולל של מכירות Panel. "כרמן" (15 מכירות לוחות) נראה משמאל ל"Jessie" (13 מכירות לוחות).
- מכווץ את העמודה של כל אזור, חוץ מ'מערב', ומסתיר את העמודה.
קבוצה של אנשי מכירות לאזור הזה. כדי לעשות זאת, מגדירים את
collapsed
לערךtrue
ב-valueMetadata
בעמודה הזו בקבוצת העמודות אזור.
פרוטוקול הבקשה מוצג למטה.
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
} } ], }