Object of type Timestamp is not JSON serializable when saving pandas dataframe to google sheets

Question

Inexperienced in python here.

I have a pandas DataFrame where one of the columns is of type datetime64[ns]. When attempting to save data to Google Sheets using gspread, I get an "Object of type Timestamp is not JSON serializable" error (last line in the code snippet below). The way i understand this, I can change the datetime64[ns] to string, and it should theoretically work just fine, however there are some advantages to keeping date/time column as datetime64[ns] in pandas DataFrame. Are there any good ways to dump the data to google sheets without changing data type in data frame? this is my code:

cred = "service_account.json"
url = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit#gid=0"

course_tm = pd.DataFrame.from_records([s.to_dict() for s in ft_records])

sa = gspread.service_account(filename=cred)
spreadsheet = sa.open_by_url(url)
worksheet = spreadsheet.worksheet("Sheet1")
worksheet.update([course_tm.columns.values.tolist()] + course_tm.values.tolist())

Thank you in advance

Answer 1

I believe your goal is as follows.

• In your data frame, a column of "datetime64[ns]" object is included.
• You want to put the values of the data frame into Google Spreadsheet without changing the data type of the original data frame.

In this case, how about the following modification?

Modified script:

Before you test this script, please set the column name of "datetime64[ns]" to col_name.

cred = "service_account.json"
url = "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit#gid=0"

course_tm = pd.DataFrame.from_records([s.to_dict() for s in ft_records])

sa = gspread.service_account(filename=cred)
spreadsheet = sa.open_by_url(url)
worksheet = spreadsheet.worksheet("Sheet1")

# I modified the below script.
col_name = "### column name ###"  # Please set column name of "datetime64[ns]"
temp = course_tm.copy()
temp[col_name] = temp[col_name].dt.strftime("%Y-%m-%d %H:%M:%S")
worksheet.update([temp.columns.values.tolist()] + temp.values.tolist(), value_input_option='USER_ENTERED')

• When this modified script is run, the data frame is copied. And, the column of "datetime64[ns]" is converted to the string type. And then, the values are put into the Spreadsheet with value_input_option='USER_ENTERED'. By this, the inserted date string values are put as the date object. You can modify the date format on the Spreadsheet.