1

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

isswf
  • 115
  • 1
  • 10
  • 1
    Although I'm not sure whether I could correctly understand your question, I proposed a modified script as an answer. Please confirm it. If I misunderstood your question and that was not useful, I apologize. – Tanaike Aug 31 '23 at 08:03
  • @Tanaike Thank you : you did understand this correctly. I thought there may have been a better way than copying the dataframe, but this will do. thank you – isswf Sep 02 '23 at 04:49
  • Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. – Tanaike Sep 02 '23 at 05:12

1 Answers1

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.
Tanaike
  • 181,128
  • 11
  • 97
  • 165