如何让Google Script以表格所有者权限执行？权限问题求助

DevOps 小满

2026-8-19

解决Google Sheets脚本权限不足问题：通过Web App以所有者权限执行操作

问题说明

本人编写的Google脚本可正常复制/删除表格，但其他用户执行时因受保护范围触发权限不足提示。已知可通过Web App实现以表格所有者权限运行脚本，但不清楚具体操作方法。原脚本通过表格内绑定脚本的图片触发，原代码如下：

原新建表格脚本

function dossierAanmaken() {
var response = Browser.msgBox('Dossier Aanmaken', 'Weet je zeker dat je een nieuw dossier wilt aanmaken?', Browser.Buttons.YES_NO);
if (response == "yes") {
  aanmaken();
} else {
  Logger.log('Het aanmaken van het dossier is geannuleerd!');
}
} 

function aanmaken() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("-Dossier");
  var sh2 = ss.getSheetByName("ZZZ - Medewerkers");
  var rg1 = sh1.getRange(2, 7, 1, 1);
  var rg2 = sh2.getRange(sh2.getLastRow() + 1, 1, 1, 1);
  rg1.copyTo(rg2, { contentsOnly: true });
  var sh3 = ss.getSheetByName('-Template');
  var sh4 = sh3.copyTo(ss).setName('Test');
  sh4.activate();
  var cell = sh1.getRange("G2");
  var v1G2 = cell.getValue();
  sh4.setName(v1G2);
  sh4.setTabColor(null);
  Utilities.sleep(200);
  var shts = ss.getSheets().map(sh => sh.getName()).sort().forEach((n, i) => {
    ss.setActiveSheet(ss.getSheetByName(n));
    ss.moveActiveSheet(i + 1)
  });
  var sh5 = ss.getSheetByName('ZZZ - Medewerkers');
  var range = sh5.getRange("A3:A100");
  range.sort({column: 1, ascending: true});

  var copySheet = ss.getSheetByName("-Dossier");
  var source = copySheet.getRange(2,7,5,1);
  var destination = sh4.getRange(2,7,5,1);
  source.copyTo(destination, {contentsOnly:true});
  sh2.hideSheet();
  sh4.showSheet();
  sh4.hideSheet();
  copySheet.activate();

  addtoLogboek();

 }

 function addtoLogboek() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ss.getSheetByName("-Logboek");
  var sh1 = ss.getSheetByName("-Dossier");

  // get source range
  var source = copySheet.getRange(7,1,1,17);
  // get destination range
  var destination = copySheet.getRange(copySheet.getLastRow()+1,1,1,1);

  // copy values to destination range
  source.copyTo(destination);

  var employeeName = sh1.getRange("G2").getValue();
  var sh2 = ss.getSheetByName("-Logboek");
  sh2.getRange("A8:A350").createTextFinder('-').matchEntireCell(true).replaceAllWith(employeeName);


  var rangeList = sh1.getRangeList(['G2:G6']);
  rangeList.clearContent();

  SpreadsheetApp.getUi().alert("Dossier Aangemaakt", "Het dossier is succesvol aangemaakt!", SpreadsheetApp.getUi().ButtonSet.OK);

}

原删除表格脚本

function dossierVerwijderen() {
var response = Browser.msgBox('Dossier Verwijderen', 'Weet je zeker dat je dit dossier wilt verwijderen?', Browser.Buttons.YES_NO);
if (response == "yes") {
  verwijderen();
} else {
  Logger.log('Het verwijderen van het dossier is geannuleerd!');
}
} 

function verwijderen() {
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("-Dossier");
  var cell = sh1.getRange("G2");
  var v1G2 = cell.getValue();
  ss.deleteSheet(ss.getSheetByName(v1G2));
  var employeeName = sh1.getRange("G2").getValue();
  var sh2 = ss.getSheetByName('ZZZ - Medewerkers');
  sh2.createTextFinder(employeeName).matchEntireCell(true).replaceAllWith('');


    removefromLogboek();
 }

 function removefromLogboek() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName("-Dossier");
  var employeeName = sh1.getRange("G2").getValue();
  var sh2 = ss.getSheetByName("-Logboek");
  sh2.getRange("A8:A350").createTextFinder(employeeName).matchEntireCell(true).replaceAllWith('VERWIJDEREN_LOGBOEK');

  var rows = sh2.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {

  var row = values[i];

  if (row[0].indexOf('VERWIJDEREN_LOGBOEK') > -1) {
  sh2.deleteRow((parseInt(i)+1) - rowsDeleted);
  rowsDeleted++;
 }
 }

  var rangeList = sh1.getRangeList(['G2:G6']);
  rangeList.clearContent();

  SpreadsheetApp.getUi().alert("Dossier Verwijderd", "Het dossier is succesvol verwijderd!", SpreadsheetApp.getUi().ButtonSet.OK);

 }

