Thursday 13 February 2014

Google Sheets script to automatically fill down formulas to last row

Update 10.03.2014 - since writing this script, I came across this post, which, by using arrays, is a much simpler and more elegant solution than a script. The script method could still have its uses, so am keeping this here as a reference

In Excel, I frequently use a VB script to fill down specific formulas in columns to the same length as in other columns. However, when using Google Sheets, I could not find a script or method that would allow me to do this - so I created the following script.  This also has the advantage of running on edit.

A quick demo of this in action.



The complete code is at the bottom of this page if you just want to copy and paste.  Ill explain the methodology though here first by breaking this down.
First, I added a new on-open menu item to allow the user to set this up

//Add the new menu on Open 
function onOpen() {
  var menuEntries = [{
    name: "Autofill Columns on Edit",
    functionName: "onEditTrigger"
  }];
  ss.addMenu("Auto Fill Menu", menuEntries);
}

This trigger function makes the function "onEditAutoFill" run on edit.
I also set some properties. This is used to define the columns and rows that the user will choose to do the fill on.  (I initially tried to declare global and local variables, but found that any subsequent changes did not persist, so discovered this method did the trick instead)

function onEditTrigger() {
  ScriptApp.newTrigger("onEditAutoFill").forSpreadsheet(ss).onEdit().create();
   ScriptProperties.setProperties({  //set zero properties, for use later to check if user has defined the fill down range already
   "start"     : "0",
     "end"     : "0",
     "frows"   : "0",
 });
  onEditAutoFill();   // let's just run this straight away to fill down any data
}


If the user has already defined the columns and rows already, then this part of the script takes the property values for those columns and rows, and finds the last row

var ss1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = ss1.getLastRow();
  var checker = ScriptProperties.getProperty('start'); 
  if (checker != "0") {   //Determine whether user has defined anything already
  Logger.log(checker);
  var startcolumn = ScriptProperties.getProperty('start');   //As this is already defined, take the User entered values
    var endcolumn = ScriptProperties.getProperty('end');  //As this is already defined, take the User entered values
    var numberrows = ScriptProperties.getProperty('frows'); //As this is already defined, take the User entered values

The script then gets the formulae from the first row, sets this as an array, and then pushes that array to the rows and columns it needs to fill down

  var rngVal = ss1.getRange(startcolumn + numberrows + ":"+ endcolumn + numberrows).getFormulasR1C1();// get an array of the first row
  var rowData = rngVal[0]
  var newData = []
  for(n=numberrows++;n<lastRow;++n){newData.push(rowData)}   
  ss.getRange(startcolumn + numberrows + ":"+ endcolumn + lastRow).setFormulasR1C1(newData);//write back this array
}

However, if the properties are still zero, meaning the user has not already defined the columns and rows to use, this bit runs instead, collecting the user values and writing them to the properties, before the remainder of the script fires and uses those values



else{     //We need to get the User Values
  var startcolumn = Browser.inputBox('Enter Start Column Letter', Browser.Buttons.OK_CANCEL);
    var endcolumn = Browser.inputBox('Enter End Column Letter', Browser.Buttons.OK_CANCEL);
    var numberrows = Browser.inputBox('Which Number row do you want to start your fill from', Browser.Buttons.OK_CANCEL);
    ScriptProperties.setProperty('start', startcolumn);  //Set the properties to the User entered values
     ScriptProperties.setProperty('end', endcolumn);  //Set the properties to the User entered values
     ScriptProperties.setProperty('frows', numberrows);  //Set the properties to the User entered values
  var rngVal = ss1.getRange(startcolumn + numberrows + ":"+ endcolumn + numberrows).getFormulasR1C1();// get an array of the first row
    Logger.log(rngVal);
  var rowData = rngVal[0]
  var newData = []
  for(n=numberrows++;n<lastRow;++n){newData.push(rowData)}
  ss.getRange(startcolumn + numberrows + ":"+ endcolumn + lastRow).setFormulasR1C1(newData);//write back this array to the defined range
  }
}


The complete script is here

var ss = SpreadsheetApp.getActiveSpreadsheet()

