טבלאות צירים

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

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

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

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

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

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

A B C D E F G
1 קטגוריית פריט מספר הדגם עלות כמות אזור אנשי מכירות תאריך משלוח
2 גלגל ענק W-24 20.50$‎ 4 מערב Beth 1.3.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 מזרח חנה 3/12/2016
6 חלונית P-034 24.00 ש"ח 4 צפון דניאל 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 34.00$ 9 דרום Yuliana 27/02/2016
12 חלונית P-102 12 ש"ח 15 מערב Carmen 18/04/2016
13 חלונית P-105 8.25$‎ 13 מערב ג'סי 20/06/2016
14 מנוע ENG-0211 283.00$‎ 1 צפון Amir 21/06/2016
15 דלת D-01X ‎$15.00 2 מערב Armando 3.7.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 זהות.
  • שתי קבוצות של שורות (קטגוריית הפריט ומספר הדגם). הסינון הראשון הוא לפי הערך העולה של Quantity הכולל מRegion 'מערב'. לכן, 'מנוע' (ללא מכירות במערב) מופיע מעל 'דלת' (עם 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).
  • התכונה מכוונת את העמודה של כל אזור, מלבד 'מערב', ומסתירה את הקבוצה נציג מכירות באזור הזה. כדי לעשות זאת, מגדירים את 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
      }
    }
  ],
}