解决方案：通过Web App以所有者权限运行脚本

步骤1：修改脚本适配Web App

原脚本依赖表格内的UI交互（Browser.msgBox、Ui.alert），Web App无法直接使用这些API，需修改为HTML页面交互，并添加Web App入口函数doGet：

function doGet(e) {
  // 根据URL参数判断执行操作类型
  if (e.parameter.action === 'create') {
    return handleCreateDossier();
  } else if (e.parameter.action === 'delete') {
    return handleDeleteDossier();
  } else {
    // 返回操作选择页面
    return HtmlService.createHtmlOutput(`
      <style>
        body { font-family: Arial; padding: 20px; text-align: center; }
        button { padding: 10px 20px; margin: 10px; font-size: 16px; cursor: pointer; }
      </style>
      <h3>选择操作</h3>
      <button onclick="window.location.href='${ScriptApp.getService().getUrl()}?action=create'">新建档案</button>
      <button onclick="window.location.href='${ScriptApp.getService().getUrl()}?action=delete'">删除档案</button>
    `);
  }
}

// 处理新建档案逻辑
function handleCreateDossier() {
  try {
    // 替换为你的表格ID（可在表格URL中获取）
    const ss = SpreadsheetApp.openById("你的Google表格ID");
    const dossierSheet = ss.getSheetByName("-Dossier");
    const dossierName = dossierSheet.getRange("G2").getValue();

    if (!dossierName) {
      throw new Error("请先在-Dossier表的G2单元格填写档案名称");
    }

    // 复制档案名称到员工表
    const employeeSheet = ss.getSheetByName("ZZZ - Medewerkers");
    const sourceRange = dossierSheet.getRange(2, 7, 1, 1);
    const targetRange = employeeSheet.getRange(employeeSheet.getLastRow() + 1, 1, 1, 1);
    sourceRange.copyTo(targetRange, { contentsOnly: true });

    // 复制模板表并命名
    const templateSheet = ss.getSheetByName('-Template');
    const newDossierSheet = templateSheet.copyTo(ss).setName(dossierName);
    newDossierSheet.setTabColor(null);

    // 排序工作表
    const sheetNames = ss.getSheets().map(sh => sh.getName()).sort();
    sheetNames.forEach((name, index) => {
      const sheet = ss.getSheetByName(name);
      ss.setActiveSheet(sheet);
      ss.moveActiveSheet(index + 1);
    });

    // 排序员工表
    const employeeSortRange = employeeSheet.getRange("A3:A100");
    employeeSortRange.sort({ column: 1, ascending: true });

    // 复制Dossier表数据到新档案表
    const dataSource = dossierSheet.getRange(2, 7, 5, 1);
    const dataTarget = newDossierSheet.getRange(2, 7, 5, 1);
    dataSource.copyTo(dataTarget, { contentsOnly: true });

    // 调整工作表显示状态
    employeeSheet.hideSheet();
    newDossierSheet.hideSheet();
    dossierSheet.activate();

    // 更新日志
    updateCreateLog(ss, dossierName);

    // 返回成功提示页面
    return HtmlService.createHtmlOutput(`
      <style>body { font-family: Arial; padding: 20px; text-align: center; }</style>
      <h3>Dossier Aangemaakt</h3>
      <p>Het dossier is succesvol aangemaakt!</p>
      <button onclick="window.close()">Sluiten</button>
    `);
  } catch (error) {
    // 返回错误提示页面
    return HtmlService.createHtmlOutput(`
      <style>body { font-family: Arial; padding: 20px; text-align: center; color: red; }</style>
      <h3>Fout bij aanmaken</h3>
      <p>Foutmelding: ${error.message}</p>
      <button onclick="window.close()">Sluiten</button>
    `);
  }
}

