The Advanced Sheets service lets you access the Sheets API usingApps Script. Much like Apps Script's built-in Google Sheets API 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 thisadvanced service provides a few extra features.
Reference
For detailed information on this service, see thereference documentation for the Sheets API.Like all advanced services in Apps Script, the advanced Sheets service uses thesame objects, methods, and parameters as the public API. For more information, see How method signatures are determined.
To report issues and find other support, see theSheets support guide.
Sample code
The sample code below uses version 4 of the API;this is the only version of the Sheets API currently available as anadvanced service in Apps Script.
Read values from a range
The following example demonstrates how to read data values from a specifiedrange in a sheet with the Sheets advanced service. It is equivalent to theRead a single rangerecipe sample.
advanced/sheets.gs
/** * Read a range (A1:D5) of data values. Logs the values. * @param {string} spreadsheetId The spreadsheet ID to read from. * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get */function readRange(spreadsheetId = yourspreadsheetId) { try { const response = Sheets.Spreadsheets.Values.get(spreadsheetId, 'Sheet1!A1:D5'); if (response.values) { console.log(response.values); return; } console.log('Failed to get range of values from spreadsheet'); } catch (e) { // TODO (developer) - Handle exception console.log('Failed with error %s', e.message); }}
Write values to multiple ranges
The following example demonstrates how to write data to different, disjointranges in a sheet with one request. It is equivalent to theWrite to multiple rangesrecipe sample.
advanced/sheets.gs
/** * Write to multiple, disjoint data ranges. * @param {string} spreadsheetId The spreadsheet ID to write to. * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate */function writeToMultipleRanges(spreadsheetId = yourspreadsheetId) { // Specify some values to write to the sheet. const columnAValues = [ ['Item', 'Wheel', 'Door', 'Engine'] ]; const rowValues = [ ['Cost', 'Stocked', 'Ship Date'], ['$20.50', '4', '3/1/2016'] ]; const request = { 'valueInputOption': 'USER_ENTERED', 'data': [ { 'range': 'Sheet1!A1:A4', 'majorDimension': 'COLUMNS', 'values': columnAValues }, { 'range': 'Sheet1!B1:D2', 'majorDimension': 'ROWS', 'values': rowValues } ] }; try { const response = Sheets.Spreadsheets.Values.batchUpdate(request, spreadsheetId); if (response) { console.log(response); return; } console.log('response null'); } catch (e) { // TODO (developer) - Handle exception console.log('Failed with error %s', e.message); }}
Add a new sheet
The following example demonstrates how to create a new sheet with specificsize and tab color. It is equivalent to theAdd a sheet recipe sample.
advanced/sheets.gs
/** * Add a new sheet with some properties. * @param {string} spreadsheetId The spreadsheet ID. * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate */function addSheet(spreadsheetId = yourspreadsheetId) { const requests = [{ 'addSheet': { 'properties': { 'title': 'Deposits', 'gridProperties': { 'rowCount': 20, 'columnCount': 12 }, 'tabColor': { 'red': 1.0, 'green': 0.3, 'blue': 0.4 } } } }]; try { const response = Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId); console.log('Created sheet with ID: ' + response.replies[0].addSheet.properties.sheetId); } catch (e) { // TODO (developer) - Handle exception console.log('Failed with error %s', e.message); }}
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 tablerecipe sample.
advanced/sheets.gs
/** * Add a pivot table. * @param {string} spreadsheetId The spreadsheet ID to add the pivot table to. * @param {string} pivotSourceDataSheetId The sheet ID to get the data from. * @param {string} destinationSheetId The sheet ID to add the pivot table to. * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate */function addPivotTable( spreadsheetId = yourspreadsheetId, pivotSourceDataSheetId= yourpivotSourceDataSheetId, destinationSheetId= yourdestinationSheetId) { const 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' } }]; try { const response = Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId); // The Pivot table will appear anchored to cell A50 of the destination sheet. } catch (e) { // TODO (developer) - Handle exception console.log('Failed with error %s', e.message); }}
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2024-08-29 UTC.
[{ "type": "thumb-down", "id": "missingTheInformationINeed", "label":"Missing the information I need" },{ "type": "thumb-down", "id": "tooComplicatedTooManySteps", "label":"Too complicated / too many steps" },{ "type": "thumb-down", "id": "outOfDate", "label":"Out of date" },{ "type": "thumb-down", "id": "samplesCodeIssue", "label":"Samples / code issue" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }] [{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }]
