我遇到了一些配额问题 [英] I'm running into some quota issue

问题描述

我有以下代码来清除每个工作表中的所有过滤器:

I have the following code to clear all filters in every sheet:

function clearAllFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheetIds = ss.getSheets();
  for (var i in sheetIds) {
    var requests = [{
      "clearBasicFilter": {
        "sheetId": sheetIds[i].getSheetId()
      }
    }];
    Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId); 
  }
}

代码运行良好，但是出现以下错误:

The code is working well, but I'm getting the following error:

如何摆脱此错误消息或更好的解决方法，优化代码以尽快完成其工作?...

How do I get rid of this error message or better yet, optimize the code to complete its job as quickly as possible?...

要添加更多信息，我的电子表格有119张纸.

to add more information, my spreadsheet has a 119 sheets.

推荐答案

@tehhowch注释，他给了我一个线索，我找到了代码的修复程序.

@tehhowch comment was all I needed, he gave me a clue and I found a fix to the code.

错误在于for循环中:

The error lies in the for loop:

for (var i in sheetIds) {
    var requests = [{
      "clearBasicFilter": {
        "sheetId": sheetIds[i].getSheetId()
      }
    }];
    Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId); 
  }

在这里，我遍历电子表格中的每个工作表，获取工作表ID，然后调用.batchUpdate().这里的问题是，我为每个工作表调用了工作表API，这意味着我为所有119个工作表调用了119次.

Here I am looping through every sheet in the spreadsheet, obtaining the sheet ID and then calling .batchUpdate(). The problem here, is that I'm calling the sheets API for every sheet, meaning I'm calling it 119 times for all my 119 sheets.

上面的代码效率低下，超出了我的配额限制.

The above code is inefficient and beyond my quota limit.

FIX:

1. 将所有工作表ID放入数组中.
2. 将.batchUpdate()移至for循环之外
3. 然后执行.batchUpdate({'requests': array}而不是.batchUpdate({'requests': requests}

1. Place all the sheets IDs into an array.
2. Move the .batchUpdate() outside of the for loop
3. Then do .batchUpdate({'requests': array} instead of .batchUpdate({'requests': requests}

所以现在代码是高效的，而不是119次调用sheets API，现在只调用一次，解决了配额问题并成功运行了脚本，而没有任何错误消息.

So now the code is efficient, instead of calling the sheets API 119 times, now I'm only calling it once, fixing my quota issue and successfully run the script without any error messages.

完整代码:

function clearAllFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var sheetIds = ss.getSheets();
  var idArray = [];

  //loop through all the sheets, get the sheet ID, then push into the array
  for (var i in sheetIds) {
    var requests = [{
      "clearBasicFilter": {
        "sheetId": sheetIds[i].getSheetId()
      }
    }];
        idArray.push(requests); //now the array stores all the sheet IDs
        //Logger.log(idArray);
  }
      Sheets.Spreadsheets.batchUpdate({'requests': idArray}, ssId); //do .batchUpdate only once by passing in the array
}

这篇关于我遇到了一些配额问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！