Converting Google Sheets API fetched date format to standard date

Other Questions Google Sheets
1

I’m working with a Google Sheets document that has dates in the format “Jan 30”. I’m using the Python Google Sheets API to get this data. Here’s what I’ve tried:

 def fetch_sheet_data(service, sheet_id):
     sheet_info = service.spreadsheets().get(spreadsheetId=sheet_id).execute()
     target_cell = "C15"
     for sheet in sheet_info['sheets']:
         sheet_name = sheet['properties']['title']
         cell_range = f"{sheet_name}!{target_cell}"
         response = service.spreadsheets().values().get(
             spreadsheetId=sheet_id, range=cell_range).execute()
         cell_data = response.get('values', [])

This gives me ‘Jan 30’, but I want it as ‘1/30/2017’. How can I convert this?

I tried using valueRenderOption='UNFORMATTED_VALUE', but it returned 42765. What does this number mean?

I found a workaround using Python’s date formatting, but it assumes the current year:

 date_str = f"{cell_data[0][0]} {datetime.date.today().year}"
 cell_date = datetime.datetime.strptime(date_str, '%b %d %Y')

Is there a better way to handle this date conversion? Any help would be appreciated!

2

The number 42765 you’re seeing with UNFORMATTED_VALUE is actually quite useful. It’s the date’s serial number in Excel/Google Sheets, representing days since January 1, 1900. You can leverage this for accurate date conversion:

from datetime import datetime, timedelta

base_date = datetime(1899, 12, 30)  # Excel's day 0
cell_date = base_date + timedelta(days=int(cell_data[0][0]))
formatted_date = cell_date.strftime('%m/%d/%Y')

This approach gives you the exact date, including the correct year, without assumptions. It’s more reliable than parsing strings, especially when dealing with dates from various years. Just ensure you’re consistently using UNFORMATTED_VALUE in your API calls for dates.

3

hey zack, have u tried using pandas? it’s pretty good for handling dates. u could do smth like:

import pandas as pd
date = pd.to_datetime(‘Jan 30’, format=‘%b %d’)
formatted_date = date.strftime(‘%m/%d/%Y’)

this’ll give u the date in the format u want. it assumes current year tho, so u might need to tweak it if u need a specific year.

4

I’ve encountered similar issues when working with Google Sheets API and dates. One approach that’s worked well for me is using the dateutil library. It’s quite flexible and can handle various date formats:

from dateutil import parser
import datetime

date_str = cell_data[0][0]  # 'Jan 30'
parsed_date = parser.parse(date_str)

# If you need to set a specific year
parsed_date = parsed_date.replace(year=2017)

formatted_date = parsed_date.strftime('%m/%d/%Y')

This method is robust and doesn’t assume the current year. You can easily set any year you need. It’s also less prone to errors when dealing with different locales or unexpected date formats. Just make sure to install the python-dateutil package first.