Google Apps Script仅本人可用，其他用户触发按钮报错求助

我为某历史协会开发了一款归档记录用Google表格：首个标签页为数据录入表单，其余标签页展示不同视图的归档记录，最后一个标签页用于临时数据存储。表格配套的Google Apps Script可实现归档主题选择、表单数据保存/更新、记录查询、放弃录入等功能，本人使用完全正常。但当其他成员（已获取编辑权限并完成脚本授权）操作Save/Update、Delete、Exit按钮时，会触发无详情的通用错误，提示内容多为‘发生错误’或‘尝试编辑受保护单元格’。

Save/Update按钮脚本 #

// Function to get values from all form fields and post to the Master Archive List
// for a new entry. It will update the entry if this is a change process
function getandPostFormData()  
{                              
// Set initial settings for variables
const ss = SpreadsheetApp.getActiveSpreadsheet();
const avcWS = ss.getSheetByName('Add/View/Change');
const listWS = ss.getSheetByName('Master Archive List');
const settingsWS = ss.getSheetByName('Settings');
var CurrentRow = settingsWS.getRange("L4").getValue();  // == current row of exiating record (if one has been found)

// Check that all fields are correctly entered ...
// first all topic entries are set to non-blank.

var checkitcell = avcWS.getRange('C7'); // Check if the Topic has been selected
var checkit = checkitcell.getValue();
var checkitlength = checkit.length;

if(checkitlength == 0)
{
alertMessage('Sorry, you need to select a Main Topic for this archive item.',1);
return;
}

var checkitcell = avcWS.getRange('C8'); // Check if the Sub Topic 1 has been selected
var checkit = checkitcell.getValue();
var checkitlength = checkit.length;

if(checkitlength == 0)
{
alertMessage('Sorry, you need to select a Sub-topic 1 for this archive item.',1);
return;
}

var checkitcell = avcWS.getRange('C9'); // Check if the sub-topic 2 has been selected
var checkit = checkitcell.getValue();
var checkitlength = checkit.length;

if(checkitlength == 0)
{
alertMessage('Sorry, you need to select the last Sub-topic 2 as well.',1);
return;
}

// Check if the title has been entered
var checkitcell = avcWS.getRange('C12'); // Check if title is blank
var checkit = checkitcell.getValue();
var checkitlength = checkit.length;

if(checkitlength == 0)
{
alertMessage('Sorry, please give a Title to this archived item.',1);
return;
}

// Now check if the actual location is blank
var checkitcell = avcWS.getRange('E15'); // Check if locaton is blank
var checkit = checkitcell.getValue();
var checkitlength = checkit.length;

if(checkitlength == 0)
{
alertMessage('Sorry, the Actual Location of the archived item needs to be given.',1);
return;
}

// Check if any entry has changed for existing record and update if they have
if(settingsWS.getRange("L3").getValue() === "E")
{
    if(settingsWS.getRange("L6").getValue() != avcWS.getRange("C5").getValue())        // Status
      {listWS.getRange(CurrentRow,1).setValue(settingsWS.getRange("L6").getValue());}  
    if(settingsWS.getRange("L7").getValue() != avcWS.getRange("C7").getValue())        // Topic
      {listWS.getRange(CurrentRow,2).setValue(settingsWS.getRange("L7").getValue());}
    if(settingsWS.getRange("L8").getValue() != avcWS.getRange("C8").getValue())        // Sub 1
      {listWS.getRange(CurrentRow,3).setValue(settingsWS.getRange("L8").getValue());}
    if(settingsWS.getRange("L9").getValue() != avcWS.getRange("C9").getValue())        // Sub 2
      {listWS.getRange(CurrentRow,4).setValue(settingsWS.getRange("L9").getValue());}
    if(settingsWS.getRange("L10").getValue() != avcWS.getRange("C12").getValue())      // Title
      {listWS.getRange(CurrentRow,5).setValue(settingsWS.getRange("L10").getValue());}
    if(settingsWS.getRange("L11").getValue() != avcWS.getRange("E12").getValue())      // Description
      {listWS.getRange(CurrentRow,6).setValue(settingsWS.getRange("L11").getValue());}  
    if(settingsWS.getRange("L12").getValue() != avcWS.getRange("C14").getValue())      // Record Typr
      {listWS.getRange(CurrentRow,7).setValue(settingsWS.getRange("L12").getValue());}
    if(settingsWS.getRange("L13").getValue() != avcWS.getRange("C15").getValue())      // Location Type
      {listWS.getRange(CurrentRow,8).setValue(settingsWS.getRange("L13").getValue());}
    if(settingsWS.getRange("L14").getValue() != avcWS.getRange("E15").getValue())      // Actual Location
      {listWS.getRange(CurrentRow,9).setValue(settingsWS.getRange("L14").getValue());}

   // Now clear down the stored details just for ensuring there are no problems later
   settingsWS.getRange("L4:L14").clearContent()  // Not the record type though!
}
if(settingsWS.getRange("L3").getValue() === "N")     // New record logic ...
 {
    console.log(settingsWS.getRange("L3").getValue())
    
    // Next ID logic
    const IDCell = avcWS.getRange('C4');
    const nextIDCell = settingsWS.getRange("G3");
    var nextID = IDCell.getValue();
    IDCell.setValue(nextID); // set C4 to the next value

    // Set these cells for writing the record as they are not entered
    avcWS.getRange("C5").setValue("Live"); // set C5 record status to Live

    // Set up the array for writing / updating the entry recod
    const fieldRange = ["C5","C7","C8","C9","C12","E12","C14","C4","C15","E15"];
    const fieldValues = fieldRange.map(f => avcWS.getRange(f).getValue());

    // Now write next record in the Master Archive List tab
    listWS.appendRow(fieldValues);  // Write a new row in the Master Archive List with the new entry details / record
    nextIDCell.setValue(nextID+1);  // Add 1 to the set the next ID for the next cycle
 }

clearFormData();  // Now clear down cells and display the next ID
settingsWS.getRange("L3").setValue("N");  // Assume next entry will be a new record. It will be changed to E
                                           // when existing one searched for.

} // End of getandPostFormData function


