Automatically apply formulas to new rows in Google Sheets via Zapier

Other Questions Zapier
1

I’ve got a Zapier setup that adds new rows to my Google Sheet based on incoming emails. It’s working great for columns A, B, and C, which get filled with email info, original message, and date/time.

My problem is with column D. I’ve got a formula there that I want to apply to every new row automatically. But it’s not happening. The last row (18) has column D empty because the formula didn’t copy down.

I’ve tried using array formulas and other tricks, but nothing seems to work. Google keeps pushing my formula row down instead of applying it to the new data.

Any ideas on how to make this work? I really need that formula to apply to all new rows as they come in from Zapier.

Here’s a quick example of what my sheet looks like:

| A (Email) | B (Message) | C (DateTime) | D (Formula) |
|-----------|-------------|--------------|-------------|
| [email protected]   | Hello       | 2023-05-01   | =STUFF(B1)  |
| [email protected]   | Hi there    | 2023-05-02   | =STUFF(B2)  |
| [email protected]   | Greetings   | 2023-05-03   |             |

That last row is what I’m trying to fix. How can I get that formula to apply automatically?

2

hey, have u tried using a script instead? u can set up a simple google apps script that runs on edit and applies the formula to new rows. it’s pretty easy to do and works great with zapier. just create a script in tools > script editor and set it to trigger when the sheet is edited. lemme know if u want help setting it up!

3

I’ve faced a similar issue with Zapier and Google Sheets, and I found a workaround that might help you out. Instead of relying on Google Sheets to automatically apply the formula, you can use Zapier to add the formula directly when creating the new row.

In your Zap, after the step that creates the new row in Google Sheets, add a new step using the ‘Update Spreadsheet Row’ action. Set it up to update the same row you just created, but this time include the formula in column D.

For example, if your formula is =STUFF(B{row_number}), you can use Zapier’s built-in functions to dynamically generate the correct formula for each new row. Something like this:

=STUFF(B{{row_number}})

Zapier will replace {{row_number}} with the actual row number when it runs.

This approach ensures that every new row gets the formula applied immediately, without relying on Google Sheets to propagate it. It’s a bit more setup in Zapier, but it’s solved this problem reliably for me in the past.

4

I’ve encountered this issue before, and there’s a reliable solution that doesn’t involve changing your Zapier setup. In Google Sheets, you can use the ARRAYFORMULA function to automatically apply your formula to new rows.

Try replacing your formula in D1 with something like this:

=ARRAYFORMULA(IF(ROW(B:B)=1,“Formula”,IF(ISBLANK(B:B),“”,STUFF(B:B))))

This formula will:

  1. Apply to the entire column D
  2. Put “Formula” as the header in D1
  3. Apply STUFF() to all non-empty cells in column B
  4. Leave blank cells when column B is empty

This should solve your problem without needing to modify your Zapier workflow. The formula will automatically apply to new rows as they’re added by Zapier.