CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulatriggers
Mee
Mee

Reputation: 145

onEdit specific cell copy data from one google sheets to another

In google sheets, I am trying to get one data to copy from one sheet to another.

I have this code which is working however I would like it to run onEdit when changing cell E4 in Googlesheet1. I am new at this and doesn't seem to get it to quite work with the solutions I found online.

function ExportRange() {
  var destination = SpreadsheetApp.openById('googlesheet1');
  var destinationSheet = destination.getActiveSheet();      
  var destinationCell = destinationSheet.getRange("AC3");
  var cellData = '=IMPORTRANGE("https://docs.google.com/spreadsheets/googlesheet2", "AE10:AE9697")';
  destinationCell.setValue(cellData);
}

Upvotes: 0

Views: 657

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

  • Chose between a simple and installable onEdit trigger, depending on your requirements
  • For most applciaitons a simple onEdit trigger is sufficient, to use it you just need to rename your function ExportRange() to onEdit()
  • Take advantage of event objetcs that give you informaiton about the event that fired the trigger
  • So, the trigger onEdit can give you among others information about the event range - that is the range that has been edited
  • Now you can implement an if statement to specify that the rest of the funciton shall only be run if the event range and the corresponding sheet are as required

Sample:

function onEdit(event) {
  var range = event.range;
  var sheet = range.getSheet();
  if(range.getA1Notation() == "E4" && sheet.getName() == "Googlesheet1"){
    var destination = SpreadsheetApp.openById('googlesheet1');
    var destinationSheet = destination.getActiveSheet();      
    var destinationCell = destinationSheet.getRange("AC3");
    var cellData = '=IMPORTRANGE("https://docs.google.com/spreadsheets/googlesheet2", "AE10:AE9697")';
    destinationCell.setValue(cellData);
  }
}

Please note that this function can only be fired by the trigger in case of an edit. If you try to run it manually, it will give you an error because event (and thus event.range) will be undefined if the funciton was not called by an edit event.

Upvotes: 1

Related Questions