Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Copy value of a dynamic cell to another sheet on edit

I am trying to copy the value of a dynamic cell into a specific cell on another sheet upon edits being done in the first sheet.

Example:
Whenever cell A5 and B3 and C2 are edited on sheet ‘work’, it should take the current value of cell B1 and copy it to cell C5 on sheet ‘overview’.

Here is the code I have so far, but it somehow does not work. What am I missing here?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

    function onEdit2() {
var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "work") {
    var r =s.getActiveCell() ;
    if(r.getCell('A5') || r.getCell('B3') || r.getCell('C2') {
      var b =  s.getCell('B1') ;
      var uo = SpreadsheetApp.getSheet('overview')
      b.copyTo(uo.getCell('C5'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
    }
  }
}

>Solution :

  • First of all onEdit2 can not be used as an onEdit trigger unless it is installable. Therefore I would advice onEdit so you don’t have to create the trigger yourself.

  • The optimal way is to use the event handler e that will give you the relevant information you need regarding the cell that is edited.

I also made your code cleaner by using includes to check whether the edited cell is part of a list of cells.

function onEdit(e) {
  const as = e.source.getActiveSheet();
  const ts = e.source.getSheetByName("overview");
  const cell = e.range.getA1Notation();
  const triggerCells = ["A5","B3","C2"];
  
  if (as.getName() == "work" && triggerCells.includes(cell)){
    let b = as.getRange("B1").getValue();
    ts.getRange("C5").setValue(b);
  }

}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading