Call function onEdit of a cell value if value equals specific text

Question

I have a function called copy() which can copy a range from a sheet and paste to another sheet. That function needs to be executed from script editor but I want to do it automatically if I change the value of cell Sheet3!A3.

With the following code, I can send the date/time to a specific cell. I want something like this which will just call the function copy() if the value of A3 of Sheet3 is changed.

function onEdit(event) {
  var range = event.range;
  //var cofCellEdited = range.getActivelumnOSheet();
  var cofCellEdited = range.getSheet().getSheetName();
  if (cofCellEdited === "Sale") {

    var timezone = "GMT+06:00"
    var timestamp_format = "M/d/yyyy HH:mm:ss"; // Timestamp Format. 
    var updateColName = "Product";
    var timeStampColName = "Time";
    var sheet = event.source.getSheetByName('Sale'); //Name of the sheet where you want to run this script.
    var actRng = event.source.getActiveRange();
    var editColumn = actRng.getColumn();
    var index = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf(timeStampColName);
    var updateCol = headers[0].indexOf(updateColName);
    updateCol = updateCol + 1;
    if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
      var cell = sheet.getRange(index, dateCol + 1);
      var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
      cell.setValue(date);
    }
  }

And my copy function is :

function copy() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var target = SpreadsheetApp.openById("1I9KYJUtT2Vi0yAi_b8LaBSijReL-XEoKKZUqTxeWmQQ");
  var source_sheet = ss.getSheetByName("Suposed Share");
  var target_sheet = target.getSheetByName("Payable");
  var source_range = source_sheet.getRange("A2:D3");
  var last_row = target_sheet.getLastRow();
  target_sheet.insertRowAfter(last_row);
  var target_range = target_sheet.getRange("A" + (last_row + 1) + ":D" + (last_row + 1));
  source_range.copyTo(target_range, {
    contentsOnly: true
  });


}

How can I call this function if I change the value of Sheet3!A3?

Jacques-Guzel Heron Jacques-Guzel Heron · Accepted Answer · 2019-10-15T13:26:06

You can achieve your request using installable triggers with the following formula in your code:

function onEdit(e) {
  if (e.range.getA1Notation() == 'A3' && e.source.getSheetName() == 'Sheet3') {
    copy();
  }
}

That formula will use the event object to call the copy() formula every time that the cell A3 of the sheet Sheet3 is called. You don't need the onEdit(event) (from your question) in your code to make this work. Please, ask for help if you need further clarification.

Call function onEdit of a cell value if value equals specific text

1 Answers