CODEWITHSUNDEEP
google-sheets-api
Philip Colmer
Philip Colmer

Reputation: 1664

Setting date format in Google Sheets using API and Python

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?

Upvotes: 1

Views: 2565

Answers (1)

Philip Colmer
Philip Colmer

Reputation: 1664

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

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

Upvotes: 3

Related Questions