Copy Formulas Down

In many spreadsheets, you have data in a few columns, and formulas that work with this data in other columns. When you add more rows, you have to copy the formulas to each row. This script automates copying the formulas to new rows.

You can use the script as a library in your project. The project key is MSqCbU8hTkNuB2nTncoeP0HPfFjUr1CYB. You can also copy and paste the code directly in your own script, see the source code.

The script has only one public function, copyFormulasDown(Sheet sheet, int sourceRow), as documented below.

You can create a menu entry and copy the formulas from the menu:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Copy formulas",
    functionName : "copyFormulas"
  }];
  sheet.addMenu("Scripts", entries);
};

function copyFormulas() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var row = 2;
    CopyFormulasDown.copyFormulasDown(sheet, row);
}

You can take it one step further by using an installable trigger, so the copyFormulas() function from the code above is called automatically when you edit the spreadsheet or submit a form.

copyFormulasDown(Sheet sheet, int sourceRow)

Copy formulas from the a source row in a sheet to rows below

Arguments:

Name Type Description
sheet Sheet Google Spreadsheet sheet
sourceRow int (optional) 1-based index of source row from which formulas are copied, default: 2



Comments