• Amit Sarda

Automatic Calculations in Google Sheets

Updated: Dec 21, 2019

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.

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

#automaticcalculations #automation #GoogleSheets #formulas #googlesheets #googlescripts #spreadsheets #Excel

 
  • LinkedIn
  • Twitter
  • Instagram

©2020 by Amit Sarda