טבלאות צירים

‫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 ב 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 מזרח Erik 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 מזרח Erik 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 שקולות.
  • שתי קבוצות שורות (קטגוריית פריט ומספר דגם). המיון הראשון הוא לפי הערך העולה של הכמות הכוללת מהאזור 'מערב'. לכן, המילה Engine (ללא מכירות במערב) מופיעה מעל המילה Door (עם 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
      }
    }
  ],
}