1

My code deletes a row if a string exists in a column:

function deleteRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();

var rowsDeleted = 0;
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
if (row[0].includes('bedroom')) { 
sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
rowsDeleted++;
}
}
};

How can I make it look inside cell formulas as well ?

Thank you for your help.

Karim
  • 205
  • 1
  • 9
  • Instead of `getValues()` use `getFormulas()`. It will return a 2D array of formulas as string that you can search just like value strings. However you should start from the bottom of the array. If you do it your way, once you delete a row the following rows are renumbered. – TheWizEd Apr 12 '23 at 12:57
  • @TheWizEd that was it! Thank you so much! Is there a way to run it faster ? At the moment it's deleting one row after the other, is it possible to take all those rows at once and delete them at the same time ? – Karim Apr 12 '23 at 13:11
  • Only if they are consecutive rows. See [`.deleteRows()`](https://developers.google.com/apps-script/reference/spreadsheet/sheet#deleteRows(Integer,Integer)) – TheWizEd Apr 12 '23 at 13:13

3 Answers3

1

I will answer my own question because what seemed to be a rather simple task took a good time of my day today.

function deleteRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var textToFind = "specific text";
  var rowsToDelete = [];

  var lastRow = sheet.getMaxRows();
  var formulas = sheet.getRange("G1:G" + lastRow).getFormulas();
  for (var i = 0; i < formulas.length; i++) {
    var formula = formulas[i][0];
    var pattern = new RegExp('\\b' + textToFind + '\\b', 'i');
    if (formula && pattern.test(formula)) {
      rowsToDelete.push(i + 1);
    }
  }

  if (rowsToDelete.length > 0) {
    rowsToDelete.reverse().forEach(function(rowIndex) {
      sheet.deleteRow(rowIndex);
    });
  }
}

In this version, I used the getFormulas() instead of getValues() and it worked. BUT, keep in mind that when you delete rows, you are changing the numbering of all the other rows.

So if you have a bunch of rows you want to delete inside a sheet, consecutive or not, then this code will help you achieve that.

Further explanation:

I've created an empty array called rowsToDelete before the loop, and then added the row numbers of any matching rows to that array using rowsToDelete.push(i + 1).

After the loop completes, I'm checking if the rowsToDelete array has any elements using rowsToDelete.length > 0. If it does, we're reversing the order of the row numbers in the array using rowsToDelete.reverse(), and then using the forEach() method to iterate over the row numbers and delete each row in turn using sheet.deleteRow(rowIndex).

I've used the getMaxRows() method to get the maximum number of rows in the sheet, which should include all the rows in the sheet, even if they are currently empty.

Karim
  • 205
  • 1
  • 9
0

I'm writing this answer as a community wiki, since the issue was resolved from the comments section, in order to provide a proper response to the question.

Since you want to search text within a formula in your Google Sheet, instead of using getValues(), you should use getFormulas().

getFormulas() Returns the formulas (A1 notation) for the cells in the range. Entries in the 2D array are empty strings for cells with no formula.

And regarding making the script more efficient, the reason it runs slow now is because you're making a separate call to delete one row after another, to delete rows more efficiently, you can try with API. Maybe you can refer to this similar thread which provides a workaround using the Sheets API.

Lorena Gomez
  • 1,946
  • 2
  • 4
  • 11
0
function delRows(txt=7) {
  let ss = SpreadsheetApp.getActive();
  let sh = ss.getActiveSheet();
  let f = sh.createTextFinder(txt).findAll();
  let d = 0;
  if(f) {
   [...new Set(f.map(rg => rg.getRow()))].forEach(r => sh.deleteRow(r - d++));
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Mark Rotteveel Apr 13 '23 at 10:18
  • @MarkRotteveel I just scanned the how to answer to answer documentation and it said nothing about code only answers so I'm wondering is that just your personal view or is it a website policy? Others have made the same comment in the past and I just ignored them. My personal feeling is that code only answers are most desirable because I feel that it's my responsibility to figure how they work on my own. Tanaike answers questions with a lot of additional documentation but he hardly ever get's into the nuts and bolts of the code and some of his code is quite complex. – Cooper Apr 13 '23 at 15:58
  • Code-only answers are indeed answers, and I'm leaving you a suggestion how to **improve** your answer by providing an explanation and context as it helps with the goal of Stack Overflow itself. I also didn't say posting code-only is not allowed, though opinions are that they should be considered low-quality (e.g. [Is there any benefit to allowing code-only answers while blocking code-only questions?](https://meta.stackexchange.com/questions/148272/is-there-any-benefit-to-allowing-code-only-answers-while-blocking-code-only-ques)). – Mark Rotteveel Apr 14 '23 at 06:45
  • See also [What comment should I add to code-only answers?](https://meta.stackoverflow.com/questions/300837/what-comment-should-i-add-to-code-only-answers) – Mark Rotteveel Apr 14 '23 at 06:45
  • See also [Explaining entirely code-based answers](https://meta.stackoverflow.com/questions/392712/explaining-entirely-code-based-answers) – Mark Rotteveel Apr 14 '23 at 06:51
  • Seems like more than a suggestion but I will accept it as a suggestion – Cooper Apr 14 '23 at 15:15
  • Maybe I need to work a bit on the phrasing of my comment. I'll see if can change that when I use it again. – Mark Rotteveel Apr 14 '23 at 18:07