Advanced Sheets Service

The Advanced Sheets service lets you access the Sheets API using Apps Script. Much like Apps Script's built-in Sheets service, this API allows scripts to read, edit, format and present data in Google Sheets. In most cases, the built-in service is easier to use, but this advanced service provides a few extra features.

Reference

For detailed information on this service, see the reference documentation for the Sheets API. Like all advanced services in Apps Script, the advanced Sheets service uses the same objects, methods, and parameters as the public API.

Sample code

The sample code below uses version 4 of the API; this is the only version of the Sheets API currently available as an advanced service in Apps Script.

Read values from a range

The following example demonstrates how to read data values from a specified range in a sheet with the Sheets advanced service. It is equivalent to the Read a single range recipe sample.

function readRange(spreadsheetId) {
  var response = Sheets.Spreadsheets.Values.get(spreadsheetId, "Sheet1!A1:D5");
  Logger.log(response.values);
}

Write values to multiple ranges

The following example demonstrates how to write data to different, disjoint ranges in a sheet with one request. It is equivalent to the Write to multiple ranges recipe sample.

function writeToMultipleRanges(spreadsheetId) {
  // Specify some values to write to the sheet.
  var columnAValues = [
    ["Item", "Wheel", "Door", "Engine"]
  ];
  var rowValues = [
    ["Cost", "Stocked", "Ship Date"],
    ["$20.50", "4", "3/1/2016"]
  ];

  var request = {
    "valueInputOption": "USER_ENTERED",
    "data": [
      {
        "range": "Sheet1!A1:A4",
        "majorDimension": "COLUMNS",
        "values": columnAValues
      },
      {
        "range": "Sheet1!B1:D2",
        "majorDimension": "ROWS",
        "values": rowValues
      }
    ]
  };

  var response = Sheets.Spreadsheets.Values.batchUpdate(request, spreadsheetId);
  Logger.log(response);
}

Add a new sheet

The following example demonstrates how to create a new sheet with specific size and tab color. It is equivalent to the Add a sheet recipe sample.

function addSheet(spreadsheetId) {
  var requests = [{
    "addSheet": {
      "properties": {
        "title": "Deposits",
        "gridProperties": {
          "rowCount": 20,
          "columnCount": 12
        },
        "tabColor": {
          "red": 1.0,
          "green": 0.3,
          "blue": 0.4
        }
      }
    }
  }];

  var response =
      Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId);
  Logger.log("Created sheet with ID: " +
      response.replies[0].addSheet.properties.sheetId);
}

Create a pivot table

The following example demonstrates how to create a pivot table from source data. It is equivalent to the Add a pivot table recipe sample.

function addPivotTable(
    spreadsheetId, pivotSourceDataSheetId, destinationSheetId) {
  var requests = [{
    "updateCells": {
      "rows": {
        "values": [
          {
            "pivotTable": {
              "source": {
                "sheetId": pivotSourceDataSheetId,
                "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": destinationSheetId,
        "rowIndex": 49,
        "columnIndex": 0
      },
      "fields": "pivotTable"
    }
  }];

  var response =
      Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId);
  // The Pivot table will appear anchored to cell A50 of the destination sheet.
}

Enviar comentarios sobre…