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.