使用gspread API批量更新谷歌表格遇Invalid value异常求助

使用gspread API操作Google Sheets，此前处理普通数组正常，但处理浮点数组成的Numpy数组maturities_1时，抛出"Invalid value at 'data[0].values[0]'"的APIException。

代码片段 #

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,
    }])

异常信息 #

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"}]}]}

解决方案 #

报错原因是gspread的batch_update接口要求values参数是二维数组：每个子数组对应一行的单元格数据。而Numpy数组调用tolist()后生成的是一维数组（如[0.769, 1.748, ...]），不符合Google Sheets API的格式要求。

修改strengths_1的生成方式，将一维数组转换为二维数组（每个元素包裹为单元素列表）：

方法1：使用Numpy的reshape #

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

方法2：使用列表推导式 #

strengths_1 = [[x] for x in np.round(maturities_1, 3).astype(float)]

修改后，strengths_1会变成[[0.769], [1.748], [1.758], ...]的格式，符合API要求，即可正常执行批量更新。

内容的提问来源于stack exchange，提问作者JEREDEK