AppScript追加仅17单元格行触发10000000单元格上限错误求助

• 运行AppScript时弹出错误提示：此操作会使工作簿中的单元格数量超过10000000的限制，但实际仅尝试追加一行含17个单元格的数据
• 代码原本运行正常，修改后出现该错误，即使恢复备份的原始代码，错误仍持续出现
• 脚本逻辑为从Firebase数据库导入文档，将每个字段存入数组后逐行追加到表格；数据库中文档数量远未达1000万，且错误在追加第一行时就触发

原代码 #

function myFunction() {
    const firestore=getFirestore();
    const spreadsheet=SpreadsheetApp.getActiveSpreadsheet();
    const sheet=spreadsheet.getActiveSheet();

    var range = sheet.getRange('A3:Z10000000');
    range.clear();

    const allDocuments=firestore.getDocuments('VormData');

    for(var i=0;i<allDocuments.length;i++){
     
      //Initialize the array to be printed to google sheets
      var docArray=[];

      var area=allDocuments[i].fields['Area'];
      if (roete != null){ // 注：此处存在变量名错误，应为area != null
        docArray.push(area.stringValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var roete=allDocuments[i].fields['Roete'];
      if (roete != null){
        docArray.push(roete.stringValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var staanplekNr=allDocuments[i].fields['StaanplekNr'];
      if (staanplekNr != null){
        docArray.push(staanplekNr.stringValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var datumTyd=allDocuments[i].fields['DatumTyd'];
      if (datumTyd != null){
        docArray.push(datumTyd.timestampValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var werknemerId=allDocuments[i].fields['WerknemerID'];
      if (werknemerId != null){
        docArray.push(werknemerId.stringValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var getalStands=allDocuments[i].fields['GetalStands'];
      if (getalStands != null){
        docArray.push(getalStands.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }
  
      var getalKorwe=allDocuments[i].fields['GetalKorwe'];
      if (getalKorwe != null){
        docArray.push(getalKorwe.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var geenSuperKorwe=allDocuments[i].fields['GeenSuperKorwe'];
      if (geenSuperKorwe != null){
        docArray.push(geenSuperKorwe.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var eenSuperKorwe=allDocuments[i].fields['EenSuperKorwe'];
      if (eenSuperKorwe != null){
        docArray.push(eenSuperKorwe.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var tweeSuperKorwe=allDocuments[i].fields['TweeSuperKorwe'];
      if (tweeSuperKorwe != null){
        docArray.push(tweeSuperKorwe.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var drieSuperKorwe=allDocuments[i].fields['DrieSuperKorwe'];
      if (drieSuperKorwe != null){
        docArray.push(drieSuperKorwe.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var vierSuperKorwe=allDocuments[i].fields['VierSuperKorwe'];
      if (vierSuperKorwe != null){
        docArray.push(vierSuperKorwe.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var supersTotaal=allDocuments[i].fields['SupersTotaal'];
      if (supersTotaal != null){
        docArray.push(supersTotaal.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var vangkaste=allDocuments[i].fields['Vangkaste'];
      if (vangkaste != null){
        docArray.push(vangkaste.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var supersGetrek=allDocuments[i].fields['SupersGetrek'];
      if (supersGetrek != null){
        docArray.push(supersGetrek.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var korweVrek=allDocuments[i].fields['KorweVrek'];
      if (korweVrek != null){
        docArray.push(korweVrek.integerValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      var opmerkings=allDocuments[i].fields['Opmerkings'];
      if (opmerkings != null){
        docArray.push(opmerkings.stringValue);
      }
      else {
        docArray.push("GEEN DATA");
      }
  
    //write array as row in google sheets
    sheet.appendRow(docArray); 
    }
}

问题分析与修复方案 #

1. 核心错误：超大范围的getRange调用 #

代码中sheet.getRange('A3:Z10000000')直接创建了一个包含26列 × 9999998行 = 259,999,948个单元格的范围，远超Google Sheets单工作表1000万单元格的限制。即使执行clear()，这个范围的单元格对象已经被初始化，直接触发了超限检测。

2. 修正清空逻辑 #

替换原清空代码为仅操作已使用的范围：

// 清空A3开始的所有已使用数据
const startRow = 3;
const lastRow = sheet.getLastRow();
const lastCol = sheet.getLastColumn();
if (lastRow >= startRow) {
  sheet.getRange(startRow, 1, lastRow - startRow + 1, lastCol).clear();
}

3. 优化数据写入效率 #

原循环中使用appendRow逐行写入，每次调用都会触发工作表刷新，效率低下且易引发异常。改为先收集所有数据，一次性写入：

function myFunction() {
    const firestore=getFirestore();
    const spreadsheet=SpreadsheetApp.getActiveSpreadsheet();
    const sheet=spreadsheet.getActiveSheet();

    // 修正清空逻辑
    const startRow = 3;
    const lastRow = sheet.getLastRow();
    const lastCol = sheet.getLastColumn();
    if (lastRow >= startRow) {
      sheet.getRange(startRow, 1, lastRow - startRow + 1, lastCol).clear();
    }

    const allDocuments=firestore.getDocuments('VormData');
    const allRows = []; // 收集所有行数据

    for(var i=0;i<allDocuments.length;i++){
      var docArray=[];

      var area=allDocuments[i].fields['Area'];
      if (area != null){ // 修正变量名错误
        docArray.push(area.stringValue);
      }
      else {
        docArray.push("GEEN DATA");
      }

      // ... 其余字段处理逻辑保持不变 ...

      allRows.push(docArray);
    }

    // 一次性写入所有数据
    if (allRows.length > 0) {
      sheet.getRange(startRow, 1, allRows.length, allRows[0].length).setValues(allRows);
    }
}

4. 工作表残留问题排查 #

若修改代码后仍报错，可能是工作表中存在残留的格式或空单元格导致计数超限：

• 手动选中数据最后一行之后的所有空行，右键删除
• 同理删除多余的空列
• 保存工作表后重新运行脚本

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