Changing script to run on a particular sheet raises TypeError

Question

How do I run this script on a particular sheet instead of the entire spreadsheet?

function deleteQCOrder() {
  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[4] == 'Delete' || row[4] == '') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

For instance, if I want to run it on "Sheet1", I tried:

function deleteQCOrder() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var ss = sheet.getSheetByName('Sheet1');

  var rows = ss.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[4] == 'Delete' || row[4] == '') { // This searches all cells in columns A (change to row[1] for columns B and so on) and deletes row if cell is empty or has value 'delete'.
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};

but instead of running, I get this error:

TypeError: Cannot find function getSheetByName in object Sheet. (line 3, file "Delete-Order-No.")

Answer 1

You must use the spreadsheet object to get the sheet object. In your second block of code, you are getting the sheet rather than spreadsheet.

You can get the spreadsheet object by using:

var ss = SpreadsheetApp.getActiveSpreadsheet();

Then you can use getSheetByName to get 'Sheet1':

var sheet = ss.getSheetByName('Sheet1');

Answer 2

What you do in your code is get a sheet object which is currently the active sheet

var sheet = SpreadsheetApp.getActiveSheet();

then you try to get a sheet object out of a sheet object and it's where is the mistake

var ss = sheet.getSheetByName('Sheet1');

What you need to do is find a sheet Object out of a spreadsheet object as follow:

function deleteQCOrder() {
  //correct use of SpreadsheetApp for your case
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var range = sheet.getDataRange();
  var numRows = range.getNumRows();
  var values = range.getValues();
  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[4] == 'Delete' || row[4] == '') {
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
};