Need to add a formula to a column when a new row is created

cnikkih

TL;DR - I need to autopopulate a formula into a cell when a new row is created without locking the formula to the column, and automations are crapping out on me.

As the title states, I need to create an automation where a formula is added to a cell when a row is added. I cannot simply lock the column to the formula because there are also instances where our team needs to be able to enter a value other than what is produced by the formula. (Basically, we want them to be able to enter their own info ONLY if it does not fit any of our criteria.)

When I first set up the automation, that column had been set to be a single-item drop-down list. It appeared to work like a charm at first. However, as we began to fill the sheet, the other values listed in the column filled the Values list and suddenly, it would only populate about half the formula as text (see Image1). As I played around with it, I realized that column should really be Text, so I changed that, and now the whole formula displays, but as text, not a formula (See Image2). I can then take that text and copy/paste it back into the same cell, and it realizes it's a formula and works fine, but that doesn't work well because...

Another issue is that it takes FOREVER for the formula to display at all now. When I first set this up, I could add a row, hit save, and the formula worked pretty much instantly. Right now, it is taking more than 2 minutes to finally pop up (even though it is wrong). Our users shouldn't have to wait that long for the formula to appear. The sheet is not large - we've been using it for a month and there are only 19 entries & 12 columns. There are no other formulas or images or attachments that would make the sheet so large that it would take that long. Help!

Jeff Reisman

@cnikkih

Unfortunately you cannot use Change Cell Value automation to populate a cell with a formula. So that is out. I have found a sort-of workaround for your situation though:

Set up a helper text column you can use for when your users need to populate a different value than what your formula would populate. Call it "Manual Path" or something like that.

Then, in your Path column, create an IF that looks for a value in the Manual Path column. If it finds a value in the cell, use that value in the Path column, otherwise, execute the formula. An example:

=IF(ISTEXT([Manual Path]@row), [Manual Path]@row, IF(ObjectType@row = "shell script", ...

Basically, this overrides the rest of the formula if there's a value in Manual Path.

Make it a column formula, and you're all set.

cnikkih

Thank you, Jeff! I appreciate your quick and helpful response. I'll implement this and let the team know.