Google Sheets行过滤脚本误删数据时如何断言指定列长度

以下代码可基于指定布尔表达式过滤Google电子表格数据，代码贡献者：newaza

function DeleteEmptyRows() {

  const sheet = SpreadsheetApp.getActiveSheet()
  const values = sheet.getDataRange()
                      .getValues()
                      .filter(row => row[9] !== '' && row[7] !== '' && row[6] !== '')

  sheet.getDataRange().clearContent()

  return sheet.getRange(1, 1, values.length, values[0].length)
              .setValues(values)                      

}

当前运行上述脚本存在异常：部分不满足布尔过滤条件的记录被错误删除，可通过过滤前后的断言校验row[6]、row[7]、row[9]三列的非空/内容长度，排查并避免误删问题。

实现逻辑 #

• 读取原始全量数据后，先统计目标列的非空行数、符合过滤条件的预期保留行数，作为校验基准
• 执行过滤逻辑后，统计过滤结果集中目标列的对应数值
• 增加断言判断，若校验不通过直接终止流程，不执行清空原表、写入新数据的操作，避免数据误删
• 增加执行日志，输出过滤前后的统计值，方便定位异常原因

带校验功能的改造后代码 #

function DeleteEmptyRows() {
  const sheet = SpreadsheetApp.getActiveSheet()
  const fullDataRange = sheet.getDataRange()
  const originalData = fullDataRange.getValues()

  // 过滤前统计
  // 统计符合三列非空要求的预期保留行数
  const expectedRetainCount = originalData.filter(row => row[6] !== '' && row[7] !== '' && row[9] !== '').length
  // 分别统计三列的非空行数
  const originCol6Count = originalData.filter(row => row[6] !== '').length
  const originCol7Count = originalData.filter(row => row[7] !== '').length
  const originCol9Count = originalData.filter(row => row[9] !== '').length
  console.log(`过滤前统计：预期保留符合条件行${expectedRetainCount}行，G列(索引6)非空${originCol6Count}行，H列(索引7)非空${originCol7Count}行，J列(索引9)非空${originCol9Count}行`)

  // 执行过滤
  const filteredData = originalData.filter(row => row[9] !== '' && row[7] !== '' && row[6] !== '')

  // 过滤后统计
  const filteredCol6Count = filteredData.filter(row => row[6] !== '').length
  const filteredCol7Count = filteredData.filter(row => row[7] !== '').length
  const filteredCol9Count = filteredData.filter(row => row[9] !== '').length
  console.log(`过滤后统计：实际保留行${filteredData.length}行，G列(索引6)非空${filteredCol6Count}行，H列(索引7)非空${filteredCol7Count}行，J列(索引9)非空${filteredCol9Count}行`)

  // 断言校验
  if (filteredData.length !== expectedRetainCount) {
    throw new Error(`校验失败：预期保留${expectedRetainCount}行，实际过滤结果为${filteredData.length}行，已终止操作，原表数据未改动`)
  }
  if (filteredCol6Count !== filteredData.length || filteredCol7Count !== filteredData.length || filteredCol9Count !== filteredData.length) {
    throw new Error(`校验失败：过滤结果中存在目标列空值，已终止操作，原表数据未改动`)
  }
  // 若需校验单元格内容长度，可替换上述判断逻辑，例如判断G列内容长度固定为3：row[6].length === 3

  // 校验通过后再执行写入
  fullDataRange.clearContent()
  return sheet.getRange(1, 1, filteredData.length, filteredData[0].length)
              .setValues(filteredData)                      
}

使用说明 #

• 所有校验逻辑在清空原表操作前执行，校验不通过会直接抛出错误，不会修改原表数据
• 脚本运行后可在Apps Script的执行日志面板查看过滤前后的列统计数据，快速定位过滤逻辑异常
• 如果需要校验单元格内容的字符长度，只需要把对应判断条件替换为长度判断即可，例如校验row[9]长度为5时，判断条件写为row[9].length === 5

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