Pivot tables

/**
 * Create pivot table
 * @param {string} spreadsheetId spreadsheet ID
 * @returns {*} pivot table's spreadsheet
 */
Snippets.prototype.pivotTable=(spreadsheetId)=>{
try{
constspreadsheet=SpreadsheetApp.openById(spreadsheetId);
// Create two sheets for our pivot table, assume we have one.
letsheet=spreadsheet.getSheets()[0];
sheet.copyTo(spreadsheet);
constsourceSheetId=spreadsheet.getSheets()[0].getSheetId();
consttargetSheetId=spreadsheet.getSheets()[1].getSheetId();
// Create pivot table
constpivotTable=Sheets.newPivotTable();
letgridRange=Sheets.newGridRange();
gridRange.sheetId=sourceSheetId;
gridRange.startRowIndex=0;
gridRange.startColumnIndex=0;
gridRange.endRowIndex=20;
gridRange.endColumnIndex=7;
pivotTable.source=gridRange;
letpivotRows=Sheets.newPivotGroup();
pivotRows.sourceColumnOffset=1;
pivotRows.showTotals=true;
pivotRows.sortOrder='ASCENDING';
pivotTable.rows=pivotRows;
letpivotColumns=Sheets.newPivotGroup();
pivotColumns.sourceColumnOffset=4;
pivotColumns.sortOrder='ASCENDING';
pivotColumns.showTotals=true;
pivotTable.columns=pivotColumns;
letpivotValue=Sheets.newPivotValue();
pivotValue.summarizeFunction='COUNTA';
pivotValue.sourceColumnOffset=4;
pivotTable.values=[pivotValue];
// Create other metadata for the updateCellsRequest
letcellData=Sheets.newCellData();
cellData.pivotTable=pivotTable;
letrows=Sheets.newRowData();
rows.values=cellData;
letstart=Sheets.newGridCoordinate();
start.sheetId=targetSheetId;
start.rowIndex=0;
start.columnIndex=0;
letupdateCellsRequest=Sheets.newUpdateCellsRequest();
updateCellsRequest.rows=rows;
updateCellsRequest.start=start;
updateCellsRequest.fields='pivotTable';
// Batch update our spreadsheet
letbatchUpdate=Sheets.newBatchUpdateSpreadsheetRequest();
letupdateCellsRawRequest=Sheets.newRequest();
updateCellsRawRequest.updateCells=updateCellsRequest;
batchUpdate.requests=[updateCellsRawRequest];
constresponse=Sheets.Spreadsheets.batchUpdate(batchUpdate,
spreadsheetId);
returnresponse;
}catch(err){
// TODO (developer) - Handle exception
console.log('Failed with error %s',err.message);
}
};

