Trigger when change is happening in specific sheet-CodePudding

I have a sheet with data that is imported through IMPORTRANGE to another sheet. When I make changes to Spreadsheet 1 >>> Spreadsheet 2 I have a script that copy the data from Copy to Paste. It is working all fine however I want to make sure that the script only runs when changes are made in the 'Copy' sheet and not any other. At the moment it runs independent on what sheet I make changes in.

Spreadsheet 1 Spreadsheet2

I tried onChange trigger two different ways... This one makes the change but triggers when changes are made in any of the sheets

function Trigger2(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() === 'Copy') {

copyInfo();

}
}

AND (this does not work)

function Trigger2(e) {
  var sheet = e.source.getActiveSheet();
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  if( sheet.getActiveSheet().getName() !== "Copy" ) return;{

copyInfo();

}}

The copy code looks like this...


function copyInfo() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("Copy");
  var pasteSheet = ss.getSheetByName("Paste");

  // get source range
  var source = copySheet.getRange(2,1,12,8);
  // get destination range
  var destination = pasteSheet.getRange(2,1,12,8);

  // copy values to destination range
  source.copyTo(destination);
}

CodePudding user response：

Copy Changes from one Sheet To Another after Imported Range Changes

Spreadsheet 2 Imports a range to Sheet1 from Spreadsheet 1 Sheet1 and when a change occurs in Spreadsheet 2 Sheet1 we copy data from Spreadsheet2 Sheet1 to Spreadsheet2 Sheet 2.

function onMyChange(e) {
  //Logger.log(JSON.stringify(e));//useful during setup
  //e.source.toast("Entry");//useful during setup
  const sh = e.source.getSheetByName("Sheet1")
  Logger.log(sh.getName());
  if(e.changeType == "OTHER") {
    let tsh = e.source.getSheetByName("Sheet2"); 
    sh.getRange(2,1,12,8).copyTo(tsh.getRange(2,1));//Only need the upper left hand corner of the range.   Thus you change change the size of the source data without having to change the target range.
  }
}

One might be inclined to attempt to use e.source.getActiveSheet() unfortunately this does not necessary get the sheet that you have written unless in is SpreadsheetApp.getActive().getSheets()[0]. onChange event object always returns the most left sheet in the spreadsheet in this situation so it can't be used to identify the sheet that is being written to from the importRange function.

With this function you no longer require another function.

CodePudding user response：

A simple if should work, but you're trying to compare the name to the wrong field. According to Google's documentation, the source field in the Event Object e returns the Spreadsheet object, which is the entire document.

Instead you can use the e.range field, which contains the exact range where the edit was made, and the Range class has a getSheet() method to get the parent Sheet.

So you can modify your sample to the following:

function Trigger2(e) {
  var sheet = e.range.getSheet();
  if (sheet.getName() === 'Copy') {//Edit: this will only work if sheet is the most left sheet in the spreadsheet
    copyInfo();
  }
}