Google Apps Script. Transfer data from current active sheet to another by URL (on Edit event)

Question

I am trying to pass data from one Google Sheet:

---

Data transfered to another sheet by URL: https://docs.google.com/spreadsheets/d/1gFuY2i50Vxdl5kx7iHZAupILAW2u35KYSRzCKKXMWIk/edit#gid=0

Data (specific row) transfered on trigger onEdit() and checks if row A is not Empty and row F is "Problem Case". If A is not empty and F is problem case then data from current row in current sheet copied to another sheet.

I take specific columns from current sheet: Name, Date, Time, City, Status and some specific cells on G column and copy it to another sheet. I take only the row where Problem Case edit occured.

---

I have a code:

function onEdit(e) {
  try {
    var sheet = e.source.getSheetByName('Daily report');
    if (!sheet || e.range.columnStart < 6 || e.range.columnStart > 6) {
      return;
    }

  var ui = SpreadsheetApp.getUi();
  var title = "Message Box";

    // Получаем данные из редактируемой строки
    var row = e.range.rowStart;
    var valueA = sheet.getRange('A' + row).getValue();
    var valueF = sheet.getRange('F' + row).getValue();
   
    // Проверяем, что строка C не пустая и значение в столбце G равно "Проблемный кейс"
    if (valueA !== '' && valueF === 'Problem Case') {

      // Получаем данные для записи в другой документ
      var dataToSave = [
        sheet.getRange('A' + row).getValue(),
        sheet.getRange('B' + row).getValue(),
        sheet.getRange('C' + row).getValue(),
        sheet.getRange('D' + row).getValue(),
        sheet.getRange('E' + row).getValue(),
        sheet.getRange('F' + row).getValue(),
        sheet.getRange('G3').getValue(),
        sheet.getRange('G4').getValue(),
        sheet.getRange('G5').getValue()
      ];

      ui.alert(title, "TEST", ui.ButtonSet.OK);
      

      var targetURL = "https://docs.google.com/spreadsheets/d/1gFuY2i50Vxdl5kx7iHZAupILAW2u35KYSRzCKKXMWIk/edit#gid=0";         
      var targetDoc = SpreadsheetApp.openByUrl(targetURL);
      var targetList = targetDoc.getSheetByName("Sheet1");

      targetList.getRange(1, 1, dataToSave.length, dataToSave[0].length).setValues([dataToSave]);
      
    }
  } catch (error) {
    // Лог ошибки
    Logger.log('Error: ' + error.toString());
  }
}

---

If column F edited then it checks criteria and copies data. OnEdit event works and copies data to array: dataToSave. But when I try to define another document and sheet in script then problem occurs. Data is not passing to another sheet. But array dataToSave on Edit is filled with corresponding data

---

I gave Edit access by link for destination document. I granted permissions for the script to edit my Google tables. But anyway nothing happens.

---

SourceSheet (sample data) is available: SourceSheet DestinationSheet (sample data) is available: DestinationSheet

---

Update: I edited my script in editor then added manually trigger function onMyEdit() provided by @Cooper as to be activated on change. But no reaction when I change F column to "Problem Case".

Answer 1

Please setup an installable trigger

function onMyEdit(e) {
  //e.source.toast("Entry");
  const sh = e.range.getSheet();
  if (sh.getName() == 'Daily Report' && e.range.columnStart > 5 && e.range.columnStart < 7) {
    //e.source.toast("Gate1")
    var title = "Message Box";
    var [colA,,,,,colF] = sh.getRange(e.range.rowStart,1,1,6).getValues().flat();//a way to get values in A and F with only one read
    if (colA !== '' && colF === 'Problem Case') {
      var vs = sh.getRange(e.range.rowStart, 1, 1, 6).getValues().flat()
      vs = vs.concat(sh.getRange(3, 7, 3).getValues().flat());
      e.source.toast("Message",title);//I would use a toast because it will go away by itself and not slow down any other editing
      var tss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1gFuY2i50Vxdl5kx7iHZAupILAW2u35KYSRzCKKXMWIk/edit#gid=0");
      var tsh = tss.getSheetByName("Sheet1");
      tsh.getRange(tsh.getLastRow() + 1, 1, 1, vs.length).setValues([vs]);
    }
  }
}