How to automatically apply formulas to new rows in Google Sheets

Introduction

If you have new data flowing into your Google Sheets from different programs regularly, this blog post is for you!

This post shows you how to automatically apply formulas to new rows in Google Sheets, or in other words, how to automatically fill down entire columns in Google Sheets without dragging.

You will often have additional columns in your datasheet, running calculations based on the raw data being imported into your Google Sheet.

Think of how you send contact form submissions to a Google Sheets workbook, for instance. To make it work, you may want to know how to apply a formula to new rows in Google Sheets automatically.

Learn how to create barcodes in Google Sheets.

Example of a formula to automatically apply to new rows in Google Sheets

The simplest example is combining a person’s first name and last name to generate their full name. So, the raw data columns are First Name and Last Name, and a calculation you’re going to run is one that combines the two with a space in the middle.

If you’re fairly new to spreadsheets, you will be advised to ‘extend’ the formula in column C to cover the entire data every time you have new rows in your dataset. But when there are multiple people using the sheet, and if they are not as savvy as you are, and if their work depends on the calculated columns, it is imperative that you think of a solution that automates this.

In this case, you will want to automatically apply the formula “A2&” “&B2 to all the newly added rows in column C.

Apply formulas to new rows using Google Apps Script

Thankfully, Google Sheets have Scripts, a lot like VBA in Excel. And you can automate extending the formulas to the newly added rows in your data.

1. Go to Tools in the main menu and select Script Editor.
2. Create a function FillFormulas.
3. The formula in column C is =A2&” “&B2.
4. The column number for column C is 3, and the row the formula will be first pasted in is row #2.

Code Snippet to apply formulas to new rows in Google Sheets

function FillFormulas() {   

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');   

var lastRow = spreadsheet.getLastRow();   

spreadsheet.getRange("C2").setFormula("=A2&\" \"&B2");   
var fillDownRange = spreadsheet.getRange(2,3,(lastRow-1));   spreadsheet.getRange("C2").copyTo(fillDownRange); 

}

Other important points to consider

1. Use escape character ‘\’ when double quotes are present in formulas. In this example, we used them for the space between the first name and the last name.
2. Time-bound Triggers can be added from the Edit menu to run as frequently as every minute. In this example, you can select this function, FillFormulas to run every 15 minutes.
3. If you want to repeat this for multiple columns, just keep repeating the last 2 lines with the correct references to respective columns and formulas.
4. You can also add a custom menu to your Google Sheets workbook where you can list these functions to allow them to run manually. In this example, the function ‘Fill Formulas’ will show up in a top-level menu called ‘Genius’.
5. You could also consider using Array Formulas to automatically apply formulas to new Google Sheets rows. But you will find that it is pretty computation-heavy and slows down sheets if several edits are made.

Code Snippet to add the custom scripts to Menu in Google Sheets

function onOpen() {
   var spreadsheet = SpreadsheetApp.getActive();
   var menuItems = [
     {name: 'Fill Formulas', functionName: 'FillFormulas'},
   ];
   spreadsheet.addMenu('Genius', menuItems);
 }

Summary

You’d come across many such cases when you want to apply formulas to new rows in Google Sheets workbook when new rows are added to your data tabs in Google Sheets. You can do this for multiple columns if there are multiple calculations involved.