Google Sheets API returns "Invalid values[3][1]: struct_value {\n}\n" error

Question

I'm using the Google Sheets API v4 for Java to fill a spreadsheet with some values:

String spreadsheetID = ...;
Spreadsheets service = new Sheets.Builder(GoogleNetHttpTransport.newTrustedTransport(),
    JacksonFactory.getDefaultInstance(), credential).setApplicationName("Test").build().spreadsheets();

List<List<Object>> content = new ArrayList<>();
content.add(Arrays.asList("Client", "John Doe"));
content.add(Arrays.asList("Date", new Date()));
service.values().update(spreadsheetID, "Sheet1!C3:D4",
    new ValueRange().setValues(content)).setValueInputOption("RAW").execute();

but I get the following error when calling execute():

com.google.api.client.googleapis.json.GoogleJsonResponseException: 400 Bad Request
{
  "code" : 400,
  "errors" : [ {
    "domain" : "global",
    "message" : "Invalid values[3][1]: struct_value {\n}\n",
    "reason" : "badRequest"
  } ],
  "message" : "Invalid values[3][1]: struct_value {\n}\n",
  "status" : "INVALID_ARGUMENT"
}
    at com.google.api.client.googleapis.json.GoogleJsonResponseException.from(GoogleJsonResponseException.java:150)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:113)
    at com.google.api.client.googleapis.services.json.AbstractGoogleJsonClientRequest.newExceptionOnError(AbstractGoogleJsonClientRequest.java:40)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest$1.interceptResponse(AbstractGoogleClientRequest.java:321)
    at com.google.api.client.http.HttpRequest.execute(HttpRequest.java:1065)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:419)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.executeUnparsed(AbstractGoogleClientRequest.java:352)
    at com.google.api.client.googleapis.services.AbstractGoogleClientRequest.execute(AbstractGoogleClientRequest.java:469)

How can I solve this?

score 6 · Accepted Answer · answered May 23 '20 at 16:49

The setValues method of ValueRange takes a nested List of Object as argument, but that doesn't mean you can put all kinds of objects in that list. According to the documentation,

For input, supported value types are: bool, string, and double.

From personal experience, Integers and BigDecimals work as well, but for dates you need to convert them to the number of days since December 30th, 1899 and apply a date format as described e.g. here, or (if you don't care it's not stored as a day) as a String via e.g. SimpleDateFormat.

score 1 · Answer 2 · answered Jul 20 '22 at 11:46

The Google Sheets API has its own way of interpreting and accepting values. The other answers already presented some materials and links for your to learn more.

My answer is for someone who does not want to know more, and wants the value written in the sheet right now as a string.

This is a simple workaround.

Question code:

List<List<Object>> content = new ArrayList<>();
content.add(Arrays.asList("Client", "John Doe"));
content.add(Arrays.asList("Date", new Date()));      // problem is here
service.values().update(spreadsheetID, "Sheet1!C3:D4",
    new ValueRange().setValues(content)).setValueInputOption("RAW").execute();

The sheets API will not accept a Date Object. If you only want "yyyy-MM-dd" format try this:

List<List<Object>> content = new ArrayList<>();
content.add(Arrays.asList("Client", "John Doe"));
content.add(Arrays.asList("Date", LocalDate.now().toString));
service.values().update(spreadsheetID, "Sheet1!C3:D4",
    new ValueRange().setValues(content)).setValueInputOption("RAW").execute();

The "RAW" parameter can be "USER_ENTERED" now, if you wish.

This is the kind of result you will get:

Google Sheets API returns "Invalid values[3][1]: struct_value {\n}\n" error

2 Answers2