onEdit() specific cell doesn't fire the script

0
votes

I am trying to copy data from one sheet to another, when I change the value of one cell in the source sheet. I had this problem with other scripts that I tried to fire when editing a specific cell as well, not sure what I am doing wrong.

I found this code here (Google Apps Script - Copy data to different worksheet and append) that works well when I run it manually however when I wrap it so that when editing one cell nothing seems to happen, see below.

  if (e.range.getA1Notation() === 'C1'){
 var sss = SpreadsheetApp.openById('1ws8yg5_1k4ZZN2amfZ-cNvY-hUMUDGhbwnNPCGJhlAY'); //replace with source ID
 var ss = sss.getSheetByName('export'); 
 var range = ss.getRange('B3:C8'); 
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('152JxdVi6Ssqz2ZZ65yDW5p5m0PnP63vApNgfP3jS_Pc'); //replace with destination ID
 var ts = tss.getSheetByName('import'); //replace with destination Sheet tab name

 ts.getRange(1, 2, 6,2).setValues(data); //you will need to define the size of the copied data see getRange()

}
}

Source Sheet: https://docs.google.com/spreadsheets/d/1ws8yg5_1k4ZZN2amfZ-cNvY-hUMUDGhbwnNPCGJhlAY/edit#gid=0

Destination Sheet: https://docs.google.com/spreadsheets/d/152JxdVi6Ssqz2ZZ65yDW5p5m0PnP63vApNgfP3jS_Pc/edit#gid=0

I tried to with creating a new trigger as well, something like this:

function createOnEditTrigger() {
  var ss = SpreadsheetApp.openById(targetSpreadsheetID);
  ScriptApp.newTrigger("CopyRange")
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

but nothing is happening.

1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

Explanation / Issue:

There are 2 things to note here:

  • You forgot to pass the event object as a parameter of the CopyRange(e) function. Without this parameter, the event object can't be used but your code uses it.

  • Indeed you need an installable trigger because of SpreadsheetApp.openById(). You can indeed also create the trigger with the script you mentioned and this is also my recommended approach.

Solution:

Go to the export spreadsheet, clear the previous code and add this code. Essentially, I am adding the event object into the CopyRange(e) function and the trigger creation function. Then execute only the createOnEditTrigger function:

function CopyRange(e) {
  if (e.range.getA1Notation() === 'C1'){
 var sss = SpreadsheetApp.openById('1ws8yg5_1k4ZZN2amfZ-cNvY-hUMUDGhbwnNPCGJhlAY'); //replace with source ID
 var ss = sss.getSheetByName('export'); 
 var range = ss.getRange('B3:C8'); 
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('152JxdVi6Ssqz2ZZ65yDW5p5m0PnP63vApNgfP3jS_Pc'); //replace with destination ID
 var ts = tss.getSheetByName('import'); //replace with destination Sheet tab name

 ts.getRange(1, 2, 6,2).setValues(data); //you will need to define the size of the copied data see getRange()

}
}

function createOnEditTrigger() {
  var ss = SpreadsheetApp.openById('1ws8yg5_1k4ZZN2amfZ-cNvY-hUMUDGhbwnNPCGJhlAY');
  ScriptApp.newTrigger("CopyRange")
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

example