טבלאות צירים

‫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	מזרח	חנה	12/03/2016
6	חלונית	P-034	24.00 ש"ח	4	צפון	Devyn	02/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	צפון	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	דרום	חנה	02/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.

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

• קבוצת ערכים אחת (כמות) שמציינת את מספר המכירות. מכיוון שיש רק קבוצת ערכים אחת, שתי ההגדרות האפשריות של valueLayout שקולות.
• שתי קבוצות שורות (קטגוריית פריט ומספר דגם). המיון הראשון הוא לפי הערך העולה של Quantity הכולל מ-Region ‏West. לכן, המילה 'מנוע' (בלי מכירות במערב) מופיעה מעל המילה 'דלת' (עם 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
      }
    }
  ],
}