importcom.google.api.client.googleapis.json.GoogleJsonError;
importcom.google.api.client.googleapis.json.GoogleJsonResponseException;
importcom.google.api.client.http.HttpRequestInitializer;
importcom.google.api.client.http.javanet.NetHttpTransport;
importcom.google.api.client.json.gson.GsonFactory;
importcom.google.api.services.sheets.v4.Sheets;
importcom.google.api.services.sheets.v4.SheetsScopes;
importcom.google.api.services.sheets.v4.model.AddSheetRequest;
importcom.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
importcom.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
importcom.google.api.services.sheets.v4.model.CellData;
importcom.google.api.services.sheets.v4.model.GridCoordinate;
importcom.google.api.services.sheets.v4.model.GridRange;
importcom.google.api.services.sheets.v4.model.PivotGroup;
importcom.google.api.services.sheets.v4.model.PivotTable;
importcom.google.api.services.sheets.v4.model.PivotValue;
importcom.google.api.services.sheets.v4.model.Request;
importcom.google.api.services.sheets.v4.model.RowData;
importcom.google.api.services.sheets.v4.model.UpdateCellsRequest;
importcom.google.auth.http.HttpCredentialsAdapter;
importcom.google.auth.oauth2.GoogleCredentials;
importcom.google.common.collect.Lists;
importjava.io.IOException;
importjava.util.ArrayList;
importjava.util.Collections;
importjava.util.List;
/* Class to demonstrate the use of Spreadsheet Create Pivot Tables API */
publicclass PivotTables{
/**
 * Create pivot table.
 *
 * @param spreadsheetId - Id of the spreadsheet.
 * @return pivot table's spreadsheet
 * @throws IOException - if credentials file not found.
 */
publicstaticBatchUpdateSpreadsheetResponsepivotTables(StringspreadsheetId)
throwsIOException{
/* Load pre-authorized user credentials from the environment.
 TODO(developer) - See https://developers.google.com/identity for
 guides on implementing OAuth2 for your application. */
GoogleCredentialscredentials=GoogleCredentials.getApplicationDefault()
.createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
HttpRequestInitializerrequestInitializer=newHttpCredentialsAdapter(
credentials);
// Create the sheets API client
Sheetsservice=newSheets.Builder(newNetHttpTransport(),
GsonFactory.getDefaultInstance(),
requestInitializer)
.setApplicationName("Sheets samples")
.build();
// Create two sheets for our pivot table.
List<Request>sheetsRequests=newArrayList<>();
BatchUpdateSpreadsheetResponseresult=null;
try{
sheetsRequests.add(newRequest().setAddSheet(newAddSheetRequest()));
sheetsRequests.add(newRequest().setAddSheet(newAddSheetRequest()));
BatchUpdateSpreadsheetRequestcreateSheetsBody=newBatchUpdateSpreadsheetRequest()
.setRequests(sheetsRequests);
BatchUpdateSpreadsheetResponsecreateSheetsResponse=service.spreadsheets()
.batchUpdate(spreadsheetId,createSheetsBody).execute();
intsourceSheetId=createSheetsResponse.getReplies().get(0).getAddSheet().getProperties()
.getSheetId();
inttargetSheetId=createSheetsResponse.getReplies().get(1).getAddSheet().getProperties()
.getSheetId();
PivotTablepivotTable=newPivotTable()
.setSource(
newGridRange()
.setSheetId(sourceSheetId)
.setStartRowIndex(0)
.setStartColumnIndex(0)
.setEndRowIndex(20)
.setEndColumnIndex(7)
)
.setRows(Collections.singletonList(
newPivotGroup()
.setSourceColumnOffset(1)
.setShowTotals(true)
.setSortOrder("ASCENDING")
))
.setColumns(Collections.singletonList(
newPivotGroup()
.setSourceColumnOffset(4)
.setShowTotals(true)
.setSortOrder("ASCENDING")
))
.setValues(Collections.singletonList(
newPivotValue()
.setSummarizeFunction("COUNTA")
.setSourceColumnOffset(4)
));
List<Request>requests=Lists.newArrayList();
RequestupdateCellsRequest=newRequest().setUpdateCells(newUpdateCellsRequest()
.setFields("*")
.setRows(Collections.singletonList(
newRowData().setValues(
Collections.singletonList(
newCellData().setPivotTable(pivotTable))
)
))
.setStart(newGridCoordinate()
.setSheetId(targetSheetId)
.setRowIndex(0)
.setColumnIndex(0)
));
requests.add(updateCellsRequest);
BatchUpdateSpreadsheetRequestupdateCellsBody=newBatchUpdateSpreadsheetRequest()
.setRequests(requests);
result=service.spreadsheets().batchUpdate(spreadsheetId,updateCellsBody).execute();
}catch(GoogleJsonResponseExceptione){
// TODO(developer) - handle error appropriately
GoogleJsonErrorerror=e.getDetails();
if(error.getCode()==404){
System.out.printf("Spreadsheet not found with id '%s'.\n",spreadsheetId);
}else{
throwe;
}
}
returnresult;
}
}

