Google Sheets自动化需求：行格式设置与Protected Range动态调整

针对你提到的四个自动化需求，全部通过Google Apps Script实现，替代手动条件格式，具体代码和操作步骤如下：

1. 整行数据匹配标绿、不匹配标红（空行不染色） #

假设表格A列为成员姓名，判断逻辑为：行内所有非空单元格内容与A列姓名一致则标绿，存在不一致内容则标红。若你的判断逻辑不同，可自行修改代码中matchValue的取值和判断条件。

function formatRowColor() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  const range = sheet.getRange(1, 1, lastRow, lastCol);
  const values = range.getValues();
  
  // 遍历每一行
  for (let i = 0; i < values.length; i++) {
    const row = values[i];
    const isEmptyRow = row.every(cell => cell === "");
    if (isEmptyRow) continue; // 空行跳过
    
    const matchValue = row[0]; // 取A列值作为匹配基准
    const allMatch = row.every(cell => cell === "" || cell === matchValue);
    
    const rowRange = sheet.getRange(i + 1, 1, 1, lastCol);
    if (allMatch) {
      rowRange.setBackground("#00FF00"); // 绿色
    } else {
      rowRange.setBackground("#FF0000"); // 红色
    }
  }
}

这个函数可通过onEdit触发器设置，每次编辑表格时自动执行，实时更新行颜色。

2. 新增数据行30分钟后自动扩展保护范围 #

需结合编辑触发器记录新增行信息，再用时间驱动触发器延迟执行扩展操作：

步骤1：记录新增行的时间 #

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const editedRow = e.range.getRow();
  const rowValues = sheet.getRange(editedRow, 1, 1, sheet.getLastColumn()).getValues()[0];
  const wasEmptyBefore = rowValues.every(cell => cell === "");
  
  // 仅当编辑的是之前为空的行时记录
  if (wasEmptyBefore) {
    const props = PropertiesService.getDocumentProperties();
    const pendingRows = props.getProperty("pendingProtectedRows") ? JSON.parse(props.getProperty("pendingProtectedRows")) : [];
    // 记录行号和30分钟后的执行时间
    pendingRows.push({row: editedRow, executeTime: Date.now() + 30 * 60 * 1000});
    props.setProperty("pendingProtectedRows", JSON.stringify(pendingRows));
    
    // 自动设置时间驱动触发器（避免重复创建）
    setupTimeDrivenTrigger();
  }
}

步骤2：创建时间驱动触发器 #

function setupTimeDrivenTrigger() {
  const triggers = ScriptApp.getProjectTriggers();
  const hasTrigger = triggers.some(trigger => trigger.getHandlerFunction() === "expandProtectedRange");
  if (!hasTrigger) {
    ScriptApp.newTrigger("expandProtectedRange")
      .timeBased()
      .everyMinutes(1) // 每分钟检查一次待处理行
      .create();
  }
}

步骤3：执行扩展保护范围操作 #

function expandProtectedRange() {
  const props = PropertiesService.getDocumentProperties();
  let pendingRows = props.getProperty("pendingProtectedRows") ? JSON.parse(props.getProperty("pendingProtectedRows")) : [];
  const now = Date.now();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const protection = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)[0]; // 假设仅存在一个保护范围
  
  if (!protection) return;
  
  // 筛选出已到执行时间的行
  const rowsToProcess = pendingRows.filter(item => item.executeTime <= now);
  if (rowsToProcess.length === 0) return;
  
  // 获取当前保护范围的最后一行
  const currentProtectedRange = protection.getRange();
  const currentLastRow = currentProtectedRange.getLastRow();
  
  // 扩展保护范围至最大的待处理行
  const maxRow = Math.max(...rowsToProcess.map(item => item.row));
  if (maxRow > currentLastRow) {
    const newRange = sheet.getRange(currentProtectedRange.getRow(), currentProtectedRange.getColumn(), maxRow - currentProtectedRange.getRow() + 1, currentProtectedRange.getNumColumns());
    protection.setRange(newRange);
  }
  
  // 移除已处理的行记录
  pendingRows = pendingRows.filter(item => item.executeTime > now);
  props.setProperty("pendingProtectedRows", JSON.stringify(pendingRows));
}

3. 保护范围内行变空时，自动排序并缩小保护范围 #

通过时间驱动触发器定时检查处理：

function shrinkProtectedRangeAndSort() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const protection = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)[0];
  if (!protection) return;
  
  const protectedRange = protection.getRange();
  const startRow = protectedRange.getRow();
  const endRow = protectedRange.getLastRow();
  const colCount = protectedRange.getNumColumns();
  
  // 获取保护范围内的所有行数据
  const range = sheet.getRange(startRow, 1, endRow - startRow + 1, colCount);
  const values = range.getValues();
  
  // 过滤出非空行
  const nonEmptyRows = values.filter(row => !row.every(cell => cell === ""));
  
  if (nonEmptyRows.length === 0) {
    // 全为空行时移除保护范围
    protection.remove();
    return;
  }
  
  // 按A列从A到Z排序
  nonEmptyRows.sort((a, b) => a[0].localeCompare(b[0]));
  
  // 将排序后的数据写回表格
  range.clearContent();
  sheet.getRange(startRow, 1, nonEmptyRows.length, colCount).setValues(nonEmptyRows);
  
  // 缩小保护范围至非空行的最后一行
  const newEndRow = startRow + nonEmptyRows.length - 1;
  const newRange = sheet.getRange(startRow, 1, newEndRow - startRow + 1, colCount);
  protection.setRange(newRange);
}

这个函数可设置为每5分钟执行一次的时间驱动触发器，定期检查并处理空行。

触发器设置说明 #

1. 打开Google Sheets，点击顶部菜单「扩展程序」→「Apps脚本」
2. 将上述代码粘贴到脚本编辑器中，保存项目
3. onEdit函数会自动创建简单触发器（编辑表格时触发）
4. 为expandProtectedRange和shrinkProtectedRangeAndSort手动创建时间驱动触发器：
   • 点击脚本编辑器左侧「触发器」图标
   • 点击「添加触发器」，选择对应函数，设置触发类型为「时间驱动」，按需调整执行频率

注意：若表格存在多个保护范围，需修改代码中获取保护范围的逻辑（比如通过名称查找）。

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