Issue: Smartsheet API Not Returning Data for Formula Columns in Sheets

SmarsheetUser1

I am experiencing an issue with the Smartsheet API where data from columns containing formulas are not being returned when querying a sheet. However, the same data is correctly displayed in the UI and can be accessed through reports via THE API. So I can access sheet data for formula columns via a report that points to this sheet, but I can't retrieve formula columns data when directly trying to access sheet data. The Sheets are not returning the expected data but Reports that point to the sheet work fine and all the data is returned. Below are the details of the issue:

Report Data Request: GET /2.0/reports/reportID?level=2&page=1&pageSize=500

The API response for this request includes the calculated from the formula columns.

Column Details:

Data:

Sheet Data Request: GET /2.0/sheets/sheetID?pageSize=500&page=1

The API response for this request does not include data from the formula columns, despite the UI showing the correct values in the sheet. Values and DisplayValues are 0 in the API response.

Column Details:

Data:

Is this a know issue? If you have any ideas kindly share them. I really appreciate your help.

Thank you so much.

jmyzk_cloudsmart_jp

Hi @SmarsheetUser1

Are you comparing the same cell? Do they have the same row ID?

I suspect the "Data:" value on the sheet shows different cell values.

SmarsheetUser1

Hi @jmyzk_cloudsmart_jp, thank you for your reply.

Indeed, I am comparing the same cell. The issue is that the data for the formula column is 0.0 for the entire column. While I can see the correct data in the Smartsheet UI for this column in the sheet, but when trying to access sheet data via the API, it returns 0.0 values.

Let me know if you have any other ideas. Could this be related to permissions? Is it not expected that formula columns would not return data?

Thank you so much!

jmyzk_cloudsmart_jp

Hi @SmarsheetUser1

I am unsure if I understand your problem well, but here is my test result.

sheet column [test] with column formula '=0'

Sheet cell value gotten by API

{"columnId": 7644573838036868, "displayValue": "0", "formula": "=0", "value": 0.0}

Report cell value gotten by API

{"columnId": 7644573838036868, "displayValue": "0", "formula": "==", "value": 0.0, "virtualColumnId": 2221516201873284}

SmarsheetUser1

Hi @jmyzk_cloudsmart_jp,

Thank you for your response. Sorry for the confusion, but in the Smartsheet GUI, the sheet does not have 0 values. The API is returning 0 when querying the sheet, but it returns the correct value(385,300,057.36) when querying the report that points to the same sheet.

See below example:
sheet column TOTAL INTERNAL COST:

Formula:

Sheet cell value gotten by API:

{"columnId":4211383104130948,"value":0.0,"displayValue":"0","formula":"=IFERROR(IF(Summary@row = 1, "", IF(COUNT(CHILDREN([Program Details]@row)) > 0, SUM(CHILDREN()), [Net Total (Before Tax)]@row + [TAX COST TOTAL]@row + [GRATUITY COST TOTAL]@row)), "")"}

Report cell value gotten by API

{"columnId":5133666026999684,"virtualColumnId":5681717244546948,"value":3.85300057360896E8,"displayValue":"385,300,057.36","formula":"=="}

Let me know if this help. The only difference is see from your case is that columnId match in your case, but in mine they don't! Not sure if this might be related?!

Thank you so much!

jmyzk_cloudsmart_jp

Hi @SmarsheetUser1

I suspect you are comparing different columns, as the "columnIds" differ.

Yours:

Sheet cell value gotten by API:

{"columnId":4211383104130948,"value":0.0,"displayValue":"0","formula":"=IFERROR(IF(Summary@row = 1, "", IF(COUNT(CHILDREN([Program Details]@row)) > 0, SUM(CHILDREN()), [Net Total (Before Tax)]@row + [TAX COST TOTAL]@row + [GRATUITY COST TOTAL]@row)), "")"}

Report cell value gotten by API

{"columnId":5133666026999684,"virtualColumnId":5681717244546948,"value":3.85300057360896E8,"displayValue":"385,300,057.36","formula":"=="}

Mine:

Sheet cell value gotten by API

{"columnId": 7644573838036868, "displayValue": "0", "formula": "=0", "value": 0.0}

Report cell value gotten by API

{"columnId": 7644573838036868, "displayValue": "0", "formula": "==", "value": 0.0, "virtualColumnId": 2221516201873284}

JustMeThisTime

@jmyzk_cloudsmart_jp:
I'm experiencing a similar issue w/ the API where when targeting the intersection of the ColumnID & RowID, I'm not getting the true displayValue.
For instance what is returned below is from a cell which renders on screen as $24,300

{"columnId":1059275962273668,"value":24271,"displayValue":"$24,271.00","formula":"=ROUND([Cost]@row * (1 + Markup@row), -2)"},

I created an adjacent column simply referencing the first column to see of that helps return the desired 24300, but when that cell is targeted the following is returned:
{"columnId":5562875589644164,"value":24271,"displayValue":"$24,271.00","formula":"=[Price]@row"}

What should I do in order for the API to return the desired rounded value of 24300.
Thanks,