0

I am trying to delete specific rows containing specific string "X". 

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('delete containing');
  var r = s.getRange('A:A');
  var v = r.getValues();
  for(var i=v.length-1;i>=0;i--)
    if(v[0,i]=='Substitution: ')
      s.deleteRow(i+1);
};

But I am getting below error:

TypeError: Cannot call method "getRange of null.(line 4, file "Code").

Can Anybody help me resolving this error? Thank you

enter image description here enter image description here

EmmyAyes
  • 138
  • 1
  • 12

1 Answers1

0

From the documentation: getSheetByName() "Returns null if there is no sheet with the given name." So you need to handle that, see the inserted if block: 

function deleteRows() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName('delete containing');
    if (s === null) {
      ui = SpreadsheetApp.getUi();
      ui.alert("No worksheet of that name"); // Note: displays in the worksheet GUI, not the scripts editor.
      return false;
    }
    var r = s.getRange('A:A');
    var v = r.getValues();
    for(var i=v.length-1;i>=0;i--)
      if(v[0,i]=='Substitution: ') // [sic] jshint kicking up a storm about this line!
        s.deleteRow(i+1);
  }
P Burke
  • 1,630
  • 2
  • 17
  • 31
  • 1
    but why is this not working if the sheet with the given name exists (as I have already created google spreadsheet) and how to resolve this? – EmmyAyes Jan 19 '19 at 13:40
  • Looking at the images you provided: your **spreadsheet** name is 'delete containing', but what we can't see are the individual **worksheets** within the spreadsheet. Could you be confused about the difference between the spreadsheet and the worksheets it contains? For your code to work, there needs to be a worksheet called 'delete containing'. – P Burke Jan 19 '19 at 13:46
  • I'm afraid that "not working" doesn't really help anyone help you. I suggest your read [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) and try again with a new, specific, question. – P Burke Jan 19 '19 at 14:03