CODEWITHSUNDEEP
google-apps-script
Tudor68
Tudor68

Reputation: 25

Google Sheets App Script - Convert a function to an onEdit of a specific cell

I have a function which sets a formula in a range of cells and then copies that formula down. This seems to work fine.

I would like to convert this into an onEdit function which runs when cell D11 is edited on the current tab.

I have not had much luck so far.

function copyDownAll() {
  let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("A12:C12").setFormula("=Master!A7");
  let noRows = ss.getMaxRows();
 
  let fillDownRange = ss.getRange(12,1,noRows+1,4);
  ss.getRange("A12:C12").copyTo(fillDownRange);
  
  let fillDownRange2 = ss.getRange(11,4,noRows+1,1);
  ss.getRange("D11").copyTo(fillDownRange2);
}

Upvotes: 0

Views: 152

Answers (2)

doubleunary
doubleunary

Reputation: 19220

Your comments suggest that you are exposed to the risk of rows getting misaligned.

To avoid potential trouble going forward, you may want to explore putting all data in the Master sheet, and have everyone edit it there.

Use filter views to let multiple simultaneous users edit the sheet without disturbing each other. Insert links in the frozen section to easily switch between filter views — see the Filter views example spreadsheet.

To answer your original question, this sort of thing is usually done in Google Sheets with an arrayformula() or an { array expression }. Try this in cell A12:

={ Master!A7:C }

This expression will fill columns A:C all the way down with values from Master.

Upvotes: 0

Tudor68
Tudor68

Reputation: 25

After a bit more searching I found the following solution which seems to work so far

function onEdit(e) {
  if(e.range.getA1Notation() =='D11'){

  let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange("A12:C12").setFormula("=Master!A7");
  let noRows = ss.getMaxRows();
 
  let fillDownRange = ss.getRange(12,1,noRows+1,4);
  ss.getRange("A12:C12").copyTo(fillDownRange);
  
  let fillDownRange2 = ss.getRange(11,4,noRows+1,1);
  ss.getRange("D11").copyTo(fillDownRange2);
  };
};

Reference:

Upvotes: 1

Related Questions