Google Sheets Copy down formula with inserting a row

user_7976830 0
Viewed 288

I am transitioning from Excel and VBA to Google Sheets and admittedly, don't know JavaScript.

I see that there is a common request for some VBA which will copy down the formulas from the row above, when I insert a new row anywhere in the dataset. I know how to add a butt, and i used the Macro Recorder to record the insertion of a Row, but the recorder only records that particular row that I chose to insert above.

I'd like the script to grab the activecell row number, so that it is dynamic on where ever my cursor is.

Here's the code I have so far. Can someone help me correct it?

function test() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveCell().getRow().activate();
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveCell().getRow(), 1);
  spreadsheet.getActiveCell().offset(0, 0, 1, spreadsheet.getActiveCell().getNumColumns()).activate();
};
google-apps-script google-sheets
1 Answers

Solution:

You can accomplish your goal by creating an installable onChange trigger.

Follow the instructions below to set it up. After the setup is completed, you don't execute myFunction manually. It will be executed automatically everytime you insert a new row in the active sheet.

function myFunction(e) {
 const sh = SpreadsheetApp.getActiveSheet();
 if(e.changeType === 'INSERT_ROW') {
 const row = sh.getActiveRange().getRow();
 const formulas = sh.getRange(row-1,1,1,sh.getLastColumn()).getFormulasR1C1();
 sh.getRange(row,1,1,sh.getLastColumn()).setFormulasR1C1(formulas);  
  }
}

Instructions:

  1. Go to use the aforementioned code, copy/paste it to the script editor and click save.

  2. Click on the current project's triggers:

example

  1. Click on add trigger on the bottom right side of the page.

  2. And then use exactly these settings:

settings

user_112252910
Related