How to Preserve Date Format in Google Sheets when Updating with Python (gspread and pandas)

Question

I'm currently working with a dataset in Google Sheets and as part of the data cleaning process, I'm utilizing Python along with libraries such as gspread and pandas to remove unnecessary columns before updating the spreadsheet with the cleaned data. However, I've run into an issue after executing my Python script: attempting to use Google Sheets' QUERY function to extract specific data based on a date-formatted column into another worksheet seems to fail. It appears that my script inadvertently alters the date column's format, causing the QUERY function to not recognize the dates correctly. This results in an error stating, "The query has completed with an empty result." This problem does not occur when I manually remove the columns; it only surfaces after running my script, despite the column format being set to 'Automatic'.

Below is the code snippet I've been using:

def load():
    auth.authenticate_user()
    creds, _ = default()
    gc = gspread.authorize(creds)

    wb = gc.open_by_key('key')
    ws = wb.worksheet("worksheet")
    rows = ws.get_all_values()

    df = pd.DataFrame.from_records(rows[1:], columns=rows[0])
    return df

def update(cols):
    base = load()
    base.drop(columns=cols, axis=1, inplace=True)

    new_bs = [base.columns.tolist()] + base.values.tolist()

    auth.authenticate_user()
    creds, _ = default()
    gc = gspread.authorize(creds)
    wb = gc.open_by_key('key')
    ws = wb.worksheet("worksheet")

    ws.clear()

    ws.update('A1', new_bs)

columns_to_remove = ['column1', 'column2', "column3", "column4", "column5"]
update(columns_to_remove)

After analysis, I've identified the issue with the date formatting. Following the execution of my Python script to update a Google Sheets worksheet, I noticed that an apostrophe precedes the date values, formatting them as text ('28/02/2019). This prevents Google Sheets from recognizing these entries as dates. Although manually removing the leading apostrophe converts the text back to a date format, this manual intervention is not very efficient.

Answer 1

In your situation, how about the following modification?

From:

ws.update('A1', new_bs)

To:

ws.update('A1', new_bs, value_input_option=gspread.utils.ValueInputOption.user_entered)

or

ws.update('A1', new_bs, value_input_option="USER_ENTERED")

Reference:

• ValueInputOption