ReferenceError: "Sheets" is not defined

Viewed 21913

This is my first attempt with script editor. I was assigned to do a script to crate pivot table for google sheet.

  //creating pivot table through script editor for google sheet
  function addPivotTable() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "Sheet1";

  // Create a new sheet which will contain our Pivot Table
  var pivotTableSheet = ss.insertSheet();
  var pivotTableSheetId = pivotTableSheet.getSheetId();

 // Add Pivot Table to new sheet
 // Meaning we send an 'updateCells' request to the Sheets API
 // Specifying via 'start' the sheet where we want to place our Pivot Table
 // And in 'rows' the parameters of our Pivot Table 

  var requests = [{
    // Meaning we send an 'updateCells' request to the Sheets API
    "updateCells": {
        // And in 'rows' the parameters of our Pivot Table 
       "rows": {
         "values": [
      {
         // Add Pivot Table to new sheet
        "pivotTable": {
          "source": {
            "sheetId": ss.getSheetByName(sheetName).getSheetId(),
            "startRowIndex": 0,
            "startColumnIndex": 0,
          },
          //create rows from the selected columns
          "rows": [
            {
              "sourceColumnOffset": 14,
              "showTotals": true,
              "sortOrder": "ASCENDING",   
            },
          ],
          //show values from the selected columns
          "values": [
            {
              "summarizeFunction": "COUNTA",
              "sourceColumnOffset": 10
            }
          ],
          //display in horizontal layout
          "valueLayout": "HORIZONTAL"
        }
      }
    ]
  },
  // Specifying via 'start' the sheet where we want to place our Pivot Table
  "start": {
    "sheetId": pivotTableSheetId,
  },
  "fields": "pivotTable"
}
 }];

  Sheets.Spreadsheets.batchUpdate({'requests': [requests]}, ss.getId());
}

Please do check my code and explain where did i went wrong as every time im running the script editor error telling sheet is not defined popup. "ReferenceError: "Sheets" is not defined. (line 46, file "Code")Dismiss"

2 Answers

This an advanced service from google. You need to enable this service before using it. https://developers.google.com/apps-script/guides/services/advanced In the script editor, select Resources > Advanced Google services.... In the dialog that appears, click the on/off switch next to the service you want to use. At the bottom of the dialog, click the link for the Google API Console. In the console, click into the filter box and type part of the name of the API (for example, "Calendar"), then click the name once you see it. On the next screen, click Enable API. Close the API Console and return to the script editor. Click OK in the dialog. The advanced service you enabled is now available in autocomplete.

Related