CODEWITHSUNDEEP
pythongoogle-sheets-api
yulGM
yulGM

Reputation: 1094

where to add the valueInputOption to enable formatting dates? (google sheets API)

I have a python script that has to update a column in a google sheet to specific date format:

    req = [
          { 
            "repeatCell": {
              "range": {
                "sheetId": SHEET_ID,
                "startColumnIndex": 2,
                "endColumnIndex" : 3,
              },
              "cell": {
                "userEnteredFormat": {
                  "numberFormat": {
                      "type": "DATE",
                      "pattern": "dd-mmm-yyyy"
                },
                  
                  "horizontalAlignment" : "CENTER",  #OPTIONS: 'LEFT', 'RIGHT', 'CENTER'
                }
              },
              "fields": "userEnteredFormat(numberFormat,horizontalAlignment)",
            }
          }]
        body = {'requests':req}
        sheet.batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=body).execute()

However the updates don't go through. I checked and from what I understand it is because the date format in the file is string (starts with ', like '2021-03-06 14:19:17.102.

I saw some answers that suggest that I have to change ValueInputOption to 'USER_ENTERED', but I cannot find where to add the change in the script?

eg: (How to format text as a number using the Google Sheets API?)

I tried adding ValueInputOption='USER_ENTERED' in last row, and i tried body = {'requests':req, 'ValueInputOption':'USER_ENTERED'. Not sure where this change supposed to go?

Or is there another way to overwrite 'string' to date and number formats?

Upvotes: 1

Views: 704

Answers (1)

CMB
CMB

Reputation: 5163

Explanation:

The ValueInputOption parameter is from a different batchUpdate request, namely the spreadsheets.values.batchUpdate method, and cannot be used in spreadsheets.batchUpdate.

Also, since the value of the cell forces the date to be a string value due to the ', you need to remove it either manually or via spreadsheets.values.batchUpdate().

References:

spreadsheets.batchUpdate()

spreadsheets.values.batchUpdate()

Upvotes: 1

Related Questions