Need to Trigger email when cell says "yes"

[Heidi Gibson]

Hi, I've been fighting with this for 3 solid days now to no avail. I have a workbook that has about 30 sheets in it. Each sheet is the same, but has a different form linked to it. I want the program to automatically email someone when a cell in column J populates with "Yes", but I want the message to identify which sheet has the comment. I tried making a separate script file for each sheet, but it only runs the last one, so every time any page populates with Yes, it sends an email directing to the last sheet. I have tried creating multiple projects, I've tried modifying some of the suggestions on this page, as well as other pages, and I can't seem to get it to work. I have never dealt with creating scripts, so I'm well out of my zone with this. Can anyone suggest what I can edit to make this work?

Thank you!!

This is my current effort to create one script that checks multiple sheets. I'm fighting with just these 3 right now, anticipating I will add the rest of the sheets once I get this to work.

function checkValue() {

{

var ss = SpreadsheetApp.getActiveSpreadsheet();

   

  var sheetname01 = "GMC 3Ton";

  var sheetname02 = "Hill Evac Van";

  var sheetname03 = "1989 Water Truck";

var valueToCheck = sheet.getRange("J:J").getValue();

if(valueToCheck="Yes")

{

  if (sheet.getSheetName() === sheetname01){

  MailApp.sendEmail("my_e...@gmail.com");

  var message1 = 'Check GMC 3Ton Sheet for details.';

  var subject1 = 'GMC 3Ton Flag';

} else if (sheet.getSheetName() === sheetname02){

  MailApp.sendEmail(" my_e...@gmail.com");

  var message1 = 'Check Hill Evac Van sheet for details.';

  var subject1 = 'Hill Evac Van Flag';

} else if (sheet.getSheetName() === sheetname03){

  MailApp.sendEmail("my_e...@gmail.com");

  var message1 = 'Check 1989 Water Truck sheet for details.';

  var subject1 = '1989 Water Truck Flag';

}

}/** @OnlyCurrentDoc */

[Arthur M. Futoryan]

You need to loop through your sheets.  Pull the values in each sheet, check them against your criteria, and if it meets it, then shoot off other functions. 

Use the "for loop". 

Ex: 

for (i = 0; i < cars.length; i++) {
  text += cars[i] + "<br>";
}

If you need more help let me know: arthurm...@gmail.com