Format specific cell to date with python in google sheet API V4

1
votes

I try to format some cells to a date format in a google spreadsheet.

The data on the cell looks like this : 09/11/2021
And I try to make it looks like this : 11/2021
Or at least format the cell with a date format.

I use batchUpdate() to update cells :
request = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

What I tried :

  • Both updateCells and repeatCell but none worked.
  • Formatting before AND after adding the value to the cell.
  • Change cells by changing rown or coln values (in the code below)
  • Other date formats like dd-mm-yy or mm yyyy
  • Also read a lot of google documentation and watched their videos...

updateCells body

        body = {
            "requests": [
                {
                'updateCells': {
                    'range': {
                        'sheetId': sheet_id,
                        'startRowIndex': rown-1,
                        'endRowIndex': rown,
                        'startColumnIndex': coln,
                        'endColumnIndex': coln
                    },
                    'rows': {'values': [{'userEnteredFormat': {'numberFormat': {'type': "DATE", 'pattern': "mm/yyyy"}}}]},
                    'fields': 'userEnteredFormat.numberFormat'
                }
                }
            ]
        }

repeatCell body

         body = {
            "requests": [
                {
                    "repeatCell": {
                        "range": {
                            "sheetId": sheet_id,
                            "startRowIndex": rown-1,
                            "endRowIndex": rown,
                            "startColumnIndex": coln,
                            "endColumnIndex": coln
                        },
                        "cell": {
                            "userEnteredFormat": {
                                "numberFormat": {
                                    "type": "DATE",
                                    "pattern": "mm/yyyy"
                                }
                            }
                        },
                        "fields": "userEnteredFormat.numberFormat"
                    }
                }
            ]
        }

Any tips and tricks or ideas are welcome !

1
python-3.xgoogle-sheetsformattinggoogle-sheets-api
Have you tried using json.dumps() to the body before using it in batchUpdate()? - Jason E.

1 Answers

2
votes

I thought that in your request body, the reason of your issue might be due to that the values of 'startColumnIndex': coln, and 'endColumnIndex': coln are the same. So, for example, how about modifying your request bodies as follows?

For updateCells body:

body = {
    "requests": [
        {
            'updateCells': {
                'range': {
                    'sheetId': sheet_id,
                    'startRowIndex': rown-1,
                    'endRowIndex': rown,
                    'startColumnIndex': coln-1, # Modified
                    'endColumnIndex': coln
                },
                'rows': {'values': [{'userEnteredFormat': {'numberFormat': {'type': "DATE", 'pattern': "mm/yyyy"}}}]},
                'fields': 'userEnteredFormat.numberFormat'
            }
        }
    ]
}

For repeatCell body:

body = {
    "requests": [
        {
            "repeatCell": {
                "range": {
                    "sheetId": sheet_id,
                    "startRowIndex": rown-1,
                    "endRowIndex": rown,
                    "startColumnIndex": coln-1, # Modified
                    "endColumnIndex": coln
                },
                "cell": {
                    "userEnteredFormat": {
                        "numberFormat": {
                            "type": "DATE",
                            "pattern": "mm/yyyy"
                        }
                    }
                },
                "fields": "userEnteredFormat.numberFormat"
            }
        }
    ]
}

Note:

  • In above modified request body, for example, when the values of rown and coln are 1 and 1, respectively, the number format of cell "A1" is modified.
  • In this modified script, it supposes that the value of 09/11/2021 in the cell is the date object. Please be careful this.

Reference: