Advanced Sheets Service
Page Summary
The Advanced Sheets service allows accessing the Sheets API using Apps Script to read, edit, format, and present data in Google Sheets.
This advanced service must be enabled before use and provides some features not available in the built-in Google Sheets API service.
The service uses the same objects, methods, and parameters as the public Sheets API.
Sample code is provided for common tasks like reading and writing values, adding a new sheet, and creating a pivot table.
The Advanced Sheets service lets you access theSheets API usingApps Script. Much like Apps Script'sbuilt-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.
This is an advanced service that must beenabled before use.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, seeHow method signatures are determined.
To report issues and find other support, see theSheets support guide.
Sample code
The sample code below usesversion 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.
/** * 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 */functionreadRange(spreadsheetId=yourspreadsheetId){try{constresponse=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 exceptionconsole.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.
/** * 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 */functionwriteToMultipleRanges(spreadsheetId=yourspreadsheetId){// Specify some values to write to the sheet.constcolumnAValues=[["Item","Wheel","Door","Engine"]];constrowValues=[["Cost","Stocked","Ship Date"],["$20.50","4","3/1/2016"],];constrequest={valueInputOption:"USER_ENTERED",data:[{range:"Sheet1!A1:A4",majorDimension:"COLUMNS",values:columnAValues,},{range:"Sheet1!B1:D2",majorDimension:"ROWS",values:rowValues,},],};try{constresponse=Sheets.Spreadsheets.Values.batchUpdate(request,spreadsheetId,);if(response){console.log(response);return;}console.log("response null");}catch(e){// TODO (developer) - Handle exceptionconsole.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.
/** * 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 */functionaddSheet(spreadsheetId=yourspreadsheetId){constrequests=[{addSheet:{properties:{title:"Deposits",gridProperties:{rowCount:20,columnCount:12,},tabColor:{red:1.0,green:0.3,blue:0.4,},},},},];try{constresponse=Sheets.Spreadsheets.batchUpdate({requests:requests},spreadsheetId,);console.log(`Created sheet with ID:${response.replies[0].addSheet.properties.sheetId}`,);}catch(e){// TODO (developer) - Handle exceptionconsole.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 theAdd a pivot tablerecipe sample.
/** * 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 */functionaddPivotTable(spreadsheetId=yourspreadsheetId,pivotSourceDataSheetId=yourpivotSourceDataSheetId,destinationSheetId=yourdestinationSheetId,){constrequests=[{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{constresponse=Sheets.Spreadsheets.batchUpdate({requests:requests},spreadsheetId,);// The Pivot table will appear anchored to cell A50 of the destination sheet.}catch(e){// TODO (developer) - Handle exceptionconsole.log("Failed with error %s",e.message);}}
Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-11 UTC.