Advanced Sheets Service

The Advanced Sheets service lets you access the Sheets API using Apps 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 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. For more information, see How method signatures are determined .

To report issues and find other support, see the Sheets 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 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.

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, disjoint ranges in a sheet with one request. It is equivalent to the Write to multiple ranges recipe 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 specific size and tab color. It is equivalent to the Add 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 table recipe 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 
 ); 
  
 } 
 }