Google Sheets: automatically apply formula to new rows added at the top

Question

I have a simple question for which I have failed to find an answer, simple or otherwise!

My Spreadsheet looks like this (say)

   A       B      C
1  Name  Amount  Tax 
2  Neil    20      2
3  Rose    100     10

Now column C is calculated via a formula B/10. I want to insert a new row ABOVE ROW 2 (not at the end) and I want the formula to apply to that new row in column C.

I cannot find a way to do this automatically. I know how to

• manually copy the formula (but of course there are many columns I actually want to do this to) by copy and paste or grabbing the handle
• use arrayformula to extend the formula. The problem I have is that if I use arrayformula like this: arrayformula(B2:B/10) that's great if I add rows at the end. But when I insert a row, becoming the new row 2, what happens is that the formula remains linked with the original cell I entered and changes to arrayformula(B3:B/10) which is logical but not what I want because the new row 2 has no formula associated with it
• if I use arrayformula(B:B/10) then it applies to all rows including row 1 (the column headers) and gets very confused about position.

There must be a way, so I ask you, the internet for your assistance :-)

Answer 1

To ensure that the calculated fields in a Google Sheet are updated, especially when a Google Form is connected to the sheet, you need to create an App Script and set up a trigger for the script.

To do this, go to the 'Extensions' menu, select 'Apps Script', and open the code editor. In the code editor, write a function that contains the logic you want to use for updating the calculated fields.

Here is an example of what the code could look like:

 function FillFormulas() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); // get sheet by name
  var lastRow = spreadsheet.getLastRow(); // get last row
  var firstFormulaCell = spreadsheet.getRange("D2"); // get the columns that will be autocalculated
  firstFormulaCell.setFormula('=IF(B2="Yes",1,0)'); // set calculated field logic 
  var fillDownRange = firstFormulaCell.offset(0, 0, (lastRow - 1));  // get range of rows to fill 
  firstFormulaCell.copyTo(fillDownRange);  // final set
}

This function will append column D from column B. If the value in column B is 'Yes', the corresponding cell in column D will contain '1', otherwise it will contain '0'. To ensure that the function is working, select 'Save' and then press 'Run'.

After saving and running the function, go to the 'Triggers' section, located on the left side of the page.

To set up the trigger, click 'Add Trigger' and select the event that will trigger the function to run. If you want the trigger to run whenever the sheet is updated by a form submission, select 'On form submit' as the action

Answer 2

For C1

={"Tax";arrayformula(B2:B/10)}

Answer 3

An ARRAYFORMULA actually will work in this situation if you place it in cell C1 as follows:

=ARRAYFORMULA(IF(ROW(B:B)=1, "Tax", IF(ISBLANK(B:B), "", B:B/10)))

1. If the current row is the first, then make the value of the cell "Tax".
2. Else if it is blank, then leave it blank.
3. Otherwise attempt the tax calculation using B:B/10.

Answer 4

Here's a way to use Apps Script to insert a new row on top and copy the formula to the new row. It may take some customization depending on how complex your data is, but hopefully you get the idea.

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


function addFirstRow() {
    var firstRow = 2;
    var sh = ss.getActiveSheet();
    var lCol = sh.getLastColumn();
    var range = sh.getRange(firstRow, 3, 1, lCol);
    var formulas = range.getFormulas();
    sh.insertRowsAfter(1, 1);
    newRange = sh.getRange(firstRow, 3, 1, lCol);
    newRange.setFormulas(formulas);
}