// 更新新建档案日志
function updateCreateLog(ss, employeeName) {
  const logSheet = ss.getSheetByName("-Logboek");
  const sourceLogRange = logSheet.getRange(7, 1, 1, 17);
  const targetLogRange = logSheet.getRange(logSheet.getLastRow() + 1, 1, 1, 17);
  sourceLogRange.copyTo(targetLogRange);

  logSheet.getRange("A8:A350")
    .createTextFinder('-')
    .matchEntireCell(true)
    .replaceAllWith(employeeName);

  // 清空Dossier表的G2:G6
  ss.getSheetByName("-Dossier").getRangeList(['G2:G6']).clearContent();
}

// 处理删除档案逻辑
function handleDeleteDossier() {
  try {
    // 替换为你的表格ID
    const ss = SpreadsheetApp.openById("你的Google表格ID");
    const dossierSheet = ss.getSheetByName("-Dossier");
    const dossierName = dossierSheet.getRange("G2").getValue();

    if (!dossierName) {
      throw new Error("请先在-Dossier表的G2单元格填写要删除的档案名称");
    }

    // 删除指定档案表
    const sheetToDelete = ss.getSheetByName(dossierName);
    if (!sheetToDelete) {
      throw new Error("Geen dossier gevonden met de opgegeven naam");
    }
    ss.deleteSheet(sheetToDelete);

    // 清空员工表中的对应记录
    const employeeSheet = ss.getSheetByName('ZZZ - Medewerkers');
    employeeSheet.createTextFinder(dossierName)
      .matchEntireCell(true)
      .replaceAllWith('');

    // 更新日志
    updateDeleteLog(ss, dossierName);

    // 返回成功提示页面
    return HtmlService.createHtmlOutput(`
      <style>body { font-family: Arial; padding: 20px; text-align: center; }</style>
      <h3>Dossier Verwijderd</h3>
      <p>Het dossier is succesvol verwijderd!</p>
      <button onclick="window.close()">Sluiten</button>
    `);
  } catch (error) {
    // 返回错误提示页面
    return HtmlService.createHtmlOutput(`
      <style>body { font-family: Arial; padding: 20px; text-align: center; color: red; }</style>
      <h3>Fout bij verwijderen</h3>
      <p>Foutmelding: ${error.message}</p>
      <button onclick="window.close()">Sluiten</button>
    `);
  }
}

// 更新删除档案日志
function updateDeleteLog(ss, employeeName) {
  const logSheet = ss.getSheetByName("-Logboek");
  logSheet.getRange("A8:A350")
    .createTextFinder(employeeName)
    .matchEntireCell(true)
    .replaceAllWith('VERWIJDEREN_LOGBOEK');

  // 倒序删除行，避免索引错位
  const logData = logSheet.getDataRange().getValues();
  for (let i = logData.length - 1; i >= 0; i--) {
    if (logData[i][0] === 'VERWIJDEREN_LOGBOEK') {
      logSheet.deleteRow(i + 1);
    }
  }

  // 清空Dossier表的G2:G6
  ss.getSheetByName("-Dossier").getRangeList(['G2:G6']).clearContent();
}

步骤2：部署为Web App

打开Google表格的脚本编辑器（工具→脚本编辑器）
点击右上角「部署」→「新部署」
在部署设置中：
- 类型选择「Web应用」
- 执行：选择「我」（即表格所有者，确保脚本以你的权限运行）
- 谁可以访问：根据需求选择「任何人，甚至匿名」或「组织内任何人」
- 点击「部署」，复制生成的Web App URL

步骤3：替换表格内的触发方式

在表格中插入绘制按钮（插入→绘制），设计「新建档案」和「删除档案」按钮
选中按钮，点击右上角「链接」，分别粘贴以下URL：
- 新建档案：你的WebAppURL?action=create
- 删除档案：你的WebAppURL?action=delete
用户点击按钮后会打开新窗口，执行对应操作并返回结果提示

内容的提问来源于stack exchange，提问作者Thomas van Dooremaal

如何让Google Script以表格所有者权限执行？权限问题求助

问题说明 #

原新建表格脚本 #

原删除表格脚本 #

解决方案：通过Web App以所有者权限运行脚本 #

步骤1：修改脚本适配Web App #

步骤2：部署为Web App #

步骤3：替换表格内的触发方式 #