GAS - Find empty cells rather slow

Question

I have the following code in Google Apps Script. It loops through more than 800 rows to find empty cells of a column. But it takes too long. Is there a way to speed it up?

    for(var t = 2; t <= lastrow; t++)
   {

   if (Geojson_Data.getRange(t,col1).getValue() == "")
   {
    Geojson_Data.deleteRow(t);

   }
   }

Try to optimize your code so that you don't invoke methods like `getRange(...)` in a loop. Fetch all your data in one go (via `getValues()`), process the data as arrays, and then update your sheet in one go (via `setValues()`). With your current implementation you're reading and updating your sheet on every iteration of your loop which is extremely inefficient. [Read the guides for more on best practices](https://developers.google.com/apps-script/guides/support/best-practices) — TheAddonDepot, Oct 02 '19 at 17:53
Possible duplicate of [Google app script timeout ~ 5 minutes?](https://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes) — TheMaster, Oct 02 '19 at 20:17

score 1 · Answer 1 · answered Oct 02 '19 at 17:57

1

function delRowsWithNothingInColA() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getRange(2,1,sh.getLastRow()-1,1);
  var vA=rg.getValues();
  var d=0;
  for(var i=0;i<vA.length;i++) {
    if(vA[i][0]=='') {
      sh.deleteRow(i+2-d++)
    }
  }
}

answered Oct 02 '19 at 17:57

Cooper

59,616
6
23
54

Just to add on to the response, this code assumes you're going over column `A`, to specify it's just a matter of changing `getRange(2,1,sh.getLastRow()-1,1)` to `getRange(2,col1,sh.getLastRow()-1,1);`. Mind you, this is only to change which column you'd check, the code provided in the answer is completely correct. – AMolina Oct 03 '19 at 06:41

contributorpw · Answer 2 · 2019-10-02T20:07:31.050

var values = Geojson_Data.getDataRange().getValues();
var col1Arr = col1 - 1;

var t = values.length;
while(t--) 
  if(values[t][col1Arr] === '')
    Geojson_Data.deleteRow(t + 1);

If you need a really tool then look at the snippet The script removes rows in batches without changing rows queue and without destroying formulas.

/**
 * Runs the snippet.
 * Removes rows by condition 'A:A=""'.
 * @ignore
 */
function run() {
  var sheet = SpreadsheetApp.getActiveSheet();
  deleteRowsByConditional_(sheet, function(row) {
    return row[0] === '';
  });
}

function deleteRowsByConditional_(sheet, condition, action) {
  var values = sheet.getDataRange().getValues();
  values.unshift([]);
  values.reverse().forEach(
    function() {
      var i = this.l - arguments[1];
      if (this.condition.apply(null, [arguments[0], i, arguments[2]])) {
        this.isContinue++;
      } else if (this.isContinue) {
        if (action) action(arguments[2], i, this.isContinue);
        this.sheet.deleteRows(i, this.isContinue);
        this.isContinue = 0;
      }
    },
    { sheet: sheet, condition: condition, isContinue: 0, l: values.length }
  );
}

GAS - Find empty cells rather slow

2 Answers2