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 · Accepted Answer · 2017-08-25 09:46:56Z

3

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

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

answered Aug 25, 2017 at 9:46

Philip Colmer

1,6842 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting date format in Google Sheets using API and Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related