I am trying to add rows to a Google Spreadsheet using the Python API and I am getting a 400 response code and the response body says that all the values I am trying to add are invalid.
I simplified my code to try and add 1 string value and I get the same result. I was going to start adding values back 1 by 1 to see which one was the problem, but I think the problem is something else and not actually to do with invalid value as the only thing I am trying to add now is simple string of the current date and I get the same response.
Here is my code:
client = gspread.oauth(credentials_filename="credentials.json")
sheet = client.open(constants_manager.SPREADSHEET_FILE_NAME)
worksheet = sheet.worksheet(constants_manager.SPREADSHEET_SHEET_NAME)
today_date = date.today().strftime("%d/%m/%Y")
old_num_rows = len(worksheet.get_all_values())
print(old_num_rows)
new_values = [today_date]
# for item in macros.values():
# new_values.append(round(item,2))
# print(type(item))
print(new_values)
worksheet.insert_rows(new_values, old_num_rows+1)
And here is my console output with the error message. You can see that I print new_values which is a 1 element list with the current date as a string.
['02/01/2024']
Traceback (most recent call last):
File "/home/moorby/Documents/coding_projects/loseit_calorie_weight_data_tracker/main.py", line 63, in <module>
main()
File "/home/moorby/Documents/coding_projects/loseit_calorie_weight_data_tracker/main.py", line 59, in main
worksheet.insert_rows(new_values)
File "/home/moorby/.pyenv/versions/loseit_calorie_weight_data_tracker/lib/python3.11/site-packages/gspread/worksheet.py", line 2049, in insert_rows
res = self.spreadsheet.values_append(range_label, params, body)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moorby/.pyenv/versions/loseit_calorie_weight_data_tracker/lib/python3.11/site-packages/gspread/spreadsheet.py", line 141, in values_append
r = self.client.request("post", url, params=params, json=body)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/moorby/.pyenv/versions/loseit_calorie_weight_data_tracker/lib/python3.11/site-packages/gspread/client.py", line 93, in request
raise APIError(response)
gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid value at \'data.values[0]\' (type.googleapis.com/google.protobuf.ListValue), "02/01/2024"', 'status': 'INVALID_ARGUMENT', 'details': [{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'field': 'data.values[0]', 'description': 'Invalid value at \'data.values[0]\' (type.googleapis.com/google.protobuf.ListValue), "02/01/2024"'}]}]}
I found some other answers that suggested the Spreadsheet ID might be wrong. I do not think that is the case for me as I can set inherit_from_before as True in the worksheet.insert_rows() method and I can see new rows being added with the same background colour as before.
I'm really confused as my string value of the current date can't be an invalid value, it's just a string. What's going on?
EDIT: I also just tried with new_values = ["hello"] and I get the same result, apparently it is an invalid value.
The docs for insert_rows state it requires a 2D list structure:
values (list) – List of row lists. a list of lists, with the lists each containing one row’s values. Widens the worksheet if there are more values than columns.
To fix your code you need to wrap the data you want to insert into a list to create the list of row values.
worksheet = sheet.worksheet(constants_manager.SPREADSHEET_SHEET_NAME)
today_date = date.today().strftime("%d/%m/%Y")
old_num_rows = len(worksheet.get_all_values())
print(old_num_rows)
new_values = [ [today_date] ] # <-- HERE
for item in macros.values():
new_values.append( [ round(item,2) ] ) # <-- HERE
print(type(item))
print(new_values)
worksheet.insert_rows(new_values, old_num_rows+1)
If you want new cells in the same row just add them to inner row value list:
new_values = [ [today_date, "more", "cells"] ]
for item in macros.values():
new_values.append( [ round(item,2), "more", "cells" ] )