如何让Google脚本在受保护的Google表格工作表中运行

问题根源 #

工作表启用保护后，默认会拦截所有非授权编辑操作——哪怕脚本是你创建的，也会被保护规则限制，除非你明确授权脚本的运行账户拥有编辑权限，或者在脚本中临时处理保护规则。

两种可行解决方案 #

方案1：调整保护设置，允许脚本运行账户编辑 #

无需修改代码，直接通过表格设置解决：

• 打开目标表格，右键需要保护的工作表标签（比如DADOS XX系列工作表），选择「保护工作表和范围」
• 在右侧面板切换到「权限」标签页，选择「仅特定用户可编辑」
• 添加你自己的Google账号（脚本默认以你的账号权限运行），保存设置后脚本即可正常写入

方案2：脚本中临时移除保护再恢复 #

如果不想调整全局保护规则，可以在脚本执行写入操作前后，临时移除保护，完成后自动恢复：

修改后的完整代码如下：

// @OnlyCurrentDoc

function REGISTRODEATIVIDADE() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const formularioSheet = spreadsheet.getSheetByName("FORMULÁRIO");
  const state = formularioSheet.getRange("A2").getValue();
  const city = formularioSheet.getRange("C2").getValue();
  const valuesToCopy = formularioSheet.getRange("D2:G2").getValues()[0];

  const targetSheetName = "DADOS " + state;
  const targetSheet = spreadsheet.getSheetByName(targetSheetName);
  if (!targetSheet) return;

  // 临时处理工作表保护
  let protection = targetSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  let originalEditors = [];
  let isWarningOnly = false;
  // 存在保护则先移除并记录原设置
  if (protection) {
    originalEditors = protection.getEditors();
    isWarningOnly = protection.isWarningOnly();
    protection.remove();
  }

  try {
    // 原数据写入逻辑
    const cityColumn = 3;
    const targetCellRow = findTargetRow(targetSheet, city, cityColumn);

    if (targetCellRow !== -1) {
      const targetCell = targetSheet.getRange(targetCellRow, 4);

      if (targetCell.getValue() === "") {
        targetSheet.getRange(targetCellRow, 4, 1, valuesToCopy.length).setValues([valuesToCopy]);
      } else {
        const newRow = targetCellRow + 1;
        targetSheet.insertRowAfter(targetCellRow);
        const newCell = targetSheet.getRange(newRow, 3);
        newCell.setValue(targetCell.getValue());
        targetSheet.getRange(newRow, 4, 1, valuesToCopy.length).setValues([valuesToCopy]);
        
        targetSheet.getRange(targetCellRow, 3, 2, 1).merge();
      }
    }
  } finally {
    // 无论操作成功与否，都恢复原保护设置
    if (protection) {
      const newProtection = targetSheet.protect();
      newProtection.setEditors(originalEditors);
      newProtection.setWarningOnly(isWarningOnly);
    }
  }
}

function findTargetRow(sheet, city, cityColumn) {
  const data = sheet.getDataRange().getValues();
  for (let i = 0; i < data.length; i++) {
    if (data[i][cityColumn - 1] === city) {
      return i + 1;
    }
  }
  return -1;
}

代码说明 #

• 用try...finally确保保护设置一定会被恢复，避免表格长时间处于无保护状态
• 完整记录并还原了原保护的编辑者列表、「仅警告」模式等设置
• 如果原保护是针对特定单元格范围而非整个工作表，可额外添加代码获取并恢复保护范围，按需调整即可

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