如何让其他用户在受保护的Google表格中运行App Script函数

问题描述 #

我是Google App Script新手，已创建一个允许成员通过表单提交数据的函数。我希望保护存储表单提交数据的Data工作表，防止其他用户在提交后修改数据。但锁定该工作表后，其他用户运行函数时会出现以下错误：

Exception: You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit.

我看过类似问题的解答，但难以将自己的脚本修改成符合需求的解决方案。我了解可能需要使用doGet函数，但还未深入学习该函数。能否请您帮助我解决这个问题？

原代码 #

var workBook = SpreadsheetApp.getActiveSpreadsheet();
var codesBook = SpreadsheetApp.openById("1L6ERNbbH1kbQtGnhgcsYKafkP57U8OSMNJQniF4R9_Y")
var formSheet = workBook.getSheetByName("Form")
var dataSheet = workBook.getSheetByName("Data")
var printOut = workBook.getSheetByName("Print Out")
var passwordsBook = SpreadsheetApp.openById('16CCT17bh4Gyu3zPV00a9T9cVPup0I_oLa94QkMEs_qc')
var passwordsSheet = passwordsBook.getSheetByName("Sheet1")

function submitForm(password, branch, index) {
  var ui = SpreadsheetApp.getUi();
  var data = formSheet.getRange("B3:C11").getValues();
  if ((data[4][1] + data[5][1] + data[6][1] + data[7][1] + data[8][1]) > 5) {
    ui.alert("Max 5 transactions are allowed only.")
    return
  }
  if (data[0][0].toString().trim().length == 0 || data[1][0].toString().trim().length == 0 && data[2][0].toString().trim().length == 0 || data[3][0].toString().trim().length == 0 || (data[4][1] + data[5][1] + data[6][1] + data[7][1] + data[8][1]) < 1) {
    ui.alert("Please fill the form")
  }
  else {
    var output = []
    var errorMessage = [];
    for (var i = 4; i < data.length; i++) {
      if (data[i][1] == 0)
        continue;
      var codesSheet = codesBook.getSheetByName(data[i][0])
      var codes = codesSheet.getDataRange().getValues();
      var countCodes = codes.filter((row) => row[2] != "VOID" && row[2] != true)
      if (countCodes.length - 1 < data[i][1]) {
        if ((countCodes.length - 1) == 0)
          errorMessage.push("No Codes are available for " + data[i][0] + " denomination")
        else
          errorMessage.push("Only " + (countCodes.length - 1) + " are remaining for " + data[i][0] + " denomination")
      }
      else {
        count = 0;
        for (var c = 1; c < codes.length; c++) {
          if (count == data[i][1])
            break;
          else if (codes[c][2] != "VOID" && codes[c][2] != true) {
            output.push([Utilities.getUuid(), data[0][0], data[1][0], data[2][0], data[3][0], data[i][0], codes[c][1], password, branch, "Hi " + data[0][0] + " please see your purchased wallet code of P" + data[i][0] + " below:" + codes[c][1]])
            codes[c][2] = true;
            count++;
          }
        }
        codesSheet.getRange(1, 1, codes.length, codes[0].length).setValues(codes)
      }
    }

    if (errorMessage.length > 0)
      ui.alert(errorMessage.join("\n"))
    else {
      if (output.length > 0) {
        dataSheet.getRange(dataSheet.getLastRow() + 1, 1, output.length, output[0].length).setValues(output)
        var printOutData = output.map((r) => [r[0], r[5], r[6]])
        printOutData.push(['Date of Purchase', new Date(), ''])
        printOut.getRange("A7:C").clearFormat().clearContent()
        printOut.getRange("A1").setValue("Hi, " + output[0][1])
        printOut.getRange(7, 1, printOutData.length, printOutData[0].length).setValues(printOutData).setNumberFormat("@").setFontColor("blue").setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID).setHorizontalAlignment("center")
        SpreadsheetApp.flush();
        var lr = printOut.getLastRow();

        printOut.getRange(lr, 1).setFontColor("black").setFontWeight("bold")
        printOut.getRange("B" + lr + ":C" + lr).merge()

        var note = [['Note: Should you have any issues in using the voucher codes, please raise the issue'], ['24 hours of purchase. Please present this printout to the Grab representative to '], ['validate your purchase']]

        var instructions = [['Instructions:'], ['1. Pumunta sa iyong Cash Wallet sa Grab Driver app at i-click ang Top-up.'], ['2. Ilagay ang amount na gusto mong i-top up at pindutin ang Next.'], ['3. Pindutin ang Top Up Now para makumpleto ang transaction.']]
        printOut.getRange(lr + 2, 1, note.length, 1).setValues(note).setFontColor("red").setFontWeight("bold")
        printOut.getRange(lr + 6, 1, instructions.length, 1).setValues(instructions).setFontColor("black").setFontWeight("bold")
      }
      formSheet.getRange("B3:C6").clearContent()
      formSheet.getRange("C7:C11").clearContent()
      ui.alert("All codes assigned successfully.")
    }
  }
}

