טבלאות צירים

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

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

בדוגמאות האלה, ערכי הפלייסהולדר SPREADSHEET_ID ו-SHEET_ID מציינים את המקומות שבהם צריך לספק את המזהים האלה. המזהה של הגיליון האלקטרוני מופיע בכתובת ה-URL של הגיליון האלקטרוני. אפשר לאתר את מזהה הגיליון האלקטרוני באמצעות ה-method‏ spreadsheets.get. הטווחים מצוינים באמצעות סימון A1. טווח לדוגמה הוא Sheet1!A1:D5.

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

נתוני המקור של טבלת צירים

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

	A	B	C	D	E	F	G
1	קטגוריית פריט	מספר הדגם	עלות	כמות	אזור	אנשי מכירות	תאריך משלוח
2	גלגל ענק	W-24	$20.50	4	מערב	Beth	01/03/2016
3	דלת	D-01X	$15.00	2	דרום	Amir	15/03/2016
4	מנוע	ENG-0134	$100.00	1	צפון	Carmen	20/03/2016
5	מסגרת	FR-0B1	$34.00	8	מזרח	Hannah	12/03/2016
6	חלונית	P-034	24.00 ש"ח	4	צפון	Devyn	02/04/2016
7	חלונית	P-052	‪11.50$	7	מזרח	Erik	16/05/2016
8	גלגל ענק	W-24	$20.50	11	דרום	Sheldon	30/04/2016
9	מנוע	ENG-0161	$330.00	2	צפון	Jessie	02/07/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	מערב	Jessie	20/06/2016
14	מנוע	ENG-0211	‪$283.00	1	צפון	Amir	21/06/2016
15	דלת	D-01X	$15.00	2	מערב	Armando	03/07/2016
16	מסגרת	FR-0B1	$34.00	6	דרום	Carmen	15/07/2016
17	גלגל ענק	W-25	$20.00	8	דרום	Hannah	02/05/2016
18	גלגל ענק	W-11	$29.00	13	מזרח	Erik	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.

הבקשה מגדירה את הטבלה הדינמית עם המאפיינים הבאים:

• קבוצת ערכים אחת (כמות) שמציינת את מספר המכירות. מכיוון שיש רק קבוצת ערכים אחת, שתי ההגדרות האפשריות של valueLayout שקולות.
• שתי קבוצות שורות (קטגוריית פריט ומספר דגם). המיון הראשון הוא לפי ערך עולה של הכמות הכוללת מהאזור 'מערב'. לכן, המילה 'מנוע' (ללא מכירות במערב) מופיעה מעל המילה 'דלת' (עם 15 מכירות במערב). הקבוצה מספר הדגם ממוינת בסדר יורד לפי סך המכירות בכל האזורים, כך שהדגם W-24 (15 מכירות) מופיע מעל הדגם W-25 (8 מכירות). כדי לעשות זאת, צריך להגדיר את השדה valueBucket לערך {}.
• קבוצת עמודות אחת (אזור) שממוינת בסדר עולה לפי נפח המכירות. שוב, 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).
• שלוש קבוצות שורות (קטגוריית פריט, מספר דגם ועלות).
• קבוצה אחת של עמודות (אזור).
• הקיבוצים של השורות והעמודות ממוינים לפי שם (ולא לפי כמות) בכל קבוצה, כך שהטבלה ממוינת לפי סדר האלפבית. כדי לעשות את זה, משמיטים את השדה 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. בעצם, כדי לערוך טבלת צירים צריך להחליף אותה בטבלה חדשה.

הבקשה מבצעת את השינויים הבאים בטבלת הצירים המקורית:

• הסרה של קבוצת השורות השנייה מטבלת הציר המקורית (מספר הדגם).
• הוספה של קבוצת עמודות (Salesperson). העמודות ממוינות בסדר יורד לפי המספר הכולל של מכירות החבילה. ‫'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
      }
    }
  ],
}