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!

CodePudding user response：

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

CodePudding user response：

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: 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.

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

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

• You can also refer to this link for alternate solutions, take a look on it and use whatever works for you. https://webapps.stackexchange.com/questions/47171/how-to-automatically-insert-a-new-row-and-retain-functions-formulas-from-last-ro