@Test public void testPivotTable() throws IOException { String spreadsheetId = this.createTestSpreadsheet(); BatchUpdateSpreadsheetResponse result = this.snippets.pivotTables(spreadsheetId); assertNotNull(result); }
public BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId) throws IOException { Sheets service = this.service; List<Request> sheetsRequests = new ArrayList<>(); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest() .setRequests(sheetsRequests); BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets() .batchUpdate(spreadsheetId, createSheetsBody).execute(); int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties() .getSheetId(); int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties() .getSheetId(); PivotTable pivotTable = new PivotTable() .setSource( new GridRange() .setSheetId(sourceSheetId) .setStartRowIndex(0) .setStartColumnIndex(0) .setEndRowIndex(20) .setEndColumnIndex(7) ) .setRows(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(1) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setColumns(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(4) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setValues(Collections.singletonList( new PivotValue() .setSummarizeFunction("COUNTA") .setSourceColumnOffset(4) )); List<Request> requests = Lists.newArrayList(); Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest() .setFields("*") .setRows(Collections.singletonList( new RowData().setValues( Collections.singletonList( new CellData().setPivotTable(pivotTable)) ) )) .setStart(new GridCoordinate() .setSheetId(targetSheetId) .setRowIndex(0) .setColumnIndex(0) )); requests.add(updateCellsRequest); BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, updateCellsBody).execute(); return result; }
SpreadsheetSnippets { public BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId) throws IOException { Sheets service = this.service; List<Request> sheetsRequests = new ArrayList<>(); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest() .setRequests(sheetsRequests); BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets() .batchUpdate(spreadsheetId, createSheetsBody).execute(); int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties() .getSheetId(); int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties() .getSheetId(); PivotTable pivotTable = new PivotTable() .setSource( new GridRange() .setSheetId(sourceSheetId) .setStartRowIndex(0) .setStartColumnIndex(0) .setEndRowIndex(20) .setEndColumnIndex(7) ) .setRows(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(1) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setColumns(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(4) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setValues(Collections.singletonList( new PivotValue() .setSummarizeFunction("COUNTA") .setSourceColumnOffset(4) )); List<Request> requests = Lists.newArrayList(); Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest() .setFields("*") .setRows(Collections.singletonList( new RowData().setValues( Collections.singletonList( new CellData().setPivotTable(pivotTable)) ) )) .setStart(new GridCoordinate() .setSheetId(targetSheetId) .setRowIndex(0) .setColumnIndex(0) )); requests.add(updateCellsRequest); BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, updateCellsBody).execute(); return result; } }
SpreadsheetSnippets { public BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId) throws IOException { Sheets service = this.service; List<Request> sheetsRequests = new ArrayList<>(); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest() .setRequests(sheetsRequests); BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets() .batchUpdate(spreadsheetId, createSheetsBody).execute(); int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties() .getSheetId(); int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties() .getSheetId(); PivotTable pivotTable = new PivotTable() .setSource( new GridRange() .setSheetId(sourceSheetId) .setStartRowIndex(0) .setStartColumnIndex(0) .setEndRowIndex(20) .setEndColumnIndex(7) ) .setRows(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(1) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setColumns(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(4) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setValues(Collections.singletonList( new PivotValue() .setSummarizeFunction("COUNTA") .setSourceColumnOffset(4) )); List<Request> requests = Lists.newArrayList(); Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest() .setFields("*") .setRows(Collections.singletonList( new RowData().setValues( Collections.singletonList( new CellData().setPivotTable(pivotTable)) ) )) .setStart(new GridCoordinate() .setSheetId(targetSheetId) .setRowIndex(0) .setColumnIndex(0) )); requests.add(updateCellsRequest); BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, updateCellsBody).execute(); return result; } SpreadsheetSnippets(Sheets service); }
SpreadsheetSnippets { public BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId) throws IOException { Sheets service = this.service; List<Request> sheetsRequests = new ArrayList<>(); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest() .setRequests(sheetsRequests); BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets() .batchUpdate(spreadsheetId, createSheetsBody).execute(); int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties() .getSheetId(); int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties() .getSheetId(); PivotTable pivotTable = new PivotTable() .setSource( new GridRange() .setSheetId(sourceSheetId) .setStartRowIndex(0) .setStartColumnIndex(0) .setEndRowIndex(20) .setEndColumnIndex(7) ) .setRows(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(1) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setColumns(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(4) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setValues(Collections.singletonList( new PivotValue() .setSummarizeFunction("COUNTA") .setSourceColumnOffset(4) )); List<Request> requests = Lists.newArrayList(); Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest() .setFields("*") .setRows(Collections.singletonList( new RowData().setValues( Collections.singletonList( new CellData().setPivotTable(pivotTable)) ) )) .setStart(new GridCoordinate() .setSheetId(targetSheetId) .setRowIndex(0) .setColumnIndex(0) )); requests.add(updateCellsRequest); BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, updateCellsBody).execute(); return result; } SpreadsheetSnippets(Sheets service); String create(String title); BatchUpdateSpreadsheetResponse batchUpdate(String spreadsheetId, String title,
                                                      String find, String replacement); ValueRange getValues(String spreadsheetId, String range); BatchGetValuesResponse batchGetValues(String spreadsheetId, List<String> _ranges); UpdateValuesResponse updateValues(String spreadsheetId, String range,
                                             String valueInputOption, List<List<Object>> _values); BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId, String range,
                                                       String valueInputOption,
                                                       List<List<Object>> _values); AppendValuesResponse appendValues(String spreadsheetId, String range,
                                             String valueInputOption, List<List<Object>> _values); BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId); BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId); }
