You’re not always going to be able to drag a formula down a column in Google Sheets.

Sometimes, you need Google Sheets to automatically apply the formula to new rows.

If you’re entering data into a Sheet manually, the technique might save you a few seconds. But if you’re using automation to add rows to a Sheet, this technique becomes essential.

That scenario comes up a lot for people using GMass’s recurring campaigns feature.

You add new rows with new contacts to a Google Sheet. (Or, even better, the machine add new rows for you via a web form, Zapier integration, or other automation.)

You want to apply a formula to those rows. But you can’t drag the formula down to those rows ahead of time — or else they aren’t “new rows” anymore.

Fortunately, there are a few different functions in Google Sheets that can automatically apply a column’s formula to any new rows. And I tested them all out to find the most versatile one for you to use in your spreadsheets.

You won’t even have to use a Google Apps Script to make it happen; the formulas are built right into Google Sheets.

Here’s how to use them.

Apply a Formula to a New Google Sheets Row: Table of Contents

• Google Sheets: Automatically Add a Formula to a New Row
• Using Your Google Sheet in a Recurring Email Campaign
• The Power of the GMass + Google Sheets Connection

Google Sheets: Automatically Add a Formula to a New Row

Let’s say you have a Google Sheet of prospects for a cold email campaign.

And every time you add a new prospect, you want to automatically do two things:

1. Calculate an estimate of what they’re spending on job postings.
2. If you’ve created a report for that prospect, build a link to a custom report which you can then mail merge into your email.

Here’s how my Google Sheet looks:

Now I’ll cover the best Google Sheets technique you can use to automatically apply a formula to every new row in a column.

I’ve found this works better across different situations than most of the other options you might see someone recommend (some combo of ARRAYFORMULA, FILTER, and VLOOKUP).

Using MAP and LAMBDA

In column F of my spreadsheet, I’m doing something pretty simple: Multiplying column D by column E.

I’ve entered this into cell F1.

=MAP(F:F,LAMBDA(x, IF(ROW(x)=1,"EstimatedTotal",IF(AND(INDEX(D:D,ROW(x))<>0,INDEX(E:E,ROW(x))<>0),INDEX(D:D,ROW(x))*INDEX(E:E,ROW(x)),""))))

Here’s a breakdown of how it works:

• MAP applies a function to each row in the range
• LAMBDA allows you to create a custom function
• The initial IF command checks if it’s the header row and, if so, applies the header text
• The next IF command checks if the column is blank
• Then with the AND, we make sure both indexes aren’t zero
• If both check out, INDEX finds the values in the right columns for the current row and multiplies them

And here’s my result:

Concatenating with the MAP and LAMBDA combo

With the MAP/LAMBDA combo you can also apply other, more involved formulas. In this case, I’m going to use the CONCATENATE function to create a personalized link.

For that, I use this formula in cell G1.

=MAP(G:G,LAMBDA(x, IF(ROW(x)=1,"ReportURL",IF(ISBLANK(INDEX(C:C, ROW(x))), "", CONCATENATE("<a href=",CHAR(34),"https://mysite.com/report?", INDEX(C:C, ROW(x)),CHAR(34),">Get your custom report</a>")))))

With that formula, if there’s an entry in the ReportID field for the row, this will automatically create a HTML-wrapped link which I can then use in an email campaign.

For reference, CHAR(34) is our way of adding quotation marks around the URL.

The result:

And whenever I add a new row, Google Sheets automatically applies the formulas.

You should be able to adapt these formulas based on your specific needs.

But if not, share the formula with your AI tool of choice and tell it what you want to do.

The AI tools generally have trouble creating these complex queries from scratch, but since you’re getting them most of the way there, they should be able to help.

Using Your Google Sheet in a Recurring Email Campaign

Now that your Google Sheet is set up to automatically apply formulas to new rows, you can use it in a recurring email campaign.

(I’ll assume you have GMass set up already for this demo. If not — it takes a few minutes at most to get started. Download the Chrome extension here, then you can check the quickstart guide if needed.)

Connecting to your Google Sheet

In Gmail, click the spreadsheet icon next to the Gmail search bar to bring up the Google Sheets connection box.

Then choose your Google Sheet from the dropdown.

You can use any of the optional settings if you want. If you choose to update with reporting data, those columns will go at the end of your Google Sheet.

Compose your email using your merge tags

You can now compose your email in the regular Gmail compose window.

To use your Google Sheet for mail merge, type a left curly brace { and that will bring up all your column headers.

Note: I also used GMass’s conditional content feature here. I’m checking to see if that person’s row has a ReportID. If so, I include the link to the report. If not, I tell them I could put together a report.

Setting this up as a recurring campaign

Open the GMass settings by clicking the arrow next to the GMass button.

Open the Schedule section.

Then under Repeat, you can set your recurrence frequency.

GMass will check your connected Google Sheet at the interval you choose. And if there are new rows, GMass will automatically send your campaign to those new contacts.

Thanks to the way we automatically applied formulas to new rows, those contacts will get complete emails — no merge tags missing.

Adjust any other settings and set the campaign live

You can now adjust any other settings for your campaign (adding auto follow-ups, adding triggered emails, scheduling, and so on).

You also may want to send yourself a test of the email, to make sure all your merge tags look right.

Here are my test results — all looks perfect.

And when you’re ready to send your campaign to the initial batch of prospects on your Google Sheet, hit the red GMass button in the compose window to set the campaign live.

The Power of the GMass + Google Sheets Connection

GMass’s native integration with Google Sheets allows you to do some pretty incredible stuff when it comes to personalizing email campaigns.

You have so much more control than you would with a standard CRM — since Google Sheets gives you the power to calculate, style, and prep data in a near-infinite number of ways.

And with techniques like automatically applying formulas to new rows, like we covered in this article, the GMass + Google Sheets connection becomes even more powerful.

So… if you haven’t tried out GMass for your email campaigns yet, you really should. Whether you’re sending cold outreach, email marketing, mail merges, something else, or all of the above, GMass is the ideal solution.

You can do complex things — but you’re doing them inside Gmail and Google Sheets, two apps you already know and understand.

Join the 300,000+ other people currently using GMass (who’ve given it more than 7,600 glowing reviews on the Chrome Web Store).

You can started by downloading the Chrome extension — there’s no credit card or even web form required for the free trial — and you’ll be sending mail merges in a matter of minutes.