使用Sheets API删除多行时endIndex指向不存在行的处理方法

一、API原生行为说明 #

当deleteDimension请求中的endIndex超出表格实际存在的行数时，Sheets API会自动将endIndex截断为表格当前的最大行索引，不会抛出错误。只要startIndex是有效的（即存在该行），API会正常删除startIndex到表格最后一行的内容；如果startIndex也超出表格范围，该请求会被直接忽略。

比如你场景中的startIndex:808、endIndex:809，若表格最大行数是808，API会自动把endIndex修正为808，执行后会删除第808行（对应索引808的行）。

二、优化处理方案 #

1. 提前修正endIndex参数 #

在构建请求时，先获取表格的实际最大行数（包含空行），对超出范围的endIndex进行修正，让请求参数更规范：

const sheetId = fabricDbSht.getSheetId();
let requests = [];
const fabricDbValues = fabricDbSht.getRange(1, 1, fabricDbSht.getLastRow(), fabricDbSht.getLastColumn()).getValues();
const maxRows = fabricDbSht.getMaxRows(); // 获取表格实际最大行数

for (let a = fabricDbValues.length - 1; a >= 0; a--) {
  if (fabricDbValues[a][0] == systemId) {
    let row = a + 1;
    if (row > 0) {
      let endIndex = row + 1;
      // 修正endIndex不超过表格最大行数
      if (endIndex > maxRows) {
        endIndex = maxRows;
      }
      requests.push({
        deleteDimension: {
          range: {
            sheetId,
            startIndex: row,
            endIndex,
            dimension: "ROWS"
          }
        }
      });
    }
  }
}
console.log('Requests: ' + JSON.stringify(requests))
Sheets.Spreadsheets.batchUpdate({requests}, fileId);

2. 合并连续行删除请求（推荐） #

当前每行一个请求的方式效率较低，且容易出现零散的endIndex问题。可以将连续的待删除行合并为一个请求，既减少请求数量，又能从根源避免单个请求的endIndex超出问题：

const sheetId = fabricDbSht.getSheetId();
const fabricDbValues = fabricDbSht.getRange(1, 1, fabricDbSht.getLastRow(), fabricDbSht.getLastColumn()).getValues();
const rowsToDelete = [];

// 收集所有待删除的行索引
for (let a = fabricDbValues.length - 1; a >= 0; a--) {
  if (fabricDbValues[a][0] == systemId) {
    rowsToDelete.push(a + 1);
  }
}

// 若无待删除行，直接终止
if (rowsToDelete.length === 0) {
  console.log("No rows to delete");
  return;
}

// 排序行索引，方便合并连续区间
rowsToDelete.sort((x, y) => x - y);

const requests = [];
let currentStart = rowsToDelete[0];
let currentEnd = currentStart + 1;

// 合并连续行
for (let i = 1; i < rowsToDelete.length; i++) {
  const row = rowsToDelete[i];
  if (row === currentEnd) {
    currentEnd = row + 1;
  } else {
    requests.push({
      deleteDimension: {
        range: {
          sheetId,
          startIndex: currentStart,
          endIndex: currentEnd,
          dimension: "ROWS"
        }
      }
    });
    currentStart = row;
    currentEnd = row + 1;
  }
}

// 添加最后一个合并后的区间
requests.push({
  deleteDimension: {
    range: {
      sheetId,
      startIndex: currentStart,
      endIndex: currentEnd,
      dimension: "ROWS"
    }
  }
});

// 最后修正所有请求的endIndex，确保不超出表格范围
const maxRows = fabricDbSht.getMaxRows();
requests.forEach(req => {
  if (req.deleteDimension.range.endIndex > maxRows) {
    req.deleteDimension.range.endIndex = maxRows;
  }
});

console.log('Requests: ' + JSON.stringify(requests))
Sheets.Spreadsheets.batchUpdate({requests}, fileId);

这种方式将你的3个请求合并为1个（startIndex:806, endIndex:809），大幅提升执行效率，同时避免了零散请求的endIndex问题。

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