pythongoogle-sheetsgoogle-sheets-apigspread

How to get correct date with gspread

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?

enter image description here

I will omit the sheet acquisition part.

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

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

Solution

  • 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)

    References: