טבלאות צירים

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