Can anyone help, please? Error on update formulas in cells with API

Hello!

I am using your SmartSheets JavaScript SDK: https://github.com/smartsheet-platform/smartsheet-javascript-sdk for automate some process in sheets.

But when i try to set/update formula in cells, i always get an error ‘#UNPARSED’ inside of cell in browser view. If i try to double click on this cell and then skip selection, i get a fully works formula, what me need.

Screenshot after app depend formula with API: https://i.ibb.co/Y8BrJzL/image-preview.png

And then, after double click and skip selection: https://i.ibb.co/HgwCJQ7/image-preview-1.png (formula works)

Formula update example on Node.js: https://gist.github.com/TTATPuOT/e2f58b0bd2805d40180a436de7e9f397

Find more posts tagged with

API and Developers

Comments

Shaine Greenwood

UPDATE: For anyone else who happens upon this community thread, my post wasn't the cause of the issue. See the post marked as Best Answer to know how this was resolved!

Hello—

From looking at your code, it appears you're using accent marks instead of straight quotes. Try switching to straight quotes:

id: rowId,

cells: [

{

columnId: columnId,

formula: "=IF(Правок${row.rowNumber} < 2; [Обработано кадров]${row.rowNumber} * 10 + [Из них с вырезкой]${row.rowNumber} * 10; ([Обработано кадров]${row.rowNumber} * 10 + [Из них с вырезкой]${row.rowNumber} * 10) * (1,1 - 0,1 * Правок${row.rowNumber}))"

If that doesn't work, the next thing that you might troubleshoot is when and how your function is being called. Looks like you've made this function asynchronous, meaning anything after it will be executed immediately.

If this function updates your row with the formula and this happens before your columns are named, or before data that the formula is referencing enters other cells, you may receive this error. You may want to call this function later in your script or consider not making it asynchronous.

Otherwise—contact our Support team to troubleshoot further.

detsad

i wrote support team twice. without answer for two weeks

dAVE Inden

It looks like you are using Smartsheet set in the Russian language. When you have your language set to one we are localized in you can write formulas using separators for that language. With Russian you can write formulas using a comma for the decimal separator and a semi-colon for separating the phrases of your formula. This localization to your language happens in your browser when you work in Smartsheet.

The API is not localized in other languages and expects formulas to be entered using the English language with commas to separate parts of the formula and the decimal point for numbers. For example, a formula you would write in a cell in the browser would look like this:

=IF(Num1 > 1,5; [Num2]1; 0)

To add this formula via the API you would need to have it look like this:

=IF(Num1 > 1.5, [Num2]1, 0)

This will add the formula so that it doesn't give the #UNPARSEABLE error and then when you view it in your browser it will have the structure based on your language settings.

detsad

Thank you very much. Now its ok.

Для русскоговорящих. Замените точки с запятой на просто запятые, а разделители в числах на точки, и все заработает.

Sign in to join the conversation!

Quick Links

Trending discussions

Does a view-only user need a license to use the API?
Hello everyone, I will need to use Smartsheet API to ingest data from Smartsheet to an object storage. To do that, my client will create a view only account for me. If I understand correctly, to use the API the account needs to be licensed, so we will need to pay for this "service account". That is correct?
Using Control Center to Track Folder Updates?
Hello, I am unsure of the feasibility of this goal but am hoping someone could provide some insight. Within my organization we have the following structure Workspace → Workspace → Folder. Within this folder we have additional folders that are added through an API integration we have setup. I am hoping we would be able to…
Will and API token for a report give access to the Sheet the Report generated from?
Hi, I have a large Smartsheet, I want to generate a row report that reduces information displayed down to a specific group. For that Row Report, if I were to generate an API token and share it to a third party, will that third part have access to the information on the original 'Grid' sheet, or will it be reduced to what…