CODEWITHSUNDEEP
javagoogle-sheetsgoogle-apigoogle-sheets-api
Peter Penzov
Peter Penzov

Reputation: 1728

Create sheet and update data with one request

I want to implement Google sheets api request with one api call.

I managed to implement this code:

List<Request> requests = new ArrayList<>();

        List<CellData> values = new ArrayList<>();

        values.add(new CellData()
            .setUserEnteredValue(new ExtendedValue()
                .setStringValue("Hello World!")));

        requests.add(new Request().setAddSheet(new AddSheetRequest()
            .setProperties(new SheetProperties()
                .setTitle("scstc")))
            .setUpdateCells(new UpdateCellsRequest()
                .setStart(new GridCoordinate()
                    .setSheetId(0)
                    .setRowIndex(0)
                    .setColumnIndex(0))
                .setRows(Arrays.asList(
                    new RowData().setValues(values)))
                .setFields("userEnteredValue,userEnteredFormat.backgroundColor"))
        );

        BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);

        BatchUpdateSpreadsheetResponse response = service.spreadsheets().batchUpdate(spreadsheetId, body).execute();

But I get error:

400 Bad Request
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "message" : "Invalid value at 'requests[0]' (oneof), oneof field 'kind' is already set. Cannot set 'updateCells'",
    "reason" : "badRequest"
  } ],
  "message" : "Invalid value at 'requests[0]' (oneof), oneof field 'kind' is already set. Cannot set 'updateCells'",
  "status" : "INVALID_ARGUMENT"
}
    at com.google.sheet.impl.GoogleSheetBasicTest1_____1.hello(GoogleSheetBasicTest1_____1.java:133)

Do you how how I can fix this issue?

Upvotes: 5

Views: 2474

Answers (2)

alcapone1919
alcapone1919

Reputation: 31

@Sam is correct, however if you are using the JSON representation make sure that your formatting is set correctly in the dictionaries you are making. I found the following formating helpfull, found in the Google Devs' Formatting cells with the Google Sheets API blogpost:

reqs = {'requests': [
    # frozen row 1, request #1
    {'updateSheetProperties': {
        'properties': {'gridProperties': {'frozenRowCount': 1}},
        'fields': 'gridProperties.frozenRowCount',
    }},
    # embolden row 1, request #2
    {'repeatCell': {
        'range': {'endRowIndex': 1},
        'cell': {'userEnteredFormat': {'textFormat': {'bold': True}}},
        'fields': 'userEnteredFormat.textFormat.bold',
    }},
]}

*I am new to adding information to this site. Sorry if this is not he best way to add the information but I just want to help out. I had this problem while using python instead of java and found that it was a simple error of were the brackets where.

Upvotes: 3

Sam Berlin
Sam Berlin

Reputation: 3773

Each Request object is intended to have just a single value set within it. You are setting two values:

requests.add(new Request()
            .setAddSheet(...)
            .setUpdateCells(...));

Instead of doing the above, you need to use two request objects:

requests.add(new Request().setAddSheet(...));
requests.add(new Request().setUpdateCells(...));

Upvotes: 6

Related Questions