function clearFormData()  // Function to clear the details and redisplay ID
{
  // Set initial settings for variables
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const avcWS = ss.getSheetByName('Add/View/Change');
  const settingsWS = ss.getSheetByName('Settings');
  const IDCell = avcWS.getRange('C4');
  const nextIDCell = settingsWS.getRange("G3").getValue();
  const fieldRange = ["C5","C7","C8","C9","C12","E12","C14","C4","C15","E15","E4"];
  fieldRange.forEach(f => avcWS.getRange(f).clearContent());  // Clear all cells

  // Redisplay the next ID
  IDCell.setValue(nextIDCell); // set C4 to the next value ready for next input cycle

}  // End of function to clear the form details for the next entry cycle

Exit按钮脚本 #

// Function when the Exit button is pressed.
function ExitForm()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var avcWS = ss.getSheetByName('Add/View/Change');
var listWS = ss.getSheetByName('Master Archive List');
var settingsWS = ss.getSheetByName('Settings');
var changed = "N";   // Used to denote some field has been changed

// Anything changed?
if(settingsWS.getRange("L6").getValue() != avcWS.getRange("C5").getValue())        // Status
  {changed ="Y";}  
if(settingsWS.getRange("L7").getValue() != avcWS.getRange("C7").getValue())        // Topic
  {changed ="Y";}
if(settingsWS.getRange("L8").getValue() != avcWS.getRange("C8").getValue())        // Sub 1
  {changed ="Y";}
if(settingsWS.getRange("L9").getValue() != avcWS.getRange("C9").getValue())        // Sub 2
  {changed ="Y";}
if(settingsWS.getRange("L10").getValue() != avcWS.getRange("C12").getValue())      // Title
  {changed ="Y";}
if(settingsWS.getRange("L11").getValue() != avcWS.getRange("E12").getValue())      // Description
  {changed ="Y";}  
if(settingsWS.getRange("L12").getValue() != avcWS.getRange("C14").getValue())      // Record Typr
  {changed ="Y";}
if(settingsWS.getRange("L13").getValue() != avcWS.getRange("C15").getValue())      // Location Type
  {changed ="Y";}
if(settingsWS.getRange("L14").getValue() != avcWS.getRange("E15").getValue())      // Actual Location
  {changed ="Y";}

if(settingsWS.getRange("L3").getValue() == "N" && changed == "Y")  // This is a new entry being abandoned.
                                                                    // Note stored fields will be blank
  {
   if(alertMessage('You appear to have started to enter a new entry. Abandon it?',2) == "Y")
     {clearFormData();
   }
  }

