Using Google Sheets API, adding and updating Column Formatting is not honoured when I entered the data

Question

So, it is really a follow-up question based on my last attempt

How to add the column to Google Sheets using API and provide the name and type of the column in the same call?

So, I thought to use batchUpdate command to do the following in a single API call

• Add a new column.
• Give a name to the column at A1:A2 (using A1 notation)
• Provide the formatting for the column values A2:A (for all the values that will fall into this column.

I structured my API call and executed as following

curl -v \
-H 'Authorization: Bearer ya29.GlxUB-UnpiYLeMzdAYyz_kVvNIVwmlMvxauAfiklslphd60INbgOQEr2eUwdx4smOSirde63hW2CvR17AMfj1P5iGw1Q3dRgGBHGZ92JKoFWozSxjbJh0wEZJKvDUQ' \
-H 'Content-Type: application/json' \
-d '{
      "requests": [
        {
          "insertDimension": {
            "range": {
              "sheetId": 2052094881,
              "dimension": "COLUMNS",
              "startIndex": 0,
              "endIndex": 1
            }
          }
        },
        {
          "repeatCell": {
            "range": {
              "sheetId": 2052094881,
              "startRowIndex": 0,
              "endRowIndex": 1,
              "startColumnIndex": 0,
              "endColumnIndex": 1
            },
            "cell": {
              "userEnteredValue": {
                "stringValue": "discretionary"
              }
            },
            "fields": "userEnteredValue.stringValue"
          }
        },{
          "repeatCell": {
            "range": {
              "sheetId": 2052094881,
              "startRowIndex": 1,
              "startColumnIndex": 1,
            },
            "cell": {
              "userEnteredFormat": {
                "numberFormat": {
                    "type": "DATE_TIME",
                    "pattern": "dddd, m/d/yy at h:mm"
                }
              }
            },
            "fields": "userEnteredFormat.numberFormat"
          }
        },
      ],
    }' \
https://sheets.googleapis.com/v4/spreadsheets/1mHrPXQILuprO4NdqTgrVKlGazvvzgCFqIphGdsmptD8:batchUpdate

This seemed to work fine as I could see a new Column at index 0 which is A and the API response was also good

< HTTP/2 200
< content-type: application/json; charset=UTF-8
< vary: X-Origin
< vary: Referer
< vary: Origin,Accept-Encoding
< date: Mon, 29 Jul 2019 20:09:29 GMT
< server: ESF
< cache-control: private
< x-xss-protection: 0
< x-frame-options: SAMEORIGIN
< alt-svc: quic=":443"; ma=2592000; v="46,43,39"
< accept-ranges: none
<
{
  "spreadsheetId": "1mHrPXQILuprO4NdqTgrVKlGazvvzgCFqIphGdsmptD8",
  "replies": [
    {},
    {},
    {}
  ]
}
* Connection #0 to host sheets.googleapis.com left intact

So, I thought to verify that if now I put any value into this column (starting from A2), the formatting dddd, m/d/yy at h:mm would be applied. So I inserted a value which looks like following command

curl -v \
-H 'Authorization: Bearer ya29.GlxUB3ad1mGpbFBT1raZx2C76kmiSPBDX6_epAWNd3hwWC7EMtzgVBPB34JAK543N7g2Y1cYeFc_VWjTpmeU7LSE4nDGiyCUafqhqPTJHUg5UmrfjVxH5pcmeJXK4Q' \
-H 'Content-Type: application/json' \
-X POST \
-d '{
  "valueInputOption": "USER_ENTERED",
  "data": [
    {
      "range": "Sheet1!A2:A3",
      "majorDimension": "COLUMNS",
      "values": [
        ["7/29/2019 12:56:31"]
      ]
    }
  ]
}' \
https://sheets.googleapis.com/v4/spreadsheets/1mHrPXQILuprO4NdqTgrVKlGazvvzgCFqIphGdsmptD8/values:batchUpdate

When I executed, the response seemed fine

