smartsheet API cell format of currency

Question

I'm able to successfully format a cell through the API and the compact format string, cell.format, except for cells with currency formats.

I would like to see an example of the cell.format that works for submitting 123456 as the value/display value and a cell.format string to make it appear as $1,234.56

My best guess, based on the thin documentation I have found was ",,,,,,,,,,,13,2,1,2,," It isn't clear whether the decimalCount field needs to be set if the numberFormat is set. Either way, I have been unsuccessful in getting the value to format to anything resembling US currency.

Answer 1

I've successfully tested the scenario you've described as follows:

1. Get the row that contains a cell with the numeric value I want to format (so that I can inspect initial value/format in that cell, and later compare it to what is returned after I've updated the cell format).

GET ROW (before cell format update)

request URI: https://api.smartsheet.com/2.0/sheets/3826787173066628/rows/3277051783341956?include=format

abbreviated response showing data for the cell I'm interested in:

{
    "id": 3277051783341956,
    ...
    "cells": [
        ...
        {
            "columnId": 310981806057348,
            "value": 1234.56,
            "displayValue": "1234.56",
            "format": ",,1,,,,,,,22,,,,,,,"
        }
    ]
}

The cell value looks like this initially in the Smartsheet UI:

2. Update the format of the cell, to format the cell value as US Currency, with 2 decimal places and a comma separator.

UPDATE ROW (cell format)

request URI: https://api.smartsheet.com/2.0/sheets/3826787173066628/rows

request body:

[
    {
        "id": "3277051783341956", 
        "cells": [
            {"columnId": "310981806057348", "value": 1234.56, "format": ",,,,,,,,,,,13,2,1,2,,"}
        ]
    }
]

3. Examine the cell data again, now that I've updated the format.

GET ROW (after cell format update)

request URI: https://api.smartsheet.com/2.0/sheets/3826787173066628/rows/3277051783341956?include=format

abbreviated response showing data for the cell I'm interested in:

{
    "id": 3277051783341956,
    ...
    "cells": [
        ...
        {
            "columnId": 310981806057348,
            "value": 1234.56,
            "displayValue": "$1,234.56",
            "format": ",,,,,,,,,,,13,2,1,2,,"
        }
    ]
}

The cell value now looks like this (as desired) in the Smartsheet UI:

This example verifies that the correct format to use for the scenario you've described is: ",,,,,,,,,,,13,2,1,2,,"

Also please note that the cell value for the cell needs to contain 2 decimal places -- so if the raw value you have is 123456 and you want that value to appear in the cell as $1,234.56 then your integration will need to divide the raw value by 100 and set that as the cell value in the Update Row (cell) request, in order to get the desired result.