SpreadsheetSnippets { public BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId) throws IOException { Sheets service = this.service; List<Request> sheetsRequests = new ArrayList<>(); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); sheetsRequests.add(new Request().setAddSheet(new AddSheetRequest())); BatchUpdateSpreadsheetRequest createSheetsBody = new BatchUpdateSpreadsheetRequest() .setRequests(sheetsRequests); BatchUpdateSpreadsheetResponse createSheetsResponse = service.spreadsheets() .batchUpdate(spreadsheetId, createSheetsBody).execute(); int sourceSheetId = createSheetsResponse.getReplies().get(0).getAddSheet().getProperties() .getSheetId(); int targetSheetId = createSheetsResponse.getReplies().get(1).getAddSheet().getProperties() .getSheetId(); PivotTable pivotTable = new PivotTable() .setSource( new GridRange() .setSheetId(sourceSheetId) .setStartRowIndex(0) .setStartColumnIndex(0) .setEndRowIndex(20) .setEndColumnIndex(7) ) .setRows(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(1) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setColumns(Collections.singletonList( new PivotGroup() .setSourceColumnOffset(4) .setShowTotals(true) .setSortOrder("ASCENDING") )) .setValues(Collections.singletonList( new PivotValue() .setSummarizeFunction("COUNTA") .setSourceColumnOffset(4) )); List<Request> requests = Lists.newArrayList(); Request updateCellsRequest = new Request().setUpdateCells(new UpdateCellsRequest() .setFields("*") .setRows(Collections.singletonList( new RowData().setValues( Collections.singletonList( new CellData().setPivotTable(pivotTable)) ) )) .setStart(new GridCoordinate() .setSheetId(targetSheetId) .setRowIndex(0) .setColumnIndex(0) )); requests.add(updateCellsRequest); BatchUpdateSpreadsheetRequest updateCellsBody = new BatchUpdateSpreadsheetRequest() .setRequests(requests); BatchUpdateSpreadsheetResponse result = service.spreadsheets() .batchUpdate(spreadsheetId, updateCellsBody).execute(); return result; } SpreadsheetSnippets(Sheets service); String create(String title); BatchUpdateSpreadsheetResponse batchUpdate(String spreadsheetId, String title,
                                                      String find, String replacement); ValueRange getValues(String spreadsheetId, String range); BatchGetValuesResponse batchGetValues(String spreadsheetId, List<String> _ranges); UpdateValuesResponse updateValues(String spreadsheetId, String range,
                                             String valueInputOption, List<List<Object>> _values); BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId, String range,
                                                       String valueInputOption,
                                                       List<List<Object>> _values); AppendValuesResponse appendValues(String spreadsheetId, String range,
                                             String valueInputOption, List<List<Object>> _values); BatchUpdateSpreadsheetResponse pivotTables(String spreadsheetId); BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId);  }