Limit Script to one sheet

Question

This script does exactly what I'm looking for with my data, however I'd like to limit it to run only on one sheet. I've tried using if (s.getName() == 'Sheet1') immediately after var sheet = ss.getSheets()[0] but then the script no longer functions properly.

What am I missing?

function onEdit(e){
    var ui = SpreadsheetApp.getUi(); 
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
  var range = e.range;
  var columnOfCellEdited = range.getColumn();

  if (columnOfCellEdited === 1) 
    var range2 = range.getRow();
    var destrange = range2;

    // adds the formulas
    var cell = sheet.getRange("C" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("details",$B' + destrange +')+7,SEARCH(",",$B' + destrange +')-SEARCH("details",$B' + destrange +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("D" + destrange);
    cell.setFormula('=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B' + destrange +',FIND("$",B' + destrange +'),LEN(B' + destrange +'))," ",REPT(" ",100)),100)),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("E" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("exceed",$B' + destrange +')+7,SEARCH("%",$B' + destrange +')-SEARCH("exceed",$B' + destrange +')-6),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("F" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("due",$B' + destrange +')+3,SEARCH(";",$B' + destrange +')-SEARCH("due",$B' + destrange +')-3),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("G" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("held on",$B' + destrange +')+7,SEARCH(". Lottery",$B' + destrange +')-SEARCH("held on",$B' + destrange +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    var cell = sheet.getRange("H" + destrange);
    cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("posted by",$B' + destrange +')+9,SEARCH(". ",$B' + destrange +')-SEARCH("",$B' + destrange +')-167),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');

}

Jack Brown Jack Brown · Accepted Answer · 2017-04-11T00:02:55

You can do this to make sure the function acts only on "sheet1"

        var range = e.range;
        var sheet = range.getSheet()
        var sheetName = sheet.getName()
        if(sheetName != "Sheet1"){
           return                            //exit function
        }

Use event object to get the range>Sheet>SheetName. Compare the sheet name to sheet1 if different exit function

Full code:

function onEdit(e){
    var ui = SpreadsheetApp.getUi(); 
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var range = e.range;
    var sheet = range.getSheet()
    var sheetName = sheet.getName()
    if(sheetName != "Sheet1"){
       return                            //exit function
    }  
    var columnOfCellEdited = range.getColumn();
    if (columnOfCellEdited === 1) 
     var range2 = range.getRow();
     var destrange = range2;
     // adds the formulas
     var cell = sheet.getRange("C" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("details",$B' + destrange +')+7,SEARCH(",",$B' + destrange +')-SEARCH("details",$B' + destrange +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("D" + destrange);
     cell.setFormula('=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(B' + destrange +',FIND("$",B' + destrange +'),LEN(B' + destrange +'))," ",REPT(" ",100)),100)),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("E" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("exceed",$B' + destrange +')+7,SEARCH("%",$B' + destrange +')-SEARCH("exceed",$B' + destrange +')-6),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("F" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("due",$B' + destrange +')+3,SEARCH(";",$B' + destrange +')-SEARCH("due",$B' + destrange +')-3),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("G" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("held on",$B' + destrange +')+7,SEARCH(". Lottery",$B' + destrange +')-SEARCH("held on",$B' + destrange +')-7),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
     var cell = sheet.getRange("H" + destrange);
     cell.setFormula('=IFERROR(MID($B' + destrange +',SEARCH("posted by",$B' + destrange +')+9,SEARCH(". ",$B' + destrange +')-SEARCH("",$B' + destrange +')-167),HYPERLINK("https://housing.sfgov.org/listings","See Housing Portal"))');
    }

}

Hope that helps

Limit Script to one sheet

1 Answers