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

edited 12/09/19 in API & Developers
03/02/19 Edited 12/09/19

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



 

Popular Tags:

Comments

  • Shaine GreenwoodShaine Greenwood Employee
    edited 04/15/19

    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.

  • i wrote support team twice. without answer for two weeks 

  • dAVE IndendAVE Inden Employee

    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.

     

  • Thank you very much. Now its ok. 

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

Sign In or Register to comment.