How do I quickly add new rows to a Google Sheet while applying existing columns' formulae?

Question

Normally, adding a new row to a Google Sheet only preserves formatting. It does not automatically adapt & apply previous columns' formulae to the new row. How do I quickly add new rows while also retain existing formulae (based on the bottom-most row)?

Cloud Cloud · Accepted Answer · 2020-02-21T23:38:18

A Google Apps Script can be used.

From your spreadsheet, open the "Tools" menu and click on "Script Editor". This will create a container-bound script that is linked to your sheet and will open that script project in a new tab.

Paste the following code into the default "Code.gs" file's editor:

let spreadsheet = SpreadsheetApp.getActive();

function onOpen() {
  spreadsheet.addMenu("Custom",
     [
       {
         name: "Add Memory Row",
         functionName: "addMemoryRow"
       }
     ]
  );
}

function addMemoryRow() {
  let sheet = spreadsheet.getActiveSheet();
  let lastRowIndex = sheet.getLastRow();
  let existingRange = getRowRange(sheet, lastRowIndex);

  sheet.insertRowAfter(lastRowIndex);
  let newRange = getRowRange(sheet, ++lastRowIndex);

  existingRange.copyTo(newRange);
  newRange.setFormulas(newRange.getFormulas());
  newRange.clearNote();
}
function getRowRange(sheet, rowIndex) {
  return sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()); //start row index, start col index, row count, col count | indexes start at 1
}

Save the script from the menu, either from the "File" menu, by clicking on the editor's button with the save icon, or by hitting CTRL + S (or similar for MacOS). You will also be prompted to name your script project.
Refresh your spreadsheet. A new menu called "Custom" will be shown in your menu bar.
Open the "Custom" menu and click on "Add Memory Row". You will be prompted as follows:

Authorisation Required

A script attached to this document needs your permission to run.

Click the "Continue" button.
A small window will pop up asking you to sign in to your Google account. Once you have done so, a warning will appear telling you that "This app isn't verified". But the script is one that you've just created yourself, so this is not an issue. Click on "Advanced", then "Go to x (unsafe)" where "x" is the name you've given to your script project.
You will be told that your script wants to access the spreadsheets in your Google account. Click the "Allow" button. The window will then close.
Go back to your spreadsheet and, once again, try adding a new memory row using its associated menu item ("Add Memory Row"). This time, some alerts will flash in quick succession. First, a dismissible one telling you that your script is running, then a similar one allowing you to cancel the running of the script, and finally, a non-dismissible one telling you that the script has finished running.
Your new memory row would then have been added to your spreadsheet, formulae and all. Make use of the menu item as needed!

How do I quickly add new rows to a Google Sheet while applying existing columns' formulae?

1 Answers

Authorisation Required