טבלאות צירים

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

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

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

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

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

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

	A	B	C	D	E	F	G
1	קטגוריית פריט	מספר הדגם	עלות	כמות	אזור	אנשי מכירות	תאריך משלוח
2	גלגל ענק	W-24	82.00 ש"ח	4	מערב	Beth	1.3.2016
3	דלת	D-01X	15$	2	דרום	אמיר	15/03/2016
4	מנוע	ENG-0134	400.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	136 ש"ח	9	דרום	יוליאנה	27.02.2016
12	חלונית	P-102	12 ש"ח	15	מערב	Carmen	18/04/2016
13	חלונית	P-105	34.00 ש"ח	13	מערב	גלי	20/06/2016
14	מנוע	ENG-0211	283.00$‎	1	צפון	אמיר	21/06/2016
15	דלת	D-01X	15$	2	מערב	Armando	3.07.2016
16	מסגרת	FR-0B1	34.00$	6	דרום	Carmen	15.07.2016
17	גלגל ענק	W-25	80 ש"ח	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 זהות.
• שתי קבוצות של שורות (קטגוריית פריט ומספר המודל). הפונקציה הראשונה ממיינת בערך עולה של הכמות הכוללת מהאזור "West". לכן, 'מנוע' (ללא מכירות במערב) מופיע מעל 'דלת' (עם 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).
• מכווץ את העמודה של כל 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
      }
    }
  ],
}