onEdit脚本调用SpreadsheetApp.openById时权限报错求助

云原生阿岚

2026-8-12

问题描述

我编写了一个onEdit触发器脚本，用于将一个电子表格中的值同步到另一个电子表格。此前在onOpen脚本中使用SpreadsheetApp.openById从未出现权限问题，但这个新脚本却提示我没有使用openById的权限。我查阅相关解决方案后修改了oauthScopes配置，但问题仍未解决。

appsscript.json配置

{
  "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"
}

触发器脚本代码

function onEdit(e){

//替换为真实表格ID
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);

//设置状态和负责人
if (e.source.getActiveSheet().getName() == SHEET_NAME && row > FIXEDROW){
    //若修改了状态
    if (ESTADO !== '' && ESTADO !== ESTADO_2) {
      var targetColumn = TARGET_ESTADO_COLUMN;
      var targetValue = ESTADO;
    }

    //若修改了负责人
    else if (RESP !== ''){
      var targetColumn = TARGET_RESP_COLUMN;
      var targetValue = RESP;
    }

  cellRange.setValue(targetValue);

}

//当状态为"Preparado"或"Entregado"、负责人不为空时设置时间戳
//若准备步骤名称变更请更新此处
if ((ESTADO == 'Preparado' || ESTADO == 'Entregado') && RESP !== ''){
  var targetColumn = TARGET_TIME_COLUMN;
  var targetValue = new Date();

  cellRange.setValue(targetValue);
  
}

}