functionpivotTable(spreadsheetId,callback){
// Create two sheets for our pivot table
constrequests=[{
addSheet:{},
},{
addSheet:{},
}];
constbatchUpdateRequest={requests:requests};
try{
gapi.client.sheets.spreadsheets.batchUpdate({
spreadsheetId:spreadsheetId,
resource:batchUpdateRequest,
}).then((response)=>{
constsourceSheetId=response.result.replies[0].addSheet.properties.sheetId;
consttargetSheetId=response.result.replies[1].addSheet.properties.sheetId;
constrequests=[{
updateCells:{
rows:{
values:[{
pivotTable:{
source:{
sheetId:sourceSheetId,
startRowIndex:0,
startColumnIndex:0,
endRowIndex:20,
endColumnIndex:7,
},
rows:[{
sourceColumnOffset:1,
showTotals:true,
sortOrder:'ASCENDING',
}],
columns:[{
sourceColumnOffset:4,
sortOrder:'ASCENDING',
showTotals:true,
}],
values:[{
summarizeFunction:'COUNTA',
sourceColumnOffset:4,
}],
valueLayout:'HORIZONTAL',
},
},
],
},
start:{
sheetId:targetSheetId,
rowIndex:0,
columnIndex:0,
},
fields:'pivotTable',
},
}];
constbody={
requests,
};
gapi.client.sheets.spreadsheets.batchUpdate({
spreadsheetId:spreadsheetId,
resource:body,
}).then((response)=>{
if(callback)callback(response);
});
});
}catch(err){
document.getElementById('content').innerText=err.message;
return;
}
}

import{GoogleAuth}from'google-auth-library';
import{google}from'googleapis';
/**
 * Creates a pivot table in a spreadsheet.
 * @param {string} spreadsheetId The ID of the spreadsheet.
 * @return {Promise<object>} The response from the batch update.
 */
asyncfunctionpivotTable(spreadsheetId){
// Authenticate with Google and get an authorized client.
constauth=newGoogleAuth({
scopes:'https://www.googleapis.com/auth/spreadsheets',
});
constservice=google.sheets({version:'v4',auth});
// Create two new sheets for the pivot table.
// One for the source data and one for the pivot table itself.
letrequests=[
{
addSheet:{},
},
{
addSheet:{},
},
];
letresource={requests};
letresponse=awaitservice.spreadsheets.batchUpdate({
spreadsheetId,
resource,
});
// Get the IDs of the newly created sheets.
constsourceSheetId=response.data.replies[0].addSheet.properties.sheetId;
consttargetSheetId=response.data.replies[1].addSheet.properties.sheetId;
// Add a pivot table to the new sheet.
requests=[
{
updateCells:{
rows:{
values:[
{
pivotTable:{
// The source data for the pivot table.
source:{
sheetId:sourceSheetId,
startRowIndex:0,
startColumnIndex:0,
endRowIndex:20,
endColumnIndex:7,
},
// The rows of the pivot table.
rows:[
{
sourceColumnOffset:1,
showTotals:true,
sortOrder:'ASCENDING',
},
],
// The columns of the pivot table.
columns:[
{
sourceColumnOffset:4,
sortOrder:'ASCENDING',
showTotals:true,
},
],
// The values to display in the pivot table.
values:[
{
summarizeFunction:'COUNTA',
sourceColumnOffset:4,
},
],
valueLayout:'HORIZONTAL',
},
},
],
},
// The location to place the pivot table.
start:{
sheetId:targetSheetId,
rowIndex:0,
columnIndex:0,
},
fields:'pivotTable',
},
},
];
resource={
requests,
};
// Send the batch update request to create the pivot table.
response=service.spreadsheets.batchUpdate({
spreadsheetId,
resource,
});
returnresponse;
}

<?php
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
function pivotTables($spreadsheetId)
 {
 /* Load pre-authorized user credentials from the environment.
 TODO(developer) - See https://developers.google.com/identity for
 guides on implementing OAuth2 for your application. */
 $client = new Google\Client();
 $client->useApplicationDefaultCredentials();
 $client->addScope(Google\Service\Drive::DRIVE);
 $service = new Google_Service_Sheets($client);
 try{
 $requests = [
 new Google_Service_Sheets_Request([
 'addSheet' => [
 'properties' => [
 'title' => 'Sheet 1'
 ]
 ]
 ]),
 new Google_Service_Sheets_Request([
 'addSheet' => [
 'properties' => [
 'title' => 'Sheet 2'
 ]
 ]
 ])
 ];
 // Create two sheets for our pivot table
 $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
 'requests' => $requests
 ]);
 $batchUpdateResponse = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
 $sourceSheetId = $batchUpdateResponse->replies[0]->addSheet->properties->sheetId;
 $targetSheetId = $batchUpdateResponse->replies[1]->addSheet->properties->sheetId;
 $requests = [
 'updateCells' => [
 'rows' => [
 'values' => [
 [
 'pivotTable' => [
 'source' => [
 'sheetId' => $sourceSheetId,
 'startRowIndex' => 0,
 'startColumnIndex' => 0,
 'endRowIndex' => 20,
 'endColumnIndex' => 7
 ],
 'rows' => [
 [
 'sourceColumnOffset' => 1,
 'showTotals' => true,
 'sortOrder' => 'ASCENDING',
 ],
 ],
 'columns' => [
 [
 'sourceColumnOffset' => 4,
 'sortOrder' => 'ASCENDING',
 'showTotals' => true,
 ]
 ],
 'values' => [
 [
 'summarizeFunction' => 'COUNTA',
 'sourceColumnOffset' => 4
 ]
 ],
 'valueLayout' => 'HORIZONTAL'
 ]
 ]
 ]
 ],
 'start' => [
 'sheetId' => $targetSheetId,
 'rowIndex' => 0,
 'columnIndex' => 0
 ],
 'fields' => 'pivotTable'
 ]
 ];
 return $batchUpdateResponse;
 }
 catch(Exception $e) {
 // TODO(developer) - handle error appropriately
 echo 'Message: ' .$e->getMessage();
 }
 }

