Automatic Calculations in Google Sheets

If you have new data flowing into your Google Sheets regularly, this blog post is for you! Many a time, you will have additional columns in your data sheet, running calculations based on the raw data being imported in your Google Sheet.

Example

The simplest, and probably the lamest, example is combining the first name and the last name of a person 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.

Spreadsheet with First Name and Last Name, and a formula that combines these fields to create Full Name
A formula in column C combines First Name and Last Name fields to create Full Name

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.

Solution

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.
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);

}

House Keeping

  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. You can also add a custom menu to your GoogleSheet where you can list these functions to allow them to run manually. In this example, function ‘Fill Formulas’ will show up in a top-level menu called ‘Genius’.
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Fill Formulas', functionName: 'FillFormulas'},
];
spreadsheet.addMenu('Genius', menuItems);
}

Hit the subscribe button below if you would like to be notified about such new posts.

One thought on “Automatic Calculations in Google Sheets

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s