if(settingsWS.getRange("L3").getValue() == "E" && changed == "Y")  // This is an existing that has changed being abandoned.
                                                                    // Note stored fields will be original entry
  {
  if(alertMessage('You appear to have abandoned changes to an existing entry. Abandon it?',2) == "Y")
    {clearFormData();}
  }

  if(changed == "N")    // As nothing has changed, simply clear form data and start again for the next cycle
    {
      clearFormData()
    // Now clear down the stored details just for ensuring there are no problems later
      settingsWS.getRange("L4:L14").clearContent()  // Not the record type though!
      settingsWS.getRange("L3").setValue("N");  // Assume next entry will be a new record.
    }

} // end of Exit function

Delete按钮脚本 #

// Function when button Delete / Reinstate has been pressed.
function DeleteReinstate()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var avcWS = ss.getSheetByName('Add/View/Change');
var listWS = ss.getSheetByName('Master Archive List');
var settingsWS = ss.getSheetByName('Settings');

if(settingsWS.getRange("L3").getValue() == "N")  // Cannot delete / reinstate an entry that has not been created!!
{
  alertMessage('You cannot delete or reinstate an entry that has not been fully entered, yet.',1)
  return;
}

if(settingsWS.getRange("L3").getValue() == "E" && settingsWS.getRange("L6").getValue() == 'Live')  // existing & live
{
  if(alertMessage('Are you sure you want to change the Status to deleted?',2) == "Y")
    {
      avcWS.getRange("C5").setValue('Deleted Entry');  // Update form cell
      settingsWS.getRange("L6").setValue('Deleted Entry');  // Then tempory store
      listWS.getRange(settingsWS.getRange("L4").getValue(),1).setValue('Deleted Entry');  // and in record row too
      return;
    }
} else {  // existing & deleted
  if(alertMessage('Are you sure you want to change the Status back to Live?',2) == "Y")
    {
      avcWS.getRange("C5").setValue('Live');  // Update form cell
      settingsWS.getRange("L6").setValue('Live');  // Then tempory store
      listWS.getRange(settingsWS.getRange("L4").getValue(),1).setValue('Live');  // and in record row too
    }


} // end of Deleted / reinstatefunction

核心原因 #

1. 工作表保护限制：你作为表格所有者默认拥有所有单元格编辑权限，但其他成员可能被工作表保护规则限制，无法修改Settings的L列、Master Archive List的记录行等区域，脚本执行时以用户权限操作，触发“尝试编辑受保护单元格”错误。
2. 脚本执行权限不足：其他用户授权脚本时仅获得基础权限，未允许修改受保护范围；且脚本未配置为以所有者身份运行，导致执行时受限于用户自身权限。
3. 缺失alertMessage函数：脚本中调用了alertMessage但未定义该函数，其他用户执行时会触发未定义函数错误，表现为“发生错误”。

解决步骤 #

1. 调整工作表保护规则 #

• 打开表格，进入Settings和Master Archive List工作表，点击数据 > 保护工作表和范围。
• 针对脚本需修改的范围（如Settings的L3:L14、Master Archive List全表），修改保护规则：
  • 在“权限”中选择仅特定用户，添加所有协会成员；或勾选允许编辑范围，将脚本操作的单元格加入允许列表，确保成员可通过脚本编辑这些区域。

2. 配置脚本以所有者身份运行（推荐） #

• 打开脚本编辑器，点击部署 > 测试部署，创建新部署：
  • 类型选Web应用，执行权限设为我（表格所有者），访问权限设为组织内任何人（或按需调整）。
  • 部署后，将按钮关联的脚本改为调用该Web应用；或在脚本编辑器的项目设置中启用appsscript.json清单文件，添加"oauthScopes": ["https://www.googleapis.com/auth/spreadsheets"]确保权限充足，再重新部署。

3. 补充alertMessage函数 #

在脚本中添加以下函数，确保弹窗功能正常：

function alertMessage(message, type) {
  const ui = SpreadsheetApp.getUi();
  if (type === 1) {
    ui.alert(message);
    return "";
  } else if (type === 2) {
    const response = ui.alert(message, ui.ButtonSet.YES_NO);
    return response === ui.Button.YES ? "Y" : "N";
  }
}

4. 优化脚本兼容性 #

• 减少单独调用getRange/setValue的次数，改用getValues/setValues批量读写，降低权限校验触发频率并提升性能。
• 对CurrentRow等变量添加类型校验，避免空值导致的范围错误：

  var CurrentRow = settingsWS.getRange("L4").getValue();
  if (typeof CurrentRow !== 'number' || CurrentRow < 1) {
    alertMessage('Invalid record row, please try searching again.', 1);
    return;
  }
  

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