CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Diego Kenny
Diego Kenny

Reputation: 35

Trigger a function when one cell is edited in Google Sheets

after some hours of reading documentation and other answers I couldn't solve my problem. I'm trying to trigger a formula when one cell is edited. I get it to run when I just ask a value to be set (see commented line in the code) but not when I call another formula. Here's the script:

function onEdit() {

var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var cell =  sheet.getActiveCell();

var row = cell.getRow();
var column = cell.getColumn();


if ( row ===1 && column ===4 && sheet.getName() ==="Hoja 1" ) {

  sendData();
  
  //sheet.getRange(1,1).setValue("5");
}

function sendData() {

  var sheet = SpreadsheetApp.openById("1pxS9F9YEagzzRyTOqIDGVpDaCEiY_Z1DOYxq1QVzWGk");

  var source_sheet = sheet.getSheetByName("Hoja 1");
  var target_sheet = sheet.getSheetByName("Hoja 2");
  
  var range1 = source_sheet.getRange(1, 7, 6, 1).getValues();
  
  target_sheet.getRange(1,1,6,1).setValues(range1);
}

}

Upvotes: 1

Views: 308

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

You have to pass the sheet into the called function this way:

sendData(spreadsheet);

function sendData(sheet) {
  ...
}

onEdit() is a simple trigger. Simple triggers cannot access services that require authorization. So it cannot call SpreadsheetApp.openById().

https://developers.google.com/apps-script/guides/triggers#restrictions

And pay attention -- in the function sendData() you're using the name sheet but it's a spreadsheet actually. This is not an error but it can be confusing a bit.

Upvotes: 1

Related Questions