How to use onEdit to copy the data to another google sheet file and overwrite the data if the ID is the same?

Question

Hi everyone,

I want to copy the data from source sheet to destination sheet. When the data reached the destination sheet, the script able to loop through row 2 in destination sheet to see whether any same ID already existed. If the ID already existed in row 2, then it will overwrite the data in the column, if not, the script will find the last empty column based on row 2 and paste the data there.

So in the screenshot above, since there is no 1004 in destination sheet, then it will paste the data in column E.

This is my code:

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSheet ();
  var targetfile = SpreadsheetApp.openById("11tpC8SNZ5XB35n7GON0St3ZQ37dIbM8UbXRjmkVAeJQ");
  var target_sheet = targetfile.getSheetByName("Sheet1");
  var target_range = target_sheet.getRange(3, ss.getLastColumn() + 1);
  
  if (e.range.columnStart == 3 && e.range.rowStart == 16){
    if (e.value == 'Submit'){
      var source_range = ss.getRange("C4:C14")
      source_range.copyTo(target_range);
      e.range.clearContent()
    } 
  } 
}

My current problems are:

• The script is not working when I triggered it in cell C16 (couldn't find the reason)
• I'm not sure how to add the checking for ID in destination sheet into my script.

This are my google files

Source sheet: https://docs.google.com/spreadsheets/d/12kKQKT2XSdPkJ46LSV9OiI167NKBdwKkpkOtOf_r_jI/edit#gid=0

Destination sheet: https://docs.google.com/spreadsheets/d/11tpC8SNZ5XB35n7GON0St3ZQ37dIbM8UbXRjmkVAeJQ/edit#gid=0

Hope to get some advice and help from expert. Any help will be greatly appreciated!

Answer 1

Explanation:

I see a couple problems with the existing script:

1. Simple Triggers only have authorization on the container Spreadsheet, so to access another Spreadsheet you need to create an Installable Trigger:

// Creates an edit trigger for a spreadsheet identified by ID.
function createTrigger() {
  ScriptApp.newTrigger('copyFunction')
    .forSpreadsheet('source-sheet-id')
    .onEdit()
    .create();
}

2. copyTo() only works when copying to the same Spreadsheet. You would need to use getValues() and setValues() to copy across spreadsheets. The caveat is that formatting is not copied. You would need to manually format the columns if a new column is filled up.

function copyFunction(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var targetfile = SpreadsheetApp.openById('dest-sheet-id');
  var target_sheet = targetfile.getSheetByName("Sheet1");
  var id_table = target_sheet.getRange(2,2,1,target_sheet.getLastColumn()-1).getValues();
  
  if (e.range.getA1Notation() == 'C16') {
    if (e.value == 'Submit') {
      var source_values = ss.getRange("C3:C14").getValues();
      for (i = 0; i < id_table[0][i]; i++) {
        if (id_table[0][i] == ss.getRange("C3").getValue()) 
        break;
      }
      target_sheet.getRange(2,i+2,12).setValues(source_values);
      e.range.clearContent();
    } 
  } 
}