How to get correct date using gspread

Question content

How to use Python’s gspread to get spreadsheet values?

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 shown on the worksheet. what do I do?

I will omit the worksheet acquisition part.

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

value = values[1][0] # Get 1/1

Correct answer

Problems and solutions:

From the image, script, and current value you showed, I understand that you put the value of 2024/01/01 into cell "a2" as a date object. Also, the cell value is displayed as 1/1 in numeric format.

At the current stage, when retrieving that cell value via the sheets api, 1/1 will be retrieved as the default request for valuerenderoption: formatted_value. When valuerenderoption is modified to unformatted_value, the serial number of 45292 is obtained. Unfortunately, the entered 2024/01/01 value cannot be directly retrieved at this stage. Therefore, the following process requires it.

1. Retrieve the sequence number from the cell using valuerenderoption: unformatted_value.
2. Convert serial number to date object.
3. Convert date format.

How about the following modification when this flow is reflected in your presentation script?

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)

• If the date object of cell "a2" is 2024/01/01, and 1/1 is displayed in numeric format, when running this script, formatted is 2024/01/01.

• Use from datetime import datetime.

references:

• get_all_values一个>
• Method: spreadsheets.values.get一个>

The above is the detailed content of How to get correct date using gspread. For more information, please follow other related articles on the PHP Chinese website!