Formulas in Automations

Andy_B

Is it possible to create an automation that will populate a cell with a formula instead of text or a drop down value?

I am trying to get a column to populate with the name of a user who checked a box. For the action block of the automation, I selected Change Cell Value in Column to

=([Modified By])@row

The automation works, but it treats this as a text output and populates the cell with an apostrophe preceding the equation:

'=([Modified By])@row

Any suggestions on how to get this to insert the actual formula and have it function as a formula?

SoS | Dan Palenchar

Hello @Andy_B,

You cannot do this with an automation because as you experienced it only populates text values 😕. There are two ways to do this, (1) a somewhat complex combination of helper columns/ copy row automation/ and lookup formula and (2) the API.

Native Solution (#1)

Disclaimer: I built this solution quite a while ago (years) and am writing it by memory. It is tricky so there might be something missing but this is what I remember.

What you need to do is:

1. Make sure you have a unique identifier in your Sheet such as Row ID.
2. Add a new column in your Sheet and input formula =[Modified By]@row. This exists to duplicate the Modified By value for reasons you'll see later. I'll call this column Modified By*
3. Set up a copy row automation to copy the row to some other Sheet based on whatever trigger you need.
4. Add a lookup formula such as INDEX(MATCH()) to pull in the Modified By* value based on Row ID.

If I remember everything correctly what happens is that when you copy the row the Modified By value will be set to automation@smartsheet.com, but the duplicated value will have the user.

API (#2)

If you know how to code you can set this up with the API. This is something we have developed at School of Sheets and can implement for you if you like.

***

Hope this helps!

SoS | Dan Palenchar

Hello @Andy_B,

You cannot do this with an automation because as you experienced it only populates text values 😕. There are two ways to do this, (1) a somewhat complex combination of helper columns/ copy row automation/ and lookup formula and (2) the API.

Native Solution (#1)

Disclaimer: I built this solution quite a while ago (years) and am writing it by memory. It is tricky so there might be something missing but this is what I remember.

What you need to do is:

1. Make sure you have a unique identifier in your Sheet such as Row ID.
2. Add a new column in your Sheet and input formula =[Modified By]@row. This exists to duplicate the Modified By value for reasons you'll see later. I'll call this column Modified By*
3. Set up a copy row automation to copy the row to some other Sheet based on whatever trigger you need.
4. Add a lookup formula such as INDEX(MATCH()) to pull in the Modified By* value based on Row ID.

If I remember everything correctly what happens is that when you copy the row the Modified By value will be set to automation@smartsheet.com, but the duplicated value will have the user.

API (#2)

If you know how to code you can set this up with the API. This is something we have developed at School of Sheets and can implement for you if you like.

***

Hope this helps!