How to read value of fetched cell data as date google sheets API

Question

I have a spreadsheet that have dates formatted as "Jan 30" etc, but have actual date values.

I fetch the data using python google Sheets API like so:

def get_spreadsheet_sheets(service, spreadsheetId):
    spreadsheet = service.spreadsheets().get(spreadsheetId=spreadsheetId).execute()
    dateCell = "C15"
    for sheet in spreadsheet['sheets']:
        sheetTitle = sheet['properties']['title']
        rangeName = "%s!%s" % (sheetTitle, dateCell)
        result = service.spreadsheets().values().get(
            spreadsheetId=spreadsheetId, range=rangeName).execute()
        values = result.get('values',[])

values gives me 'Jan 30', I would like to actually read it as 1/30/2017.. How can I do that?

update

Digger deeper into the spreadsheets.value.get http documentation (which corresponds to the python api client method references here I learned that I can put the option of valueRenderOption as UNFORMATTED_VALUE..

however when I run that I get this weird number for Jan 30: 42765

what format is that number exactly? I know it's not a unix time stamp b/c converting it returns a non-sensical date

update 2: workaround

this code works but using python date formatting (it also assumes that the year is the current year.. but obviously that won't always be the case):

def get_spreadsheet_sheets(service, spreadsheetId):
    spreadsheet = service.spreadsheets().get(spreadsheetId=spreadsheetId).execute()
    dateCell = "C15"
    for sheet in spreadsheet['sheets']:
        sheetTitle = sheet['properties']['title']
        rangeName = "%s!%s" % (sheetTitle, dateCell)
        result = service.spreadsheets().values().get(
            spreadsheetId=spreadsheetId, range=rangeName).execute()
        values = result.get('values',[])
        dateStr = "%s %s" % (values[0][0], datetime.date.today().year)
        cellDate = datetime.datetime.strptime(dateStr, '%b %d %Y')
        print(cellDate)

the value python API fetches is what you see on the spreadsheet not the underlying value you input i.e. the format matters. — Alex Fung, Feb 14 '17 at 02:06
but how can i know the year of the date when i fetch it if i simply get `Jan 30`? — abbood, Feb 14 '17 at 02:15

Rubén · Accepted Answer · 2022-01-05T14:13:55.720

13

The "weird number" it's called "serialized date-time value" or "date serial value". The 0 value displayed as date (yyyy-mm-dd) is 1899-12-30. Integers are days, fractions are hours, minutes, etc.

Related: Converting Google spreadsheet date into a JS Date object?

References:

TO_DATE, Google Spreadsheet built-in function
~~DateTime Serial Numbers, Article section from Google Sheets > API v4 Guide~~ (the referred section was removed, it might be available on archive.org)
Date and Number Formats, Article from Google Sheets > API v4 Guide.
Google Sheet API V4(Java) append Date in cells , Stack Overflow Q&A

h/t Sam Berlin

edited Jan 05 '22 at 14:13

answered Feb 14 '17 at 15:39

Rubén

34,714
9
70
166

See also: [DateTime Serial Numbers](https://developers.google.com/sheets/api/guides/concepts#datetime_serial_numbers), and [Google Sheet API V4(Java) append Date in cells](http://stackoverflow.com/questions/37986171/google-sheet-api-v4java-append-date-in-cells/37993497#37993497). – Sam Berlin Feb 14 '17 at 20:26
@SamBerlin: Thank you very much. – Rubén Feb 14 '17 at 20:31
1

Upvoted for the link to the Google Sheets > API v4 Guide. For whatever reason, this was very hard to find, not being in a section of its own. – chutz Feb 06 '18 at 04:12

How to read value of fetched cell data as date google sheets API

update

update 2: workaround

1 Answers1

Linked