How to format a datetime so Google sheets recognise the value as date time

user_616349 0
Viewed 4688

I'm currently using gspread to retrieve data from a Google sheet into a pandas DataFrame. In order to do that, I simply followed their example at https://gspread.readthedocs.io/en/latest/user-guide.html#using-gspread-with-pandas

df = pd.DataFrame(sheet.get_all_records())
df["From"] = pd.to_datetime(df["From"])
df["To"] = pd.to_datetime(df["To"])

It works well but problems arise when I want to update the spreadsheet.

If I only try to update the range with the pandas DataFrame:

sheet.update([df.columns.values.tolist()] + df.values.tolist())

I get the following error:

Object of type Timestamp is not JSON serializable

Therefore, I believe I need to cast the timestamp back to a string:

df["From"] = df["From"].dt.strftime("%Y-%m-%d %H:%M:%S.%f")
df["To"] = df["To"].dt.strftime("%Y-%m-%d %H:%M:%S.%f")

The update works well, but the data are not recognised back as timestamps:

enter image description here

Is there a way to get Google Spreadsheet to recognise the date correctly (i.e. what would be the correct format)?

gspread google-sheets-api dataframe python
1 Answers

How about this modification?

From:

df["From"] = df["From"].dt.strftime("%Y-%m-%d %H:%M:%S.%f")
df["To"] = df["To"].dt.strftime("%Y-%m-%d %H:%M:%S.%f")
sheet.update([df.columns.values.tolist()] + df.values.tolist())

To:

df["From"] = df["From"].dt.strftime("%Y-%m-%d %H:%M:%S.%f")
df["To"] = df["To"].dt.strftime("%Y-%m-%d %H:%M:%S.%f")
sheet.update([df.columns.values.tolist()] + df.values.tolist(), value_input_option='USER_ENTERED')

USER_ENTERED: The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI. Ref

At gspread, it seems that the default value of value_input_option is RAW. By this, the single quote is added to the top of value as the text value.

References:

user_71086530
Related