באמצעות Google Sheets API אפשר ליצור ולעדכן טבלאות צירים בתוך גיליונות אלקטרוניים. הדוגמאות בדף הזה ממחישות איך לבצע פעולות נפוצות בטבלת צירים באמצעות Sheets API.
הדוגמאות האלה מוצגות כבקשות HTTP כדי שיהיו ניטרליות מבחינת שפה. במאמר עדכון גיליונות אלקטרוניים מוסבר איך מטמיעים עדכון באצווה בשפות שונות באמצעות ספריות הלקוח של Google API.
בדוגמאות האלה, התוויות SPREADSHEET_ID ו-SHEET_ID מצביעות על המקומות שבהם צריך לספק את המזהים האלה. המזהה של הגיליון האלקטרוני מופיע בכתובת ה-URL של הגיליון האלקטרוני. אפשר לקבל את מזהה הגיליון באמצעות ה-method spreadsheets.get. טווחי הנתונים מצוינים באמצעות סימון A1. דוגמה לטווח היא Sheet1!A1:D5.
בנוסף, התו SOURCE_SHEET_ID מציין את הגיליון עם נתוני המקור. בדוגמאות האלה, זו הטבלה שמופיעה בקטע נתוני המקור של טבלת הצירים.
נתוני המקור של טבלת הצירים
בדוגמאות הבאות, נניח שבגיליון האלקטרוני שבו נעשה שימוש יש את נתוני המקור הבאים של 'sales' בגיליון הראשון שלו ('Sheet1'). המחרוזות בשורה הראשונה הן תוויות של העמודות השונות. דוגמאות לקריאה מגיליונות אחרים בגיליון האלקטרוני מפורטות בקטע סימון A1.
| A | B | C | D | E | F | G | |
| 1 | קטגוריית פריט | מספר הדגם | עלות | כמות | אזור | אנשי מכירות | תאריך משלוח |
| 2 | גלגל ענק | W-24 | 20.50$ | 4 | מערב | Beth | 1.3.2016 |
| 3 | דלת | D-01X | 15$ | 2 | דרום | אמיר | 15/03/2016 |
| 4 | מנוע | ENG-0134 | $100.00 | 1 | צפון | Carmen | 20/03/2016 |
| 5 | מסגרת | FR-0B1 | 34.00$ | 8 | מזרח | חנה | 3/12/2016 |
| 6 | חלונית | P-034 | 24.00 ש"ח | 4 | צפון | Devyn | 2/04/2016 |
| 7 | חלונית | P-052 | 11.50$ | 7 | מזרח | אריק | 16/05/2016 |
| 8 | גלגל ענק | W-24 | 20.50$ | 11 | דרום | Sheldon | 30/04/2016 |
| 9 | מנוע | ENG-0161 | 330.00$ | 2 | צפון | ג'סי | 2/7/2016 |
| 10 | דלת | D-01Y | 29.00 | 6 | מערב | Armando | 13/03/2016 |
| 11 | מסגרת | FR-0B1 | 34.00$ | 9 | דרום | Yuliana | 27/02/2016 |
| 12 | חלונית | P-102 | 12 ש"ח | 15 | מערב | Carmen | 18/04/2016 |
| 13 | חלונית | P-105 | 8.25$ | 13 | מערב | ג'סי | 20/06/2016 |
| 14 | מנוע | ENG-0211 | 283.00$ | 1 | צפון | אמיר | 21/06/2016 |
| 15 | דלת | D-01X | 15$ | 2 | מערב | Armando | 3.7.2016 |
| 16 | מסגרת | FR-0B1 | 34.00$ | 6 | דרום | Carmen | 15/07/2016 |
| 17 | גלגל ענק | W-25 | 80.00 ILS | 8 | דרום | חנה | 2/05/2016 |
| 18 | גלגל ענק | W-11 | 29.00 | 13 | מזרח | אריק | 19/05/2016 |
| 19 | דלת | D-05 | 17.70$ | 7 | מערב | Beth | 28/06/2016 |
| 20 | מסגרת | FR-0B1 | 34.00$ | 8 | צפון | Sheldon | 30/03/2016 |
הוספת טבלת צירים
בדוגמת הקוד הבאה של spreadsheets.batchUpdate מוסבר איך משתמשים ב-UpdateCellsRequest כדי ליצור טבלת צירים מנתוני המקור, ומאחזקים אותה בתא A50 בגיליון שצוין ב-SHEET_ID.
הבקשה מגדירה את טבלת הצירים באמצעות המאפיינים הבאים:
- קבוצת ערכים אחת (Quantity) שמציינת את מספר המכירות. מכיוון שיש רק קבוצת ערכים אחת, שתי ההגדרות האפשריות של
valueLayoutזהות. - שתי קבוצות של שורות (קטגוריית הפריט ומספר הדגם). הסינון הראשון הוא לפי הערך העולה של Quantity הכולל מRegion 'מערב'. לכן, 'מנוע' (ללא מכירות במערב) מופיע מעל 'דלת' (עם 15 מכירות במערב). הקבוצה Model Number ממוינת בסדר יורד לפי סך המכירות בכל האזורים, כך ש-'W-24' (15 מכירות) מופיע מעל 'W-25' (8 מכירות). לשם כך, מגדירים את השדה
valueBucketלערך{}. - קבוצת עמודות אחת (Region) שממוינת בסדר עולה לפי מספר המכירות הגבוה ביותר.
שוב, הערך של
valueBucketהוא{}. 'צפון' הוא האזור עם המספר הנמוך ביותר של מכירות כוללות, ולכן הוא מופיע בעמודה הראשונה של אזור.
פרוטוקול הבקשה מוצג בהמשך.
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.
הבקשה מגדירה את טבלת הצירים באמצעות המאפיינים הבאים:
- שתי קבוצות ערכים (Quantity ו-Total Price). השדה הראשון מציין את מספר המכירות. השני הוא ערך מחושב שמבוסס על המכפלה של העלות של חלק מסוים ומספר המכירות הכולל שלו, לפי הנוסחה הבאה:
=Cost*SUM(Quantity). - שלוש קבוצות של שורות (קטגוריית הפריט, מספר הדגם ועלות).
- קבוצה אחת של עמודות (Region).
- הקבוצות של השורות והעמודות ממוינות לפי שם (ולא לפי כמות) בכל קבוצה, והטבלה ממוינת לפי סדר האלפבית. כדי לעשות זאת, צריך להשמיט את השדה
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 על ידי הוספת '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 כדי לערוך את טבלת הצירים שנוצרה בקטע הוספת טבלת צירים.
לא ניתן לשנות קבוצות משנה של השדה pivotTable במשאב CellData בנפרד באמצעות הפרמטר fields. כדי לבצע שינויים, צריך לספק את כל השדה pivotTable. למעשה, כדי לערוך טבלת צירים צריך להחליף אותה בטבלה חדשה.
הבקשה מבצעת את השינויים הבאים בטבלת הצירים המקורית:
- הסרת קבוצת השורות השנייה מטבלת הצירים המקורית (Model Number).
- הוספת קבוצת עמודות (נציג מכירות). העמודות ממוינות בסדר יורד לפי המספר הכולל של מכירות Panel. 'Carmen' (15 מכירות Panel) מופיעה משמאל ל-'Jessie' (13 מכירות Panel).
- התכונה מכוונת את העמודה של כל אזור, מלבד 'מערב', ומסתירה את הקבוצה נציג מכירות באזור הזה. כדי לעשות זאת, מגדירים את
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
}
}
],
}