Search code examples
node.jsgoogle-sheetsgoogle-sheets-api

Adjust Pivot Table Range - Google Sheets API

I would like to use the Google Sheets Node API to adjust the RANGE value. (See screenshot).

enter image description here

I have searched far and wide and can't seem to get an answer here. Any help would be appreciated.

Thanks so much.

Solution

  • I believe your goal is as follows.

    • You want to set the source range of the pivot table using Sheets API with googleapis for Node.js.

    In this case, how about the following sample script?

    Sample script:

    const sheets = google.sheets({ version: "v4", auth }); // Please use your client.

const spreadsheetId = "###"; // Please set your spreadsheet ID.
const sheetIdOfPivotTable = "###"; // Please set sheet ID of pivit table.
const sheetIdOfSourceSheet = "###"; // Please set the sheet ID of the source sheet for your pivot table.

const requests = [
  {
    updateCells: {
      rows: [
        {
          values: [
            {
              pivotTable: {
                source: {
                  sheetId: sheetIdOfSourceSheet,
                  startRowIndex: 0,
                  endRowIndex: 10,
                  startColumnIndex: 0,
                  endColumnIndex: 3,
                },
              },
            },
          ],
        },
      ],
      fields: "pivotTable.source",
      range: {
        sheetId: sheetIdOfPivotTable,
        startRowIndex: 14,
        endRowIndex: 15,
        startColumnIndex: 5,
        endColumnIndex: 6,
      },
    },
  },
];
await sheets.spreadsheets
  .batchUpdate({ spreadsheetId, resource: { requests } })
  .catch(({ errors }) => console.log(errors));

    • When this script is run, the source range of the pivot table of "F15" of sheetIdOfPivotTable is updated to A1:C10 of sheetIdOfSourceSheet.

    • In this case, please set the range as the grid range. And, in this sample, it supposes that the pivot table is existing in the cell "F15" of sheetIdOfPivotTable. So, the range is as follows.

      {
  sheetId: sheetIdOfPivotTable,
  startRowIndex: 14,
  endRowIndex: 15,
  startColumnIndex: 5,
  endColumnIndex: 6,
}

    • In this sample, it supposes that the following grid range of source range (you want to update) is used.

      {
  sheetId: sheetIdOfSourceSheet,
  startRowIndex: 0,
  endRowIndex: 10,
  startColumnIndex: 0,
  endColumnIndex: 3,
}
      • This grid range is used, A1:C10 of a sheet of sheetIdOfSourceSheet is set as the source range.

    References: