如何阻止删除含Global-ID的Google Sheets行？

我有一个基于单元格值删除行的脚本，绑定到包含3列的工作表：

• A列：Global-ID
• B列：Local-ID
• C列：itemName

需要阻止用户删除带有Global-ID的行，具体逻辑：

• 当提交removeItemFrom表单时
• 检查对应行的A列单元格是否为空
• 若A列单元格不为空（即存在Global-ID），则返回错误，禁止删除

JavaScript 代码 #

function showDeleteItem() {
  const ui = SpreadsheetApp.getUi();
  var html = HtmlService.createTemplateFromFile('DeleteItemHTML')
  .evaluate();
  html.setTitle("Delete Item")
  ui.showSidebar(html);
}


function getItemName(formObject) {
  const locItemID = formObject.itemLocalID;
  const SHEET = getItemSheet();
  const RANGE = SHEET.getDataRange();
  const DELETE_VAL = locItemID;
  const ITEMNAMECOL = 3;
  const LOCAL_ID = 2;
  const rangeVals = RANGE.getValues();
  for (var i = rangeVals.length - 1; i >= 0; i--) {
    if (rangeVals[i][LOCAL_ID] === DELETE_VAL) {
      return {
        i,
        itemName: rangeVals[i][ITEMNAMECOL],
      };
    }
  }
  return {i, itemName: "Item not found"};
}
function getItemSheet() {
  const SS = SpreadsheetApp.openById(
    '1GSzlzj7nHPIUt-RIJfsPFobtnLbuoXedtJk1x11BdT0'
  );
  const SHEET = SS.getSheetByName('Inventory');
  return SHEET;
}
function deleteItems(i) {
  getItemSheet().deleteRow(i + 1);
}

HTML 代码 #

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Stylesheet'); ?>
    <?!= include('jQuery'); ?>
  </head>
  <body>
  <div class="sidebarwrapper">
      <div class="xbuttonwrapper">
          <button class="xbutton" onclick="google.script.host.close()">
              <svg class="x" enable-background="new 0 0 212.982 212.982" viewBox="0 0 212.98 212.98" xml:space="preserve" xmlns="http://www.w3.org/2000/svg"><path d="m131.8 106.49 75.936-75.936c6.99-6.99 6.99-18.323 0-25.312-6.99-6.99-18.322-6.99-25.312 0l-75.937 75.937-75.937-75.938c-6.99-6.99-18.322-6.99-25.312 0-6.989 6.99-6.989 18.323 0 25.312l75.937 75.936-75.937 75.937c-6.989 6.99-6.989 18.323 0 25.312 6.99 6.99 18.322 6.99 25.312 0l75.937-75.937 75.937 75.937c6.989 6.99 18.322 6.99 25.312 0s6.99-18.322 0-25.312l-75.936-75.936z" clip-rule="evenodd" fill-rule="evenodd"/></svg>
          </button>
      </div>
      <div class="titlewrapper">
          <img class="ctlogotitle" src="https://i.imgur.com/d1VMjvs.png">
          <h1 class="title">Artikel Entfernen</h1>
      </div>
      <div class="divider"></div>
      <form class="inputformwrapper" id="removeItemFrom">          
          <div class="inputblockwrapper">
              <div class="labelwrapper">
                  <label class="requiredlabel" for="itemLocalID">Lokale ID</label>
              </div>
              <input class="inputfield" 
                  type="text"
                  placeholder="PREF000001..."
                  minlength="10"
                  maxlength="10"                
                  id="itemLocalID"
                  name="itemLocalID"                    
                  required>
          </div>
          <div class="confirmbuttonwrapper">
              <input class="confirmbutton" 
                  type="submit" 
                  value="Entfernen"                
                  id="removeItem">
          </div>
      </form>
  </div>
  <script>
      document.querySelector("#removeItemFrom").addEventListener("submit", function (e) {
        e.preventDefault();
        google.script.run.withSuccessHandler(({i, itemName}) => {
          const confirmString = 'Are you sure you want to delete "' + itemName + '"?';
          if (confirm(confirmString)) {
            google.script.run.deleteItems(i);
            $('#removeItemFrom').trigger("reset");
          } else {
            $('#removeItemFrom').trigger("reset");
          }
        }).getItemName(this)
      });
  </script>
  </body>
</html>

修改后的JavaScript代码 #

function showDeleteItem() {
  const ui = SpreadsheetApp.getUi();
  var html = HtmlService.createTemplateFromFile('DeleteItemHTML')
  .evaluate();
  html.setTitle("删除条目")
  ui.showSidebar(html);
}


