Formatting cells (to type Duration) with the Google Sheets API (v4)

Question

I'm using the Google Sheets API (v4) to create/update spreadsheets programmatically and have run into the following issue:

I have seen via the JavaDoc that to set the cell to a NumberFormat of say, CURRENCY, I can set the NumberFormat type and pattern to:

"cell": {
  "userEnteredFormat": {
    "numberFormat": {
      "type": "CURRENCY",
      "pattern": "[$¥-411]#,##0.00"  // <--- Added
    }
  }
},
"fields": "userEnteredFormat.numberFormat"  // or "fields": "userEnteredFormat"

However, I do not see an option to set the NumberFormat type to DURATION. The acceptable enums I have found include: NUMBER_FORMAT_TYPE_UNSPECIFIED, TEXT, NUMBER, PERCENT, CURRENCY, DATE, TIME, DATE_TIME, SCIENTIFIC. Is there any way to set a cell format to a type Duration mimicking what you can do via UI?

This shows what is seen on the UI for setting a cell's format type

Answer 1

You need to obtain the format token of duration and use it as pattern in your request body.

To do that:

1. Highlight any cell and change its cell format to Duration
2. In the same cell, Navigate to Number -> More formats -> Custom number format.
3. Copy the existing format token. For duration it should be [h]:mm:ss.
4. Replace the value of pattern in your request body with [h]:mm:ss
5. Replace the value of type to DATE_TIME

Your request body should look like this:

"cell":{
   "userEnteredFormat":{
      "numberFormat":{
         "pattern": "[h]:mm:ss",
         "type": "DATE_TIME"
      }
   }
}

Before:

After:

Reference:

• Date and time format tokens