How to get correct date with gspread

Question

How can I get the spreadsheet values with Python's gspread?

Suppose there is a cell that looks like 1/1 because m/d is specified as the cell display format, but actually contains 2024/1/1. Retrieving this cell using get_all_values() returns "1/1". I want the actual value "2024/1/1", not the value displayed on the sheet. What should I do?

I will omit the sheet acquisition part.

values = workbook.worksheet(sheet_name).get_all_values()

value = values[1][0] # 1/1 will be obtained

Answer 1

Issue and workaround:

From your showing image, script, and current value, I understood that you put a value of 2024/01/01 into a cell "A2" as a date object. And, the cell value is shown as 1/1 with the number format.

In the current stage, when this cell value is retrieved by Sheets API, 1/1 is retrieved as the default request of valueRenderOption: FORMATTED_VALUE. When valueRenderOption is changed to UNFORMATTED_VALUE, the serial number of 45292 is retrieved. Unfortunately, in the current stage, the inputted value of 2024/01/01 cannot be directly retrieved. So, it is required for the following flow.

1. Retrieve the serial number from a cell with valueRenderOption: UNFORMATTED_VALUE.
2. Convert the serial number to the date object.
3. Convert date format.

When this flow is reflected in your showing script, how about the following modification?

Modified script:

values = workbook.worksheet(sheet_name).get_all_values(value_render_option="UNFORMATTED_VALUE")
value = values[1][0]
date = datetime.fromtimestamp((int(value) - 25569) * 86400) # Ref: https://stackoverflow.com/a/6154953
formatted = date.strftime('%Y/%m/%d') # or date.strftime('%Y/%-m/%-d')  # or date.strftime('%Y/%#m/%#d')
print(formatted)

• In the case that the cell "A2" has 2024/01/01 of the date object and 1/1 is shown by the number format, when this script is run, formatted is 2024/01/01.

• from datetime import datetime is used.

References:

• get_all_values
• Method: spreadsheets.values.get