Apostrophe added to formula that is pushed from Zapier

Cheryl Lock

We’re using a sheet for ticket tracking. Issues come in via a form, manual entry, or the Gmail Add-on. We’re using hierarchies to track To Dos as tasks. I use a simple formula to tell me if a row is a task or a parent.

=COUNT(ANCESTORS())

I use the value for formatting and for accurate issue counts as I only want to count parents. New Issues saved down from Gmail can only be added to the bottom of the sheet.There are no other options. So if the previous last row was indented because it’s a task, the formula won’t copy down.

I’ve been trying to use Zapier to push the formula if the field is blank. If I try to format the formula as a number in Zapier it errors, so I just typed the formula into the update row action. Smartsheet is breaking the formula by adding a ‘ in front: '=COUNT(ANCESTORS())

The field in Smartsheet is a text/number field. I have an alert that notifies me if that field is blank so I can manually pull the formula down, but that isn’t practical long term.  

Has anyone figured out a better way?

Paul Newcome

If you are only tracking parent vs child rows and not necessarily the exact hierarchy... Have you tried using a checkbox column and simply passing a 1 or true value to check the box?

Cheryl Lock

Thanks Paul, that's an interesting idea. Thinking through that, I don't know whether to pass a 0 or a 1. Do you have any ideas how I'd figure out if something is a parent or child without a Smartsheet based formula? I can't figure out anything I could pass into Zapier that could be used to determine that so I know which value to pass?

Cheryl Lock

Update: I just spoke with Smartsheet support and they said they currently don't have any alternative methods to input a formula to newly added rows if the previous rows are indented and the new rows are not. Doesn't seem like automating this is possible for my use case. So I've implemented a manual checkbox that my team can use to format tasks, and I'll report off that checkbox for my counts.