טבלאות צירים

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

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

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

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

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

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

A B C D E F G
1 קטגוריית פריט מספר הדגם עלות כמות אזור אנשי מכירות תאריך משלוח
2 גלגל ענק W-24 82.00 ש"ח 4 מערב בת' 01.03.2016
3 דלת D-01X $15.00 2 דרום אמיר 15.03.2016
4 מנוע ENG-0134 400.00 ש"ח 1 צפון Carmen 20.03.2016
5 מסגרת FR-0B1 136.00 ש"ח 8 מזרח חנה 12.03.2016
6 חלונית P-034 24.00 ש"ח 4 צפון Devyn 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 מערב Armando 13.03.2016
11 מסגרת FR-0B1 136 ש"ח 9 דרום Yuliana 27.02.2016
12 חלונית P-102 12 ש"ח 15 מערב Carmen 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 מערב Armando 3.07.2016
16 מסגרת FR-0B1 136.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 70.80 ש"ח 7 מערב בת' 28.06.2016
20 מסגרת FR-0B1 136.00 ש"ח 8 צפון Sheldon 30.03.2016

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

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

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

  • קבוצת ערכים אחת (כמות) שמציינת את מספר המכירות. מאז יש רק קבוצת ערכים אחת, שני הערכים האפשריים valueLayout ההגדרות מקבילות.
  • שתי קבוצות של שורות (קטגוריית פריט ומספר המודל). המיון הראשון מתבצע ערך עולה של הכמות הכוללת מ'מערב' אזור. לכן, "מנוע" (ללא מכירות במערב) מופיעה מעל 'דלת' (עם 15 מכירות מערב). קבוצת מספר המודל ממוינת בסדר יורד של סך כל המכירות בכל ו-"W-24" (15 מכירות) מופיע מעל "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 יכול להסיר טבלת צירים על ידי הוספת טבלת צירים באזור הפרמטר 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). העמודות ממוינות בסדר יורד לפי המספר הכולל של מכירות Panel. "כרמן" (15 מכירות לוחות) נראה משמאל ל"Jessie" (13 מכירות לוחות).
  • מכווץ את העמודה של כל אזור, חוץ מ'מערב', ומסתיר את העמודה. קבוצה של אנשי מכירות לאזור הזה. כדי לעשות זאת, מגדירים את collapsed לערך true ב- valueMetadata בעמודה הזו בקבוצת העמודות אזור.

פרוטוקול הבקשה מוצג למטה.

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
      }
    }
  ],
}