importgoogle.auth
fromgoogleapiclient.discoveryimport build
fromgoogleapiclient.errorsimport HttpError
defpivot_tables(spreadsheet_id):
"""
 Creates the batch_update the user has access to.
 Load pre-authorized user credentials from the environment.
 TODO(developer) - See https://developers.google.com/identity
 for guides on implementing OAuth2 for the application.
 """
 creds, _ = google.auth.default()
 # pylint: disable=maybe-no-member
 try:
 service = build("sheets", "v4", credentials=creds)
 # Create two sheets for our pivot table.
 body = {"requests": [{"addSheet": {}}, {"addSheet": {}}]}
 batch_update_response = (
 service.spreadsheets()
 .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
 .execute()
 )
 source_sheet_id = (
 batch_update_response.get("replies")[0]
 .get("addSheet")
 .get("properties")
 .get("sheetId")
 )
 target_sheet_id = (
 batch_update_response.get("replies")[1]
 .get("addSheet")
 .get("properties")
 .get("sheetId")
 )
 requests = []
 requests.append(
 {
 "updateCells": {
 "rows": {
 "values": [
 {
 "pivotTable": {
 "source": {
 "sheetId": source_sheet_id,
 "startRowIndex": 0,
 "startColumnIndex": 0,
 "endRowIndex": 20,
 "endColumnIndex": 7,
 },
 "rows": [
 {
 "sourceColumnOffset": 1,
 "showTotals": True,
 "sortOrder": "ASCENDING",
 },
 ],
 "columns": [{
 "sourceColumnOffset": 4,
 "sortOrder": "ASCENDING",
 "showTotals": True,
 }],
 "values": [{
 "summarizeFunction": "COUNTA",
 "sourceColumnOffset": 4,
 }],
 "valueLayout": "HORIZONTAL",
 }
 }
 ]
 },
 "start": {
 "sheetId": target_sheet_id,
 "rowIndex": 0,
 "columnIndex": 0,
 },
 "fields": "pivotTable",
 }
 }
 )
 body = {"requests": requests}
 response = (
 service.spreadsheets()
 .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
 .execute()
 )
 return response
 except HttpError as error:
 print(f"An error occurred: {error}")
 return error
if __name__ == "__main__":
 # Pass: spreadsheet_id
 pivot_tables("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")

requests=[{
update_cells:{
rows:{
values:[
{
pivot_table:{
source:{
sheet_id:source_sheet_id,
start_row_index:0,
start_column_index:0,
end_row_index:20,
end_column_index:7
},
rows:[
{
source_column_offset:1,
show_totals:true,
sort_order:'ASCENDING',
},
],
columns:[
{
source_column_offset:4,
sort_order:'ASCENDING',
show_totals:true,
}
],
values:[
{
summarize_function:'COUNTA',
source_column_offset:4
}
],
value_layout:'HORIZONTAL'
}
}
]
},
start:{
sheet_id:target_sheet_id,
row_index:0,
column_index:0
},
fields:'pivotTable'
}
}]
result=service.batch_update_spreadsheet(spreadsheet_id,body,{})