Not allow to access from one to other Google Sprea

问题:

I trying to set new value to my Google Spreadsheet from onEdit event in other spreadsheet. I get Exception: Action not allowed. I can't understand what exactly I doing not right. I'll be glad for your help because i just doing first steps in JS + Google Docs scripting.

function onEdit(e) {
  if (e.value == "1")
    updateValue(e);
  else
    Browser.msgBox("Type 1 in this cell");
}

function updateValue(curRange) {
  //Get needed SpreadSheet ID and Cell Index (exp:D2) from current sheet
  var ssCur = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ssCur.getSheets()[0];

  var referenceID = sheet.getRange(curRange.range.getRow(), curRange.range.getColumn()+2).getValue();
  var cellCoordinate = sheet.getRange(curRange.range.getRow(), curRange.range.getColumn()+3).getValue();

  // Here still work good and get right values
  Browser.msgBox(referenceID); //tracing string

  try {
    //Create remote spreadsheet object - this string catching Exception
    var ss = SpreadsheetApp.openById(referenceID);
    //Create workbook (sheet)
    Browser.msgBox(ss.getName());
    var sheets = ss.getSheets();
    var sheet4Update = sheets[0].getRange(cellCoordinate).setValue('1');
  }
  catch(err) {
    Browser.msgBox(err);
  }
}

回答1:

You can fix your code: 1) dont call msgBox or any modals from the trigger. Breaks many things. 2) you cant access other docs from simple triggers, plus it runs as the user so you might not have permissions for other things. Instead install the trigger yourself with a different name (not onEdit) and set it to trigger when document is edited.

回答2:

Check the documentation Understanding Triggers, specifically, restrictions on Simple Triggers:

 ...

• They can only modify the containing document. Access to other documents is forbidden.