Google sheet auto populate row script-CodePudding

I am using a google sheet in which a new row is added by inserting Col A thru a third party app. Once Col A is added I wish to copy the formula from the remaining row cells above to auto calculate the values using the formulas in that row. ArrayFormula doesn't help as I don't want to have the rows created in advance before Col A is populated. Any script which can do this?

CodePudding user response：

What you are looking for can be achieved with a formula. I have created a quick sheet to demonstrate this.

Cell B1 contains the following formula:

={"Formula";ifna(arrayformula(filter(A2:A, A2:A<>"")*5))}

This formula defines the header (meaning that it is placed in the first row, ultimately not affecting any third party data inputs), and then uses an array formula for the rest of the column. This formula works because of the filter. That filters out any cells that are not blank and multiplies them by 5. The IFNA portion ensures that if there is no data at all, the second row is still left completely blank. This was verified with a script I wrote to simulate a data entry.

function test() {
  SpreadsheetApp.getActive().getActiveSheet().appendRow([Math.random()]);
}

This script is simple, and uses the appendRow() method to simulate what a third party entry would do. I used this script, and the result is displayed in the screenshot above. This shows that an appended row will still populate the next blank row, despite there being an array formula in cell B1. Using this logic, it is possible to change the formula to populate the actual data that you need.