function getItemName(formObject) {
  const locItemID = formObject.itemLocalID;
  const SHEET = getItemSheet();
  const RANGE = SHEET.getDataRange();
  const DELETE_VAL = locItemID;
  const ITEMNAMECOL = 2; // 数组索引从0开始，对应C列
  const LOCAL_ID = 1; // 对应B列
  const GLOBAL_ID_COL = 0; // 对应A列
  const rangeVals = RANGE.getValues();
  for (var i = rangeVals.length - 1; i >= 0; i--) {
    if (rangeVals[i][LOCAL_ID] === DELETE_VAL) {
      // 检查Global-ID是否存在
      const hasGlobalId = rangeVals[i][GLOBAL_ID_COL] !== "" && rangeVals[i][GLOBAL_ID_COL] !== undefined;
      return {
        i,
        itemName: rangeVals[i][ITEMNAMECOL],
        hasGlobalId: hasGlobalId
      };
    }
  }
  return {i: -1, itemName: "未找到该条目", hasGlobalId: false};
}
function getItemSheet() {
  const SS = SpreadsheetApp.openById(
    '1GSzlzj7nHPIUt-RIJfsPFobtnLbuoXedtJk1x11BdT0'
  );
  const SHEET = SS.getSheetByName('Inventory');
  return SHEET;
}
function deleteItems(i) {
  // 二次验证，防止前端绕过
  const SHEET = getItemSheet();
  const row = i + 1;
  const globalId = SHEET.getRange(row, 1).getValue();
  if (globalId === "") {
    SHEET.deleteRow(row);
  }
}

修改后的HTML代码 #

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Stylesheet'); ?>
    <?!= include('jQuery'); ?>
  </head>
  <body>
  <div class="sidebarwrapper">
      <div class="xbuttonwrapper">
          <button class="xbutton" onclick="google.script.host.close()">
              <svg class="x" enable-background="new 0 0 212.982 212.982" viewBox="0 0 212.98 212.98" xml:space="preserve" xmlns="http://www.w3.org/2000/svg"><path d="m131.8 106.49 75.936-75.936c6.99-6.99 6.99-18.323 0-25.312-6.99-6.99-18.322-6.99-25.312 0l-75.937 75.937-75.937-75.938c-6.99-6.99-18.322-6.99-25.312 0-6.989 6.99-6.989 18.323 0 25.312l75.937 75.936-75.937 75.937c-6.989 6.99-6.989 18.323 0 25.312 6.99 6.99 18.322 6.99 25.312 0l75.937-75.937 75.937 75.937c6.989 6.99 18.322 6.99 25.312 0s6.99-18.322 0-25.312l-75.936-75.936z" clip-rule="evenodd" fill-rule="evenodd"/></svg>
          </button>
      </div>
      <div class="titlewrapper">
          <img class="ctlogotitle" src="https://i.imgur.com/d1VMjvs.png">
          <h1 class="title">删除条目</h1>
      </div>
      <div class="divider"></div>
      <form class="inputformwrapper" id="removeItemFrom">          
          <div class="inputblockwrapper">
              <div class="labelwrapper">
                  <label class="requiredlabel" for="itemLocalID">本地ID</label>
              </div>
              <input class="inputfield" 
                  type="text"
                  placeholder="MUCH00000001..."
                  minlength="10"
                  maxlength="10"                
                  id="itemLocalID"
                  name="itemLocalID"                    
                  required>
          </div>
          <div class="confirmbuttonwrapper">
              <input class="confirmbutton" 
                  type="submit" 
                  value="删除"                
                  id="removeItem">
          </div>
      </form>
  </div>
  <script>
      document.querySelector("#removeItemFrom").addEventListener("submit", function (e) {
        e.preventDefault();
        google.script.run.withSuccessHandler(({i, itemName, hasGlobalId}) => {
          if (hasGlobalId) {
            alert("错误：无法删除带有Global-ID的行！");
            $('#removeItemFrom').trigger("reset");
            return;
          }
          if (itemName === "未找到该条目") {
            alert(itemName);
            $('#removeItemFrom').trigger("reset");
            return;
          }
          const confirmString = `确定要删除 "${itemName}" 吗？`;
          if (confirm(confirmString)) {
            google.script.run.deleteItems(i);
            $('#removeItemFrom').trigger("reset");
          } else {
            $('#removeItemFrom').trigger("reset");
          }
        }).getItemName(this)
      });
  </script>
  </body>
</html>

修改说明 #

1. 修正索引错误：原代码列索引使用1-based规则，改为JavaScript数组的0-based索引，确保列匹配正确。
2. 增加Global-ID检查：在getItemName中判断对应行A列值是否为空，返回hasGlobalId标记。
3. 前端拦截逻辑：表单提交后先判断hasGlobalId，若为true则弹出错误提示，阻止删除。
4. 后端二次验证：deleteItems中再次检查Global-ID，避免用户绕过前端直接调用函数删除行。
5. 界面本地化：将德语界面文本改为中文，提升使用体验。

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