Formula through API
Hello,
I'd like to make a kinda tricky formula write with the API. I don't know if it is possible but I guess it is. I just have no idea how to properly do it. Could someone land a hand on this one ? Here's the case:
I'm going to add rows in the sheet, each of them has two date type fields I'm interested in. I want the first column (which is the flag for 'At Risk') to be True if the first date (Column 5) is higher than the second (Column6). As the second date isn't filled every time, I don't want the condition to trigger if the second date field is empty.
Do you see a way to do this ?
Thank you !!
Comments
-
You can do this with a formula. First you would need to test if the cell in Column6 is a Date value first. The function for that is ISDATE(). If that returns true you could have it then test if the date in Column5 is greater than it.
I made a formula to place in the flag column like this:
=IF(ISDATE([Column6]1), [Column5]1 > [Column6]1)
That first checks for if the value in Column6 (in this case row 1) is a date or not. If it is then it evaluates if the Column5 is greater than Column6. That evaluates to true or false and will display a flag as needed.
Side note, this is more of a Smartsheet Formulas question than an API question. Providing a formula via the API is just a string. But, the syntax of a formula is the same as what one would write working in the UI. For other questions about creating formulas I would use the Formulas tag on your posts here.
-
Hello dAVE,
Thank you for your answer and sorry I wasn't here to respond this week-end. I understand the formula you wrote but I don't know how to write it in the API, because you put this 1 after the Column reference for line 1, but in the API, I don't know what are going to be my row indexes until I add the rows to the sheet.
Thank you again
-
If you won't know the row number at the time of entering the formula to the row you can use the @row reference in formulas. This will use the row number for the row the formula is found in automatically.
Your formula would look like this:
=IF(ISDATE([Column6]@row), [Column5]@row > [Column6]@row)
Adding a row to the bottom of your sheet your request body would look like this:
[
{
"toBottom": true,
"cells": [
{
"columnId": {{columnId}},
"formula": "=IF(ISDATE([Column6]@row), [Column5]@row > [Column6]@row)"
}
]
}
]You would just need to update the column names to match your sheet's columns and add in the columnId for the column where the formula would live. Add in other cell objects to the array as you need to add a new row with additional data and you can adjust the row location as needed for your process.
-
Oh I see, I didn't know about the @ reference. Thank you this is really helping !
-
No problem!
