In Google sheets java api I am doing a BatchUpdateSpreadsheetRequest which is updating the text and color of the cells in a sheet, but it seems to be ignoring my range request telling it to updates the second sheet rather than the first.
I got it to work when I just set the text for the entire spreadsheet using a list of strings like below
service.spreadsheets().values().update(spreadsheetId, "Sheet2", body).execute();
But that does not handle changing the colors of the cells. Therefore I tried doing a batch update using lists of CellData objects, which works for the first sheet tab, but refuses to obey the range telling each request to work on the second sheet. It always just update Sheet1 instead of Sheet2.
//for every row, do an update request updating that row
List<Request> requests = new ArrayList<>();
List<String> ranges = new ArrayList<>();
int row=0;
while(row < values.size())
{
//add the request that will update the rows text and cell colors
requests.add(new Request()
.setUpdateCells(new UpdateCellsRequest()
.setStart(new GridCoordinate().setRowIndex(row).setColumnIndex(0))
.setRows(Arrays.asList(new RowData().setValues(values.get(row))))
.setFields("userEnteredValue,userEnteredFormat.backgroundColor")));
//add the range specifying that this update should be applied to the second sheet
ranges.add("Sheet2");
row++;
}
//Do tha batch update we just defined
BatchUpdateSpreadsheetRequest batchUpdateRequest = new BatchUpdateSpreadsheetRequest()
.setResponseRanges(ranges) //all ranges are set to "Sheet2" so why are the requests still updating "Sheet1"??
.setRequests(requests);
service.spreadsheets().batchUpdate(spreadsheetId, batchUpdateRequest)
.execute();
How do I get it to apply the updates to Sheet2?
