טבלאות צירים

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

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

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

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

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

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

	A	B	C	D	ה.	F	G
1	Item Category	מספר הדגם	עלות	כמות	אזור	אנשי מכירות	תאריך משלוח
2	גלגל ענק	W-24	82 ש"ח	4	מערב	בת'	01.03.2016
3	דלת	D-01X	$15.00	2	דרום	אמיר	15.03.2016
4	מנוע	ENG-0134	400.00 ש"ח	1	צפון	כרמן	20.03.2016
5	מסגרת	FR-0B1	34.00$	8	מזרח	חנה	12.03.2016
6	חלונית	P-034	24.00 ש"ח	4	צפון	דווין	02.04.2016
7	חלונית	P-052	46.00 ש"ח	7	מזרח	אריק	16.05.2016
8	גלגל ענק	W-24	82 ש"ח	11	דרום	Sheldon	30.04.2016
9	מנוע	ENG-0161	$330.00	2	צפון	גלי	02.07.2016
10	דלת	D-01Y	116 ש"ח	6	מערב	ארמנדו	13.03.2016
11	מסגרת	FR-0B1	34.00$	9	דרום	יוליאנה	27.02.2016
12	חלונית	P-102	12.00 ש"ח	15	מערב	כרמן	18.04.2016
13	חלונית	P-105	32.00 ש"ח	13	מערב	גלי	20.06.2016
14	מנוע	ENG-0211	1122 ש"ח	1	צפון	אמיר	21.06.2016
15	דלת	D-01X	$15.00	2	מערב	ארמנדו	03.07.2016
16	מסגרת	FR-0B1	34.00$	6	דרום	כרמן	15.07.2016
17	גלגל ענק	W-25	80.00 ILS	8	דרום	חנה	02.05.2016
18	גלגל ענק	W-11	116 ש"ח	13	מזרח	אריק	19.05.2016
19	דלת	D-05	70 ש"ח	7	מערב	בת'	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 הוא {}. בעמודה "צפון" יש את סך המכירות הנמוך ביותר, ולכן היא מופיעה בעמודה 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.
• כדי לפשט את מראה הטבלה, הבקשה מסתירה סכומי ביניים בכל קבוצות השורות והעמודות, מלבד אלה שבשורה הראשית.
• בבקשה הוגדר הערך VERTICAL בשדה valueLayout, כדי לשפר את המראה של הטבלה. הערך 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 כדי לערוך את טבלת הצירים שנוצרה ב-Add a Pivot Table.

לא ניתן לשנות בנפרד קבוצות משנה של השדה pivotTable במשאב CellData באמצעות הפרמטר fields. כדי לערוך, צריך לספק את כל השדה pivotTable. בעיקרון, כדי לערוך טבלת צירים צריך להחליף אותה בטבלה חדשה.

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

• מסירה את קבוצת השורות השנייה מטבלת הצירים המקורית (מספר המודל).
• מוסיף קבוצת עמודות (איש מכירות). העמודות ממוינות בסדר יורד לפי המספר הכולל של מכירות ב-Panel. "Carmen" (מכירות של Panel 15) מופיע משמאל ל-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
      }
    }
  ],
}