0

I'm doing my first App Scripts and I'm able to have them working... but I remain dependant on some Sheets formulas to identify data. This impacts the speed of my script dramatically.

As a basic example, lets pretend I want to delete every lines that have a "-" in a certain column. I don't know how to identify "-" in a column in App Script so I did this formula in my Helper spreadsheet:

=IFERROR(MATCH("-",Database!$U:$U,0),0)

Then I retrieve this information in my code and recalculate the workbook after each deletion to have the new Match value:

function deleteNoValue(){
  var ss = SpreadsheetApp.getActive();                 // Thisworkbook
  var ssHlp = ss.getSheetByName('Helper');             // Sheet with match formula
  var ssDb = ss.getSheetByName('Database');            // Sheet to delete lines from
  var firstNoVal = ssHlp.getRange('A1').getValue();    // Match formula value

  while (firstNoVal != 0) {                            // Repeat until all lines deleted
    ssDb.deleteRows(firstNoVal ,1);                    // Delete line
    SpreadsheetApp.flush();                            // Wait for Match to refresh value
    firstNoVal = ssHlp.getRange('A1').getValue();      // Get new value
  }
}

How can I have this improved without needing the help of Sheets formulas to be refreshed?

1
  • I don't know how to clarify better than what I explained. The MATCH() formula above is in the Helper sheet and provides the line number to delete in Database sheet
    – Exdefoot
    Dec 20, 2021 at 13:27

1 Answer 1

0

Use batch operations and Array.filter(), like this:

function deleteRowsWhereColumnUEqualsDash() {
  const ss = SpreadsheetApp.getActive();
  const dataRange = ss.getSheetByName('Database').getDataRange();
  const filteredData = dataRange.getValues()
    .filter(row => row[20] !== '-');
  dataRange
    .clearContent()
    .offset(0, 0, filteredData.length, filteredData[0].length)
    .setValues(filteredData);
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.