//Add the new menu on Open 
function onOpen() {
  var menuEntries = [{
    name: "Autofill Columns on Edit",
    functionName: "onEditTrigger"
  }];
  ss.addMenu("Auto Fill Menu", menuEntries);
}
  
function onEditTrigger() {
  ScriptApp.newTrigger("onEditAutoFill").forSpreadsheet(ss).onEdit().create();
   ScriptProperties.setProperties({  //set zero properties, for use later to check if user has defined the fill down range already
   "start"     : "0",
     "end"     : "0",
     "frows"   : "0",
 });
  onEditAutoFill();   // let's just run this straight away to fill down any data
}

function onEditAutoFill() {

var ss1 = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = ss1.getLastRow();
  var checker = ScriptProperties.getProperty('start'); 
  if (checker != "0") {   //Determine whether user has defined anything already
  Logger.log(checker);
  var startcolumn = ScriptProperties.getProperty('start');   //As this is already defined, take the User entered values
    var endcolumn = ScriptProperties.getProperty('end');  //As this is already defined, take the User entered values
    var numberrows = ScriptProperties.getProperty('frows'); //As this is already defined, take the User entered values
    
  var rngVal = ss1.getRange(startcolumn + numberrows + ":"+ endcolumn + numberrows).getFormulasR1C1();// get an array of the first row
  var rowData = rngVal[0]
  var newData = []
  for(n=numberrows++;n<lastRow;++n){newData.push(rowData)}   
  ss.getRange(startcolumn + numberrows + ":"+ endcolumn + lastRow).setFormulasR1C1(newData);//write back this array 
}
  else{     //We need to get the User Values
  var startcolumn = Browser.inputBox('Enter Start Column Letter', Browser.Buttons.OK_CANCEL);
    var endcolumn = Browser.inputBox('Enter End Column Letter', Browser.Buttons.OK_CANCEL);
    var numberrows = Browser.inputBox('Which Number row do you want to start your fill from', Browser.Buttons.OK_CANCEL);
    ScriptProperties.setProperty('start', startcolumn);  //Set the properties to the User entered values
     ScriptProperties.setProperty('end', endcolumn);  //Set the properties to the User entered values
     ScriptProperties.setProperty('frows', numberrows);  //Set the properties to the User entered values
  var rngVal = ss1.getRange(startcolumn + numberrows + ":"+ endcolumn + numberrows).getFormulasR1C1();// get an array of the first row
    Logger.log(rngVal);
  var rowData = rngVal[0]
  var newData = []
  for(n=numberrows++;n<lastRow;++n){newData.push(rowData)}
  ss.getRange(startcolumn + numberrows + ":"+ endcolumn + lastRow).setFormulasR1C1(newData);//write back this array to the defined range
  }
}
I am still learning my way with Google Scripts, so if anyone has any improvements to the code, or an easier way to do the same functionality, I would be very happy to know.

12 comments:

  1. What a great find! Thank you!

    ReplyDelete
  2. Can I specify that the fill down formulas activate for 1 column only?
    Apologies as I'm a non-coder.
    Thanks.

    ReplyDelete
    Replies
    1. Hi, sure, the script allows you to do this for one column only. You specify this when the script runs for the first time via the dialogue box that it shows

      Delete
    2. to a non-coder, this is magical !
      much appreciated :)

      Delete
    3. A pleasure :-) Glad it is of use

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Thanks for this code! I cant seem to get this to run automatically. Do I have to use the menu everytime a new row is populated with data? It's being populated from an external source so maybe that's why?

    ReplyDelete
    Replies
    1. The script sets an on edit trigger, so that every time you change anything on the sheet it fills down. If you are using an importrange or similar to bring in data, then I guess this solution is not going to work for you. My recommendation would be to use an array formula instead to automatically fill down formulae - this will work with external data refeshes

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Good day! Thank you very much for the script!
    Could you please advise whether it possible
    1. To use only some columns like D, I, Z, AA but not from D to AA.
    2. After the calculation, from time to time, I need to update with value only instead of formula, but currently script remove all values anp calculate formulas.
    Thanks you so much!!!!!

    ReplyDelete