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

Question

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:

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

Decembrist

What happens if you get rid of the %f portion? – Dianoetic

@Dianoetic I get exactly the same result if I remove the %f portion. The answer from @Tanaike actually does the trick. – Decembrist

Answer 1

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:

• update
• ValueInputOption

Crescantia

gives me this: TypeError: Object of type Timestamp is not JSON serializable – Sumerology

@Sumerology About your question of gives me this: TypeError: Object of type Timestamp is not JSON serializable , I would like to support you. But unfortunately, I cannot replicate your situation. This is due to my poor skill. I deeply apologize for this. In order to correctly understand your question, can you post it as new question? By this, it will help users including me think of the solution. When you can cooperate to resolve your issue, I'm glad. Can you cooperate to do it? I would be grateful if you can forgive my poor skill. – Crescantia

this solution works like a charm! if I only change the type of the series but I don't format the date as follows: ``` uber_df['Date'] = uber_df['Date'].dt.strftime('<some date patter>') ``` I would get the error TypeError: Object of type Timestamp is not JSON serializable when updating the sheet. – Niles