Setting date format in Google Sheets using API and Python

Question

I'm trying to set the date format on a column so that dates are displayed like this: 14-Aug-2017. This is the way I'm doing it:

requests = [
    {
        'repeatCell':
        {
            'range':
            {
                'startRowIndex': 1,
                'startColumnIndex': 4,
                'endColumnIndex': 4
            },
            'cell':
            {
                "userEnteredFormat":
                {
                    "numberFormat":
                    {
                        "type": "DATE",
                        "pattern": "dd-mmm-yyyy"
                    }
                }
            },
            'fields': 'userEnteredFormat.numberFormat'
        }
    }
]
body = {"requests": requests}
response = service.spreadsheets().batchUpdate(spreadsheetId=SHEET, body=body).execute()

I want all the cells in column E except the header cell to be updated, hence the range definition. I used http://wescpy.blogspot.co.uk/2016/09/formatting-cells-in-google-sheets-with.html and https://developers.google.com/sheets/api/samples/formatting as the basis for this approach.

However, the cells don't show their contents using that format. They continue to be in "Automatic" format, either showing the numeric value that I'm storing (the number of days from 1st Jan 1900) or (sometimes) the date.

Adding sheetId to the range definition doesn't alter the outcome.

I'm not getting an error back from the service and the response only contains the spreadsheetId and an empty replies structure [{}].

What am I getting wrong?

Philip Colmer Philip Colmer · Accepted Answer · 2017-08-25T09:46:56

I've found the error - the endColumnIndex needs to be 5, not 4.

I didn't read that first linked article carefully enough!

Setting date format in Google Sheets using API and Python

1 Answers