javascriptemailgoogle-apps-scriptgoogle-sheetsconditional-statements

Sends and email containing the populated data, all based on whether a cell has a "Yes" or "No"


I'm trying to write a script in Google Sheets, that pulls data from a Master sheet, and sends and email containing the populated data, all based on whether a cell has a "Yes" or "No"

I have this script tied to an onEdit() trigger (which I don't know if I'm calling correctly) and I have it actually sending out emails, but there's an error somewhere that ends up dumping my entire function into the body of the email.

Here's a copy of the sheet in question

And here's my code:

function sendEmails() {
  var trackOriginSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");
  var startRow = 2;  // First row of data to process
  var numRows = 506;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 26);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var refDesc = function(trackOriginSheet) {      
      var mirandaSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Miranda");
      var piperSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Piper");
      var lowesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lowes");
      var goldenSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Golden");
      var refDescContent = trackOriginSheet;
      if (trackOriginSheet === mirandaSheet) {
        var getMirSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Miranda");
        var startMirRow = 2;  // First row of data to process
        var numRowsMir = 506;   // Number of rows to process
        // Fetch the range of cells A2:B3
        var dataRangeMir = sheet.getRange(startRow, 1, numRows, 26);
        // Fetch values for each row in the Range.
        var dataMir = dataRange.getValues();
        for (var j in dataMir) {
          var secondRowMir = dataMir[i];
          var intRefDescMir = secondRowMir[3];
          return intRefDescMir;
        }
      } else if (trackOriginSheet === piperSheet) {
        var getPipSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Piper");
        var startPipRow = 2;  // First row of data to process
        var numRowsPip = 506;   // Number of rows to process
        // Fetch the range of cells A2:B3
        var dataRangePip = sheet.getRange(startRow, 1, numRows, 26);
        // Fetch values for each row in the Range.
        var dataPip = dataRange.getValues();
        for (var k in dataPip) {
          var secondRowPip = dataPip[k];
          var intRefDescPip = secondRowPip[3];
          return intRefDescPip;
        }
      } else if (trackOriginSheet === lowesSheet) {
        var getLowSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lowes");
        var startLowRow = 2;  // First row of data to process
        var numRowsLow = 506;   // Number of rows to process
        // Fetch the range of cells A2:B3
        var dataRangeLow = sheet.getRange(startRow, 1, numRows, 26);
        // Fetch values for each row in the Range.
        var dataLow = dataRange.getValues();
        for (var l in dataLow) {
          var secondRowLow = dataLow[l];
          var intRefDescLow = secondRowLow[3];
          return intRefDescLow;
        }
      } else if (trackOriginSheet === goldenSheet) {
        var getGoldSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Golden");
        var startGoldRow = 2;  // First row of data to process
        var numRowsGold = 506;   // Number of rows to process
        // Fetch the range of cells A2:B3
        var dataRangeGold = sheet.getRange(startRow, 1, numRows, 26);
        // Fetch values for each row in the Range.
        var dataGold = dataRange.getValues();
        for (var m in dataGold) {
          var secondRowGold = dataGold[m];
          var intRefDescGold = secondRowGold[3];
          return intRefDescGold;
        }
      }
  };
  for (var i in data) {
    var row = data[i];
    var teacherEmail = row[1];  // Teacher Email Column
    var iepEmail = row[16];  // IEP Manager Email Column
    var counselorEmail = row[19];  // Assigned Counselor Email Column
    var referallEmails = [teacherEmail, iepEmail, counselorEmail];   //All admin emails
    var studName = row[4] + " " + row[3];  //Stores student name in selected sheet
    var adminActions = row[3];
    var datesAppl = row[4];
    var message = "The referral submitted for " + studName + "has been completed. The following action(s) were taken:" + refDesc + " " + adminActions + " " + datesAppl + " " + "Please email the member of the discipline team who handled the referral if you have any questions or concerns, at: "; 
    var subject = "Referral: " + studName;
    MailApp.sendEmail(teacherEmail, subject, message);
  }
}
Logger.log(sendEmails);

The Code has problems, and is likely bloated beyond what it needs to be. This is my first "Practical application" of JavaScript, I've really only done debugging before. This time I'm having trouble debugging my own logic :)

I've dug around on this site and Google for an hour or so, but haven't found anything that's helped me solve my issue. Does anyone have any tips?


Solution

  • in

    var message = "The referral submitted for " + studName + "has been completed. The following action(s) were taken:" + refDesc + " " + adminActions + " " + datesAppl + " " + "Please email the member of the discipline team who handled the referral if you have any questions or concerns, at: ";
    

    I suspect you meant refDesc() instead of refDesc.