GSpread APIException: "Invalid value at 'data[0].values[0]'"

Question

I'm trying to use gspread API, i used it before with arrays with no issue, but now when i try to use a Numpy array, i get the "Invalid value at 'data[0].values[0]'" API exception. In my snippet, maturities_1 is a numpy array consisting of floats

sh = gc.open_by_key('API_KEY_HERE')
tempWorksheet = sh.worksheet(SomeSheetName)

strengths_1 = np.round(maturities_1, 3).astype(float).tolist()

tempWorksheet.batch_update([{
      'range': 'D3:D'+str(len(strengths_1)+2),
      'values': strengths_1,
    }])

and then, it throws out this exception.

Traceback (most recent call last):
  File "/home/pekabex/termopara/server.py", line 1131, in <module>
    calculateStrength(args)
  File "/home/pekabex/termopara/server.py", line 808, in calculateStrength
    tempWorksheet.batch_update([{
  File "/usr/local/lib/python3.9/dist-packages/gspread/utils.py", line 705, in wrapper
    return f(*args, **kwargs)
  File "/usr/local/lib/python3.9/dist-packages/gspread/worksheet.py", line 1205, in batch_update
    response = self.spreadsheet.values_batch_update(body=body)
  File "/usr/local/lib/python3.9/dist-packages/gspread/spreadsheet.py", line 220, in values_batch_update
    r = self.client.request("post", url, params=params, json=body)
  File "/usr/local/lib/python3.9/dist-packages/gspread/client.py", line 93, in request
    raise APIError(response)
gspread.exceptions.APIError: {'code': 400, 'message': "Invalid value at 'data[0].values[0]' (type.googleapis.com/google.protobuf.ListValue), 0.769\nInvalid value at 'data[0].values[1]' (type.googleapis.com/google.protobuf.ListValue), 1.748\nInvalid value at 'data[0].values[2]' (type.googleapis.com/google.protobuf.ListValue), 1.758\nInvalid value at 'data[0].values[3]' (type.googleapis.com/google.protobuf.ListValue), 1.806\nInvalid value at 'data[0].values[4]' (type.googleapis.com/google.protobuf.ListValue), 1.806\nInvalid value at 'data[0].values[5]' (type.googleapis.com/google.protobuf.ListValue), 1.873\nInvalid value at 'data[0].values[6]' (type.googleapis.com/google.protobuf.ListValue), 1.921\nInvalid value at 'data[0].values[7]' (type.googleapis.com/google.protobuf.ListValue), 1.969\nInvalid value at 'data[0].values[8]' (type.googleapis.com/google.protobuf.ListValue), 2.007\nInvalid value at 'data[0].values[9]' (type.googleapis.com/google.protobuf.ListValue), 2.055\nInvalid value at 'data[0].values[10]' (type.googleapis.com/google.protobuf.ListValue), 2.103\nInvalid value at 'data[0].values[11]' (type.googleapis.com/google.protobuf.ListValue), 2.15\nInvalid value at 'data[0].values[12]' (type.googleapis.com/google.protobuf.ListValue), 2.198\nInvalid value at 'data[0].values[13]' (type.googleapis.com/google.protobuf.ListValue), 2.302\nInvalid value at 'data[0].values[14]' (type.googleapis.com/google.protobuf.ListValue), 2.349\nInvalid value at 'data[0].values[15]' (type.googleapis.com/google.protobuf.ListValue), 2.359\nInvalid value at 'data[0].values[16]' (type.googleapis.com/google.protobuf.ListValue), 2.406\nInvalid value at 'data[0].values[17]' (type.googleapis.com/google.protobuf.ListValue), 2.406", 'status': 'INVALID_ARGUMENT', 'details': [{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'field': 'data[0].values[0]', 'description': "Invalid value at 'data[0].values[0]' (type.googleapis.com/google.protobuf.ListValue), 0.769"}, {'field': 'data[0].values[1]', 'description': "Invalid value at 'data[0].values[1]' (type.googleapis.com/google.protobuf.ListValue), 1.748"}, {'field': 'data[0].values[2]', 'description': "Invalid value at 'data[0].values[2]' (type.googleapis.com/google.protobuf.ListValue), 1.758"}, {'field': 'data[0].values[3]', 'description': "Invalid value at 'data[0].values[3]' (type.googleapis.com/google.protobuf.ListValue), 1.806"}, {'field': 'data[0].values[4]', 'description': "Invalid value at 'data[0].values[4]' (type.googleapis.com/google.protobuf.ListValue), 1.806"}, {'field': 'data[0].values[5]', 'description': "Invalid value at 'data[0].values[5]' (type.googleapis.com/google.protobuf.ListValue), 1.873"}, {'field': 'data[0].values[6]', 'description': "Invalid value at 'data[0].values[6]' (type.googleapis.com/google.protobuf.ListValue), 1.921"}, {'field': 'data[0].values[7]', 'description': "Invalid value at 'data[0].values[7]' (type.googleapis.com/google.protobuf.ListValue), 1.969"}, {'field': 'data[0].values[8]', 'description': "Invalid value at 'data[0].values[8]' (type.googleapis.com/google.protobuf.ListValue), 2.007"}, {'field': 'data[0].values[9]', 'description': "Invalid value at 'data[0].values[9]' (type.googleapis.com/google.protobuf.ListValue), 2.055"}, {'field': 'data[0].values[10]', 'description': "Invalid value at 'data[0].values[10]' (type.googleapis.com/google.protobuf.ListValue), 2.103"}, {'field': 'data[0].values[11]', 'description': "Invalid value at 'data[0].values[11]' (type.googleapis.com/google.protobuf.ListValue), 2.15"}, {'field': 'data[0].values[12]', 'description': "Invalid value at 'data[0].values[12]' (type.googleapis.com/google.protobuf.ListValue), 2.198"}, {'field': 'data[0].values[13]', 'description': "Invalid value at 'data[0].values[13]' (type.googleapis.com/google.protobuf.ListValue), 2.302"}, {'field': 'data[0].values[14]', 'description': "Invalid value at 'data[0].values[14]' (type.googleapis.com/google.protobuf.ListValue), 2.349"}, {'field': 'data[0].values[15]', 'description': "Invalid value at 'data[0].values[15]' (type.googleapis.com/google.protobuf.ListValue), 2.359"}, {'field': 'data[0].values[16]', 'description': "Invalid value at 'data[0].values[16]' (type.googleapis.com/google.protobuf.ListValue), 2.406"}, {'field': 'data[0].values[17]', 'description': "Invalid value at 'data[0].values[17]' (type.googleapis.com/google.protobuf.ListValue), 2.406"}]}]}

I can't quite find anything about that issue. Any help? I do know that it's not the fault of the range, the array itself also seems to be valid.

Answer 1

Although I'm not sure whether I could correctly understand your current issue, how about the following modification?

From:

tempWorksheet.batch_update([{
      'range': 'D3:D'+str(len(strengths_1)+2),
      'values': strengths_1,
    }])

To:

tempWorksheet.batch_update([{
    'range': 'D3',
    'values': [strengths_1],
}])

• From your error message, I guessed that strengths_1 might be a 1-dimensional array. In this case, it is required to be a 2-dimensional array.

• And, when you want to put the values from "D3", in this case, you can use only "D3".

• And, when this modified script is run, the values of strengths_1 are put from "D3" as the row direction.

• If you want to put the values from "D3" as the column direction, please modify it as follows.

  tempWorksheet.batch_update([{
      'range': 'D3',
      'values': [strengths_1],
      'majorDimension': 'COLUMNS',
  }])
  

• If an error occurs when this modified script is run, can you provide a sample value of strengths_1? By this, I would like to confirm it.

Reference:

• batch_update(data, **kwargs) of classgspread.worksheet.Worksheet(spreadsheet, properties)