pandasgoogle-sheetstimestampdataformat

problem-with-data-format-while-importing-pandas-df-from-python-into-google-sheet

Used the second pattern suggested here: Problem with data format while Importing pandas DF from python into google sheets using df2gsheets

but I got the following error

TypeError: Object of type Timestamp is not JSON serializable

The error is related to the fact that one column in my data set is a Timestamp.

Is there a way to upload a pandas data frame to gsheets while keeping the user-defined data formats also in the case of Timestamps?

I used the following code lines, sorry for over-commenting:

#library to read Google Sheets
import gspread
#Library to write Google Sheets
from df2gspread import df2gspread as d2g
#Library to manage authorizations on Google Sheets
from oauth2client.service_account import ServiceAccountCredentials

#***set credential from JSON files stored in the same directory as current jupyter notebook
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('XXX', scope)
spreadsheet_key = 'YYY'
gc = gspread.authorize(credentials)
    
#***instructions to write with the proper format
# reference here https://stackoverflow.com/questions/63273092/problem-with-data-format-while-importing-pandas-df-from-python-into-google-sheet
wks = 'import'
spreadsheet = gc.open_by_key(spreadsheet_key)
values = [d_df.columns.values.tolist()]
values.extend(d_df.values.tolist())
spreadsheet.values_update(wks, params={'valueInputOption': 'USER_ENTERED'}, body={'values': values})`
Solution

  • About Is there a way to upload a pandas data frame to gsheets while keeping the user defined data formats also in case of Timestamps?, how about the following modification?

    From:

    spreadsheet = gc.open_by_key(spreadsheet_key)
values = [d_df.columns.values.tolist()]
values.extend(d_df.values.tolist())

    To:

    spreadsheet = gc.open_by_key(spreadsheet_key)

# --- I added the below script.
dateCols = d_df.select_dtypes(include=['datetime']).columns.values
for c in dateCols:
    d_df[c] = d_df[c].dt.strftime('%Y-%m-%d %H:%M:%S')
# ---

values = [d_df.columns.values.tolist()]
values.extend(d_df.values.tolist())