function authenticate() {
  var passwords = passwordsSheet.getDataRange().getValues();
  console.log(passwords)
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Please enter the password to continue...");

  var button = result.getSelectedButton();

  if (button === ui.Button.OK) {
    Logger.log("The user clicked the [OK] button.");
    var pwd = result.getResponseText();
    if (pwd.trim() == "") {
      ui.alert("Password must not be empty.")
      return
    }
    var matched = false
    for (var i = 0; i < passwords.length; i++) {
      if (passwords[i][0].toString().trim() == pwd.trim()) {
        matched = true;
        submitForm(passwords[i][0], passwords[i][1], i + 1)
      }
    }
    if (!matched) {
      ui.alert("Incorrect Password!")
    }
  } else if (button === ui.Button.CLOSE) {
    Logger.log("The user clicked the [X] button and closed the prompt dialog.");
  }
}

错误原因 #

普通用户直接运行脚本时，脚本默认以当前用户的权限执行。当Data工作表被保护后，普通用户没有编辑受保护单元格的权限，因此写入操作触发报错。

解决方案：使用可安装触发器（推荐） #

可安装触发器会以**创建触发器的用户（即表格/脚本所有者）**的权限执行，这样即使普通用户运行脚本，也能绕过工作表保护，完成Data表的写入操作。具体步骤如下：

1. 添加自定义菜单函数 #

在原代码末尾添加以下函数，用于生成自定义菜单，方便用户触发认证流程：

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('表单提交')
    .addItem('验证密码并提交', 'authenticate')
    .addToUi();
}

2. 创建可安装的onOpen触发器 #

1. 打开脚本编辑器，点击左侧栏的「触发器」图标（时钟样式）
2. 点击「添加触发器」按钮
3. 配置触发器参数：
   • 选择要运行的函数：onOpen
   • 部署来源：Head
   • 事件源：从电子表格
   • 事件类型：打开时
   • 运行权限：选择「以我（你的邮箱账号）的身份运行」
4. 保存触发器，按提示完成授权操作

3. 验证效果 #

普通用户打开表格后，会看到顶部菜单栏新增「表单提交」选项，点击「验证密码并提交」即可正常运行脚本，写入受保护的Data工作表，不会再触发权限错误。

备选方案：部署为Web App（无弹窗交互场景） #

如果后续希望替换弹窗交互为网页表单，可以将脚本部署为Web App：

1. 编写doGet函数生成HTML表单页面，doPost函数处理表单提交逻辑（替代原有的authenticate和submitForm）
2. 部署Web App时，设置「执行权限」为「以我（你的账号）的身份运行」，「谁可以访问」设置为「任何人，甚至匿名」
3. 用户通过访问Web App链接提交数据，后台以所有者权限写入Data表

内容的提问来源于stack exchange，提问作者din2345