Google Sheets deleteRow not working in my Macro

user_19118228 0
Viewed 38

I'm trying to build a Macro to erase all the rows that have empty values on column D. Originally, I was using this code that I found:

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Datos Competidor 2 - EV');
  var r = s.getRange('D:D');
  var v = r.getValues();
  for(var i=v.length-1;i>=0;i--)
    if(v[0,i]=='')
      s.deleteRow(i+1);
};

However the excessive number of calls to the API made this really slow and some times even fail due to a timeout.

I decided to just add all the rows that met the condition to a list and then just pass that to the deleteRow() in order to only call the API once using this code:

function deleteBlankRows() {
  emptyRange=[]
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Datos Competidor 2 - EV');
  var r = s.getRange('D:D');
  var v = r.getValues();
  for(var i=v.length-1;i>=0;i--)
    if(v[0,i]=='')
      emptyRange.push((i)+":"+(i));
  
  ss.getRangeList(emptyRange).activate();
  ss.getActiveSheet().deleteRows(ss.getActiveRange().getRow(), ss.getActiveRange().getNumRows());


      
};

The execution seems to work just fine, completing in 1 to 2 seconds, however, rows aren't erased as much as selected by the end of the execution.

This is what I see:

Final result

Any ideas why this is happening?

Thanks!

google-apps-script google-sheets-api google-sheets
2 Answers

I believe your goal is as follows.

  • You want to reduce the process cost of your script.

In this case, how about using Sheets API? When Sheets API is used, I thought that the process cost for deleting the rows can be reduced a little. When the Sheets API is reflected in your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Datos Competidor 2 - EV');
  var values = s.getRange('D1:D' + s.getLastRow()).getDisplayValues();
  var sheetId = s.getSheetId();
  var requests = values.reduce((ar, [d], i) => {
    if (!d) ar.push({ deleteDimension: { range: { sheetId, startIndex: i, endIndex: i + 1, dimension: "ROWS" } } });
    return ar;
  }, []).reverse();
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

References:

user_71086530

Delete Rows with empties on column D

function deleteBlankRows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = ss.getSheetByName('Datos Competidor 2 - EV');
  const r = s.getRange('D1:D' + s.getLastRow());
  const v = r.getValues().flat();
  let d = 0;
  v.forEach((e, i) => {
    if (!e) {
      s.deleteRow(i + 1 - d++)
    }
  })
}
user_72150910
Related