Filter Data and Remove Rows Fast while keeping Header

user_19495375 0
Viewed 45

This is my final task. I am looking to filter data from column 17, that if anything in that column is less than 1, to remove the data in those rows. There is data that gets imported daily and their are a lot of rows that I want to filter fast and then delete those rows specified. I just want to keep the value of 1 in column 17. There is a frozen header at the top that does not move. What I have works but is extremely slow. Thank you for any help!

function filterData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('CancelRawData'); 

  var colCdata = sh1.getRange(1,17,sh1.getLastRow(),1).getValues()
   var filtered = colCdata.filter(String);
    Logger.log(filtered);

  for(let i = colCdata.length-1;i >= 0;i--){

     if(colCdata[i] <1 === true){

     sh1.deleteRow(i+1)
     }
  }
 SpreadsheetApp.getUi().alert(" Congratulations, your data has been filtered!", SpreadsheetApp.getUi().ButtonSet.OK);
}
google-apps-script google-sheets performance
1 Answers

In your situation, in order to reduce the process cost of your script, how about the following modifications?

Modified script 1:

In this modification, Sheets API is used. Before you use this script, please enable Sheets API at Advanced Google services.

function filterData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('CancelRawData');
  var colCdata = sh1.getRange(1, 17, sh1.getLastRow(), 1).getValues();
  var sheetId = sh1.getSheetId();
  var requests = colCdata.reduce((ar, [q], i) => {
    if (!isNaN(q) && q < 1) ar.push({ deleteDimension: { range: { sheetId, startIndex: i, endIndex: i + 1, dimension: "ROWS" } } });
    return ar;
  }, []).reverse();
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
  SpreadsheetApp.getUi().alert(" Congratulations, your data has been filtered!", SpreadsheetApp.getUi().ButtonSet.OK);
}

Modified script 2:

In this modification, retrieved all values are filtered, and the filtered values are put on the sheet.

function filterData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('CancelRawData');
  var range = sh1.getDataRange();
  var values = range.getValues().filter(r => isNaN(r[16]) || r[16] >= 1);
  range.clearContent();
  sh1.getRange(1, 1, values.length, values[0].length).setValues(values);
  SpreadsheetApp.getUi().alert(" Congratulations, your data has been filtered!", SpreadsheetApp.getUi().ButtonSet.OK);
}

References:

user_71086530
Related