Don't have permission for SpreadsheetApp.openById-CodePudding

I wrote an onEdit script that takes values from one spreadsheet and sets them in another. I have used SpreadsheetApp.openById on other onOpen scrips without any issues. But only for this specific new script it tells me I don't have the permissions to use openById. I read other answers that said to change the oauthScopes an I did, but it still says I don't have permition.

{
  "oauthScopes": [
      "https://www.googleapis.com/auth/spreadsheets.readonly",
      "https://www.googleapis.com/auth/userinfo.email",
      "https://www.googleapis.com/auth/spreadsheets"
      
],
  "timeZone": "America/Argentina/Buenos_Aires",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

This is the script I wrote.

function onEdit(e){

//reemplazar targetID por ID de hoja real
var targetID = '14VgIUDG7m0-Z9SGCec5GBfaivrA_8bakUTu2qfWw_Ho';
var ss = SpreadsheetApp.openById(targetID);
var s = ss.getSheetByName('Cortes'); 

const row = e.range.getRow();
const col = e.range.getColumn();
const SHEET_NAME = 'Pendientes';

const DATE_COLUMN = 4;
const FIXEDROW = 2;
const ESTADO_COLUMN = 5;
const ESTADO_COLUMN_2 = 25;
const RESP_COLUMN = 3;
const TARGET_ROW_COLUMN = 26;

const ESTADO = e.source.getActiveSheet().getRange(row, ESTADO_COLUMN).getValue();
const ESTADO_2 = e.source.getActiveSheet().getRange(row, ESTADO_COLUMN_2).getValue();
const RESP = e.source.getActiveSheet().getRange(row, RESP_COLUMN).getValue();

Logger.log(row);
Logger.log(TARGET_ROW_COLUMN);

const TARGET_ROW = e.source.getActiveSheet().getRange(row, TARGET_ROW_COLUMN).getValue();
const TARGET_RESP_COLUMN = 3;
const TARGET_ESTADO_COLUMN = 5;
const TARGET_TIME_COLUMN = 4;


var cellRange = s.getRange(TARGET_ROW, targetColumn);

//set estado y resp
if (e.source.getActiveSheet().getName() == SHEET_NAME && row > FIXEDROW){
    //si modifica el estado
    if (ESTADO !== '' && ESTADO !== ESTADO_2) {
      var targetColumn = TARGET_ESTADO_COLUMN;
      var targetValue = ESTADO;
    }

    //si modifica el responsable
    else if (RESP !== ''){
      var targetColumn = TARGET_RESP_COLUMN;
      var targetValue = RESP;
    }

  cellRange.setValue(targetValue);

}

//set timestamp if estado=preparado o estado=entregado y resp<>"" y timestamp=""
//actualizar si cambian los nombres de los pasos de preparación
if ((ESTADO == 'Preparado' || ESTADO == 'Entregado') && RESP !== ''){
  var targetColumn = TARGET_TIME_COLUMN;
  var targetValue = new Date();

  cellRange.setValue(targetValue);
  
}

}

This is the spreadsheet where the script is posted, from where i source the values:

CodePudding user response：

I believe you can do it with an installable onEdit trigger

Just as an example I copied data from one spreadsheet to another with this code

function onMyEdit(e) {
  e.source.toast("Entry");
  const sh = e.range.getSheet();
  const vs = sh.getDataRange().getValues();
  const tss = SpreadsheetApp.openById(gobj.globals.testsourceid);//this is just a global object that holds one of my spreadsheet ids
  const tsh = tss.getSheetByName("Sheet5");
  tsh.getRange(1,1,vs.length,vs[0].length).setValues(vs);
}

The I just edited one of the cells on the source side and the data transfered immediately. So just create an installable trigger but don't name it onEdit(). It will stilll work the same as an onEdit and even have the same event object.

Here's a copy of the event object:

{"authMode":"FULL","range":{"columnEnd":13,"columnStart":13,"rowEnd":23,"rowStart":23},"source":{},"triggerUid":"","user":{"email":"","nickname":""},"value":"dd"}