The goal of this question is understand Why I get this error message if I do X and Why I don't get if I do Y.
Probably is a silly question.
I want to update an Google Sheet from my End User Account on Google Colab. The update consist in adding the column header of a data frame to a new worksheet.
I read the gspread documentation and I saw how the do:
import pandas as pd
worksheet.update([dataframe.columns.values.tolist()] + dataframe.values.tolist())
Why if I remove the square brackets I get and error like:
APIError: {'code': 400, 'message': 'Invalid value at 'data.values[0]' (type.googleapis.com/google.protobuf.ListValue)
I created a reproducible code to try understand better:
# Creating my dummy Data Frame
test_dict = {"Date":[],"KPI1":[],"KPI2":[]}
test_df = pd.DataFrame(test_dict)
# Creating my test sheet on Google Sheet
sheet_name_test= "Test"
sh_test = gc.create(sheet_name_test)
#Adding a specific worksheet
worksheet_test = sh_test.add_worksheet(title = "test",
rows =10,
cols =5)
#In that way works
worksheet_test.update([test_df.columns.values.tolist()])
#In that way desn't work
worksheet_test.update(df_columns_order.columns.values.tolist())
In that way I get the previous highlighted error:
APIError: {'code': 400, 'message': 'Invalid value at \'data.values[0]\' (type.googleapis.com/google.protobuf.ListValue), "Date"\nInvalid value at \'data.values[1]\' (type.googleapis.com/google.protobuf.ListValue), "KPI1"\nInvalid value at \'data.values[2]\' (type.googleapis.com/google.protobuf.ListValue), "KPI2"', '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), "Date"'}, {'field': 'data.values[1]', 'description': 'Invalid value at \'data.values[1]\' (type.googleapis.com/google.protobuf.ListValue), "KPI1"'}, {'field': 'data.values[2]', 'description': 'Invalid value at \'data.values[2]\' (type.googleapis.com/google.protobuf.ListValue), "KPI2"'}]}]}
In your showing script, I think that test_df.columns.values.tolist() is 1 dimensional array. When I saw the document of gsperad, it seems that worksheet.update([42, 43, 44]) can be used. Ref
But, when I saw the script of gspread, update method of Class Worksheet, the Ref It seems that the inputted values are directly used to the request body. Ref At "Method: spreadsheets.values.update" of Sheets API, it is required to use a 2-dimensional array to the values. I thought that this is the reason for your issue. (I'm not sure whether this is the current specification or a bug.)
In this situation, when you use the following script, no error occurs.
worksheet_test.update([test_df.columns.values.tolist()])
When you use the following script, an error occurs in your showing error.
worksheet_test.update(test_df.columns.values.tolist())