טבלאות צירים

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

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

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

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

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

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

	A	B	C	D	E	F	G
1	Item Category	מספר הדגם	עלות	כמות	אזור	אנשי מכירות	תאריך משלוח
2	גלגל ענק	W-24	82.00 ש"ח	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	136.00 ש"ח	8	מזרח	חנה	12.03.2016
6	חלונית	P-034	24.00 ש"ח	4	צפון	דוין	02.04.2016
7	חלונית	P-052	48.00 ש"ח	7	מזרח	אריק	16.05.2016
8	גלגל ענק	W-24	82.00 ש"ח	11	דרום	Sheldon	30.04.2016
9	מנוע	ENG-0161	$330.00	2	צפון	גלי	02.07.2016
‏10	דלת	D-01Y	$29.00	6	מערב	ארמנדו	13.03.2016
11	מסגרת	FR-0B1	136.00 ש"ח	9	דרום	יוליאנה	27.02.2016
12	חלונית	P-102	12.00 ש"ח	15	מערב	כרמן	18.04.2016
13	חלונית	P-105	34.00 ש"ח	13	מערב	גלי	20.06.2016
14	מנוע	ENG-0211	1,132 ש"ח	1	צפון	אמיר	21.06.2016
15	דלת	D-01X	$15.00	2	מערב	ארמנדו	3.07.2016
16	מסגרת	FR-0B1	136.00 ש"ח	6	דרום	כרמן	15.07.2016
17	גלגל ענק	W-25	$20.00	8	דרום	חנה	02.05.2016
18	גלגל ענק	W-11	$29.00	13	מזרח	אריק	19.05.2016
19	דלת	D-05	70.80 ש"ח	7	מערב	בת'	28.06.2016
20	מסגרת	FR-0B1	136.00 ש"ח	8	צפון	Sheldon	30.03.2016

הוספה של טבלת צירים

דוגמת הקוד הבאה של spreadsheets.batchUpdate מראה איך להשתמש בטבלה UpdateCellsRequest כדי ליצור טבלת צירים מנתוני המקור, לעגן אותה לתא A50 בגיליון שצוין ב-SHEET_ID.

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

• קבוצת ערכים אחת (כמות) שמציינת את מספר המכירות. מכיוון שיש רק קבוצת ערכים אחת, שתי ההגדרות האפשריות של valueLayout מקבילות.
• שתי קבוצות של שורות (קטגוריית פריט ומספר המודל). הפונקציה הראשונה ממיינת בערך עולה של הכמות הכוללת מהאזור "West". לכן, "מנוע" (ללא מכירות במערב) מופיע מעל "דלת" (עם 15 מכירות במערב). הקבוצה Model Number ממיינת בסדר יורד של סך המכירות בכל האזורים, כך שהערך W-24 ( 15 sales) מופיע מעל W-25 (8 מכירות). כדי לעשות את זה, מגדירים את השדה valueBucket לערך {}.
• קבוצת עמודות אחת (Region) שממיינת בסדר עולה של רוב המכירות. שוב, 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.
• כדי לפשט את המראה של הטבלה, הבקשה מסתירה את סכומי הביניים של כל הקבוצות של השורות והעמודות הראשיות.
• הבקשה מגדירה את 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 כדי לערוך את טבלת הצירים שנוצרה ב-Add a pivot table.

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

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

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