Google-sheets – How to automatically add a row above a particular cell

formulasgoogle sheetsgoogle-sheets-arrayformula

I'm sending some data from Zapier to Google Sheets and every time Zapier creates a new row, all formulas from that row are gone. I'm looking for a script that would allow the following solution:

I set up Zapier to always update row no. 2 (Row 1 is for table headlines) => Once Zapier fills the data in this row, a script automatically creates a new row above it, pushing all the other rows one place lower (with formulas remaining in all cells in row 2).

Best Answer

Use ARRAYFORMULA in row 1 and calculate rest automatically regardless of newly inserted rows. Example:

Code

// global 
var ss = SpreadsheetApp.getActive();

function onOpen() {
  var menu = [{name:"Add New Last Row", functionName:"addRow"}];
  ss.addMenu("Extra", menu);
}

function addRow() {
  var sh = ss.getActiveSheet(), lRow = sh.getLastRow(); 
  var lCol = sh.getLastColumn(), range = sh.getRange(lRow,1,1,lCol);
  sh.insertRowsAfter(lRow, 1);
  range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {contentsOnly:false});
}

Remark

Setting the contentOnly to false will yield a standard copy. Setting it to true, will paste only values. The example script you found, does way more then pasting values.....

Example

I've created an example file for you: Add Row With Formula's

Google-sheets – How to anchor all the cells on the page in Google Spreadsheet

Whether or not this is practical may depend on the formulae you are using (which you don’t show) but for something very simple like =Sum(A1:B2) you might switch to View, All formulae and replace ( with ($ and : with :$. Then transpose, repeat the process and transpose back. Otherwise “Search using regular expressions” may help.

Best Answer

Related Solutions

Google Sheets – Automatically Insert New Row with Functions/Formulas

Code

Remark

Example

Google-sheets – How to anchor all the cells on the page in Google Spreadsheet

Related Topic