< HTTP/2 200
< content-type: application/json; charset=UTF-8
< vary: X-Origin
< vary: Referer
< vary: Origin,Accept-Encoding
< date: Mon, 29 Jul 2019 20:09:58 GMT
< server: ESF
< cache-control: private
< x-xss-protection: 0
< x-frame-options: SAMEORIGIN
< alt-svc: quic=":443"; ma=2592000; v="46,43,39"
< accept-ranges: none
<
{
  "spreadsheetId": "1mHrPXQILuprO4NdqTgrVKlGazvvzgCFqIphGdsmptD8",
  "totalUpdatedRows": 1,
  "totalUpdatedColumns": 1,
  "totalUpdatedCells": 1,
  "totalUpdatedSheets": 1,
  "responses": [
    {
      "spreadsheetId": "1mHrPXQILuprO4NdqTgrVKlGazvvzgCFqIphGdsmptD8",
      "updatedRange": "Sheet1!A2",
      "updatedRows": 1,
      "updatedColumns": 1,
      "updatedCells": 1
    }
  ]
}

However, when I looked at the Sheet from the browser, the literal value 7/29/2019 12:56:31 was available. I found this incorrect, because I expected formatting dddd, m/d/yy at h:mm to have applied

I am looking to find out the answers to the following questions
- First, what am I doing wrong here? - How do I fix this issue?
- How can I know the formatting of a column when looking at the column using the browser view?

Thank you very much for reading this far!

Answer 1

• You want to know that why the values put with the method of "values:batchUpdate" are not formatted.
• You want to know about the method for modifying the format.
  • You want to see above with the browser view.

If my understanding is correct, how about this answer?

Answer for question 1:

I think that in your question 1, there are 2 issues.

Issue 1:

From your 1st curl command, I thought that the index of gridrange is not correct. In your question, you are trying to do the following update.

1. You want to insert a new column to left of the column "A".
2. You want to put a value of discretionary to the row 1 of the inserted column (in this case, it's the column "A".).
3. You want to set the number format of dddd, m/d/yy at h:mm as DATE_TIME for the inserted column.

In this case, in 3 requests of your curl command, 1st and 2nd requests are correct. But the 3rd request has an issue. The request body of the 3rd request is as follows.

{
  "repeatCell": {
    "range": {
      "sheetId": 2052094881,
      "startRowIndex": 1,
      "startColumnIndex": 1   <--- here
    },
    "cell": {
      "userEnteredFormat": {
        "numberFormat": {
          "type": "DATE_TIME",
          "pattern": "dddd, m/d/yy at h:mm"
        }
      }
    },
    "fields": "userEnteredFormat.numberFormat"
  }
}

1 and 1 are used for startRowIndex and startColumnIndex, respectively. This means all cells from the cell "B1". By this, the values put to "A2:A3" by values:batchUpdate are put to the cells without formatting. This is the reason of 1st issue.

Solution for 1st issue:

As the solution, please use 1 and 0 are used for startRowIndex and startColumnIndex, respectively. By this, the format is reflected to the column "A".

Issue 2:

When 7/29/2019 12:56:31 is put by values:batchUpdate, it seems that the format is overwritten. By this, the format set at above cannot be reflected. And furthermore, the date and time of Spreadsheet is managed by the serial number. These are the reason of 2nd issue.

Solution for 2nd issue:

As the solution, please use the serial number. When 7/29/2019 12:56:31 is converted to the serial number, it becomes 43675.54. When this is used for your below curl command, it becomes as follows.

Modified curl command:

curl \
-H 'Authorization: Bearer ###' \
-H 'Content-Type: application/json' \
-X POST \
-d '{"valueInputOption":"USER_ENTERED","data":[{"range":"Sheet1!A2:A3","majorDimension":"COLUMNS","values":[[43675.54]]}]}' \
https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchUpdate

• Please set the value of 43675.54 as a number. So please don't enclose it by the double quotes and single quotes. By this, 43675.54 is used as the number with USER_ENTERED.
• In this case, when this curl command is run, you can see that the formatted value is put to the cell on your browser.

Answer for question 2:

I think that the answer for question 1 can also answer for the answer for question 2.

References:

• GridRange
• Method: spreadsheets.values.update

If I misunderstood your question and this was not the direction you want, I apologize.