Overview

There are two main situations:

1. How to replicate the number of rows inserted
2. How to set the corresponding formula to each cell

1. How to replicate the number of rows inserted

One alternative among others is to use the Change Google Sheets installable event instead of OnEdit. The Change event has the property changeType. Your project could use it to know if rows were inserted to the bounded spreadsheet. To get this event you should create a function and add it as an installable trigger.

Below there is a sample code and the instructions to manually add an installable trigger to a Google Apps Script bound to an spreadsheet project.

You could add the code to add the formulas to each corresponding cell after the rows were inserted in the 'Overall_Formulas' sheet.

Code

// Global variables var LEADER_SHEET = 'Raw_Data'; var FOLLOWER_SHEET = 'Overall_Formulas'; function replicateRowInsertion(e){ var ss = SpreadsheetApp.getActiveSpreadsheet(); if(e.changeType == 'INSERT_ROW'){ var leaderRange = ss.getActiveRange(); var rows = leaderRange.getNumRows(); if(leaderRange.getSheet().getName() == LEADER_SHEET){ var sheet = ss.getSheetByName(FOLLOWER_SHEET); sheet.insertRows(leaderRange.getRow(),rows); } } } 

Instructions

Installable triggers could be added, manually or automatically. In order to keep this answer short, only the manually instructions are included. Take a look to the source to learn the automatically instructions and other important details like restrictions, kind of installable triggers, limitations, etc.

From Installable Triggers - Apps Script

Managing triggers manually

To manually create an installable trigger through a dialog in the script editor, follow these steps:

1. From the script editor, choose Resources > Current project's triggers.
2. Click the link that says: No triggers set up. Click here to add one now.
3. Under Run, select the name of function you want to trigger.
4. Under Events, select either Time-driven or the Google App that the script bound to (for example, From spreadsheet).
5. Select and configure the type of trigger you want to create (for example, an Hour timer that runs Every hour or an On open trigger).
6. Optionally, click Notifications to configure how and when you will be contacted by email if your triggered function fails.
7. Click Save.

2. How to set the corresponding formulas to each cell

One alternative is to add a third sheet to hold the formulas to be copied and pasted to the Overall_Formulas sheet. Doing this, maintaining the formulas to be added to new rows adding will be more easy than updating formulas included in the code and just require few additional lines to the previous code.

Code

The following code assumes that the sheet Template holds the formulas in the second row.

// Global variables var LEADER_SHEET = 'Raw_Data'; var FOLLOWER_SHEET = 'Overall_Formulas'; /* Global variables to idenfify the location of the formulas to be copied and pasted */ var SOURCE_SHEET = 'Template'; var SOURCE_RANGE = '2:2'; function replicateRowInsertion(e){ var ss = SpreadsheetApp.getActiveSpreadsheet(); if(e.changeType == 'INSERT_ROW'){ var leaderRange = ss.getActiveRange(); var rows = leaderRange.getNumRows(); if(leaderRange.getSheet().getName() == LEADER_SHEET){ var sheet = ss.getSheetByName(FOLLOWER_SHEET); sheet.insertRows(leaderRange.getRow(),rows); // Copy and paste the formulas var sourceRange = ss.getSheetByName(SOURCE_SHEET).getRange(SOURCE_RANGE); var i = leaderRange.getRow(); for(i; i < leaderRange.getRow() + rows; i++ ){ sourceRange.copyTo(sheet.getRange(i + ':' + i)); } } } }