0

This code works but not as intended. The workbook has several tabs (Sheets) and if the user is editing Tab1, then and only then a trigger OnEdit should execute. If the user is editing some other tab (ie. Tab2 or anything other than Tab1) then code should exit.

The purpose is to look up the last value in Column G (date value as mm/dd/yy) and Column I (number) and store them into cell F2

How may I test if user is editing sheet named Tab1 and stop code if on some other tab ?

function GetLastValueInColumnG() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get the active Spreadsheet.
  var sheet=ss.getSheetByName("Tab1");          //Code should run only on Tab1 sheet.
  sheet.activate() ;                           //hardcoded but dont want this.
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
 
  var lastRow = 2;
  var lastColumn = 1;
  var Gvals = sheet.getRange("G1:G").getValues();
  var Glast = Gvals.filter(String).length;
  var lastCell = sheet.getRange(Glast +1, lastColumn);
   
  var row = Glast;
  var col = 9;
  var data = sheet.getRange(row, col).getValue();
  var dtdata = sheet.getRange(row, col-2).getValue();
  var formatteddtdata = Utilities.formatDate(new Date(dtdata), "GMT+1", "MM/dd/YY");
  
  var row = 1;
  var col = 6;
  // stored in Column F Row 1
  sheet.getRange(row, col).setValue('Stored (last: ' + formatteddtdata + ' $ ' + data + ')');
}

1 Answer 1

1

Use an if statement. The following example uses on edit event object, the range property from this object and the getSheet() method of Class Range and hte getName() method of Class Sheet:

function respondToOnEdite(e){
  const sheet = e.range.getSheet();
  if(sheet.getName() !== 'Tab1') returns;
  // add here the rest of the code
}

The easier way to test this is by editing different sheets. To make even easier to see if this working as expected you might use console.log at several points of your code:

function respondToOnEdite(e){
  const sheet = e.range.getSheet();
  const name = sheet.getName();
  console.log(name)
  if( name !== 'Tab1') {
     console.log('Finished because edited sheet name is not Tab1');
     returns;
  } 
  console.log('Continue because edited sheet name is Tab1');
  // add here the rest of the code
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.