Examples with BatchUpdateValuesRequest com.google.api.services.sheets.v4.model.BatchUpdateValuesRequest used on opensource projects

Example 1 with BatchUpdateValuesRequest

use of com.google.api.services.sheets.v4.model.BatchUpdateValuesRequest in project tutorials by eugenp.

the class GoogleSheetsIntegrationTest method whenWriteSheet_thenReadSheetOk.

@Test
public void whenWriteSheet_thenReadSheetOk() throws IOException {
    ValueRange body = new ValueRange().setValues(Arrays.asList(Arrays.asList("Expenses January"), Arrays.asList("books", "30"), Arrays.asList("pens", "10"), Arrays.asList("Expenses February"), Arrays.asList("clothes", "20"), Arrays.asList("shoes", "5")));
    UpdateValuesResponse result = sheetsService.spreadsheets().values().update(SPREADSHEET_ID, "A1", body).setValueInputOption("RAW").execute();
    List<ValueRange> data = new ArrayList<>();
    data.add(new ValueRange().setRange("D1").setValues(Arrays.asList(Arrays.asList("January Total", "=B2+B3"))));
    data.add(new ValueRange().setRange("D4").setValues(Arrays.asList(Arrays.asList("February Total", "=B5+B6"))));
    BatchUpdateValuesRequest batchBody = new BatchUpdateValuesRequest().setValueInputOption("USER_ENTERED").setData(data);
    BatchUpdateValuesResponse batchResult = sheetsService.spreadsheets().values().batchUpdate(SPREADSHEET_ID, batchBody).execute();
    List<String> ranges = Arrays.asList("E1", "E4");
    BatchGetValuesResponse readResult = sheetsService.spreadsheets().values().batchGet(SPREADSHEET_ID).setRanges(ranges).execute();
    ValueRange januaryTotal = readResult.getValueRanges().get(0);
    assertThat(januaryTotal.getValues().get(0).get(0)).isEqualTo("40");
    ValueRange febTotal = readResult.getValueRanges().get(1);
    assertThat(febTotal.getValues().get(0).get(0)).isEqualTo("25");
    ValueRange appendBody = new ValueRange().setValues(Arrays.asList(Arrays.asList("Total", "=E1+E4")));
    AppendValuesResponse appendResult = sheetsService.spreadsheets().values().append(SPREADSHEET_ID, "A1", appendBody).setValueInputOption("USER_ENTERED").setInsertDataOption("INSERT_ROWS").setIncludeValuesInResponse(true).execute();
    ValueRange total = appendResult.getUpdates().getUpdatedData();
    assertThat(total.getValues().get(0).get(1)).isEqualTo("65");
}