Deleting entire row where a string is found

Question

I am trying to write a google script which will delete a row, if that row contains a cell with a string.

Here is the code I am using right now:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Test'); 
  var values = s.getDataRange().getValues();
    for(var i=values.length;i>0;i-=1){
     var lcVal=values[i-1][0].toLowerCase() 
     var index = lcVal.indexOf("deleteme"); 
     if (lcVal.indexOf("deleteme") > -1){
     s.deleteRow(i)};
  }}

Ideally, this would go through and delete the rows which have a cell that contains "deleteme". I'd also like to specify a range of cells for which this would check (i.e. the entire A column starting from row 9), but I haven't figured that part out yet.

Right now, nothing happens when I attempt to run this, and cells aren't getting deleted.

Create and publish the script so that others can test it. What error messages do you get? Check the script Logger output. — Sujay Phadke, Jul 09 '16 at 04:20

score 0 · Answer 1 · edited May 23 '17 at 12:22

First, remember that GAS is based in JavaScript. So if you are working with JavaScript solely in terms of figuring out what is wrong, you might want to add that tag. You'd probably already have an answer to this question... or it would have already been marked as a duplicate question. If you do that though, be very sure to point out that you are asking about the JavaScript part or else just put the snippet in that is JS.

Second. Logger.log() is your friend. Everything is returning as -1 so nothing is happening. If you put in a couple lines like these and then view logs after running you can see what is going on. (I leave them justified left so I remember to pull them or comment them before I call the script finished.)

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Sheet11'); 
  var values = s.getDataRange().getValues();
Logger.log(values);
  for(var i=values.length;i>0;i-=1){

     var lcVal=values[i-1][0].toLowerCase() 
     var index = lcVal.indexOf("Science"); 
Logger.log("lcVal and index are: " + lcVal + " ... " + index + " and values.indexofscience is: " + values.indexOf("science"));
      if (lcVal.indexOf("Science") > -1){
      s.deleteRow(i)};
  }}

This results in this log:

[16-07-09 10:56:15:285 EDT] [[science], [math], [science], [english], [ELA], [History], [Elective], [Science]]
[16-07-09 10:56:15:286 EDT] lcVal and index are: science ... -1 and values.indexofscience is: -1
[16-07-09 10:56:15:286 EDT] lcVal and index are: elective ... -1 and values.indexofscience is: -1
[16-07-09 10:56:15:286 EDT] lcVal and index are: history ... -1 and values.indexofscience is: -1
[16-07-09 10:56:15:287 EDT] lcVal and index are: ela ... -1 and values.indexofscience is: -1
[16-07-09 10:56:15:287 EDT] lcVal and index are: english ... -1 and values.indexofscience is: -1
[16-07-09 10:56:15:288 EDT] lcVal and index are: science ... -1 and values.indexofscience is: -1
[16-07-09 10:56:15:288 EDT] lcVal and index are: math ... -1 and values.indexofscience is: -1
[16-07-09 10:56:15:289 EDT] lcVal and index are: science ... -1 and values.indexofscience is: -1

So the science and Science words in my test sheet aren't returning an index position. At that point you need to dig into indexOf() or look at doing things differently.

Third... or maybe it should have been 1st/0th... ask if you are doing something that is unique or if it is something that likely has been done before. Find a string and delete a row is a pretty common thing. There are numerous answers on SE and other sites. here is one that should get you up and running with a good discussion on approach. I won't rehash it.

Deleting entire row where a string is found

1 Answers1