How to automatically repeat formulas when a new row is added in google sheets?

user_18072078 0
Viewed 1115

I'm fairly new to Google Sheets. I followed a youtube video about adding an Add Row button using appscript.

It worked, however, upon adding a new row, the formulas aren't automatically added, I had to copy it manually every time.

The following is the script I used for the button:

function addRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = ss.getActiveRange();
  var R = range.getRowIndex();

  sheet.insertRowsBefore(5, 1);
}

I'd greatly appreciate it if you could help me. Thanks!

google-sheets
2 Answers

In addition to Mike's answer. Yes you can update your normal formula to add the use of arrayformula.

To use arrayformula, instead of setting the formula for each cell, you just have to apply it to one cell and inside the formula you will set the range instead of the single cell.

Example scenario: enter image description here This is how you would normally do the formula =if(D2>75,"Passed","Failed") where you check only the cell specific cell D2, then you apply or copy individually to the following rows.

However, using arrayformula you just set the formula in that single cell and inside it you call the range to apply the formula to, on this example is D2:D4. This will now be your new formula using arrayformula =arrayformula(if(D2:D4>75,"Passed","Failed")). It will automatically expand to the rows below until the range covered in the arrayformula. The formula will also be applied to added rows. enter image description here

Take note that this will cause an error if there's data on the rows below so be sure to clear it. enter image description here

Try it out on your formula and comment if it didn't work.

user_184002720

By using arrayformula, the new row will inherit the formulas.

If your formula is for instance =if(D2>75,"Passed","Failed"), I recommand to put the arrayformula in row#1 as following

={"title of column";arrayformula(IF(D2:D="",,if(D2:D>75,"Passed","Failed")))}

enter image description here

user_150193800
Related