email from google sheets with (recipient, subject, body) set by cells in a nested tab

Question

I am trying to get a sheet to send an email when a save button is pressed. It should send it only when the right criteria is validated. The save button also copies form content onto a records page and then clears the form but the email function should happen first. Currently everything works but the email

Here is what my script looks like for the email section of the code:

function emailReferals(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('namedSheet');
  var dataRange = sheet.getRange('X44:Z53'); 
  // Fetch values for each row in the Range.
  var data = dataRange.getValues().toString();
  for (i in data) {
    var send = [0][0];
    var recipients = [0][1];
    var subject = [0][2];
    var message = [0][3];
    if(send === 'Send'){
      MailApp.sendEmail(recipients,subject,message);
    }
  } 
}

Note this is not a startRow numRow as seen in many tutorials for emails such as https://developers.google.com/apps-script/articles/sending_emails My data is deep in the page so I am calling a range such as x44:Z53 in the example.

The cells I am pulling from say things like: =CONCATENATE(Form!B3," for ",Form!D3) and =CONCATENATE(Form!D5, " was repremended for ",Form!D3,": ",Form!B7," by ",Form!F4)

Do I need to use row[#] or am I OK with [#][#]?

Also do I need something to clarify send ==='Send"?

Any help getting this work would be greatly appreciated.

I think Email notifications with body/subject containing cell value may have something that I'm missing, but I am not enough of a programmer to figure out what I'm missing?

Edit to show full code:

function onOpen() {
SpreadsheetApp.getUi().createMenu('My Menu').addItem('Copy Cells', 'copyCells').addToUi();
}


//send emails for Discipline Referrals to proper authorities.
function emailDNRReferals(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('HouseSort');
  var dataRange = sheet.getRange('X44:Z53');
  // Fetch values for each row in the Range.
  var data = dataRange.getValues().toString();
  for (i in data) {
    //var row = data[i];
    var send = [0][0];
    var recipients = "[email protected], [email protected]";
    var subject = [0][2];
    var message = [0][3];
    if(send == 'Send'){
      MailApp.sendEmail(recipients,subject,message);
    }
  } 
}


//Save and clear the form
function SaveDNRRecords() {
  var ss = SpreadsheetApp.getActive();
  var source = ss.getSheetByName('Form');
  var records = ss.getSheetByName(source.getRange('A1').getValues()); //get the tab to send the save data to.
  var val = source.getRange('B3:G11').getValues(); //get the cells with information to copy
  // get values from zero indext cells and save to records. 
  // 2,2=StudentD5.2,0=BehaviorD3.1,4=UserF-G4.0,0=CatagoryB3.0,2=BehaviorD3.4,0=NoteB-D7.8,5=TotalG11. Nulls leave space for houses on line 14. 
  var write = [val[2][2], val[2][0], val[1][4], new Date(), val[0][0], val[0][2], val[4][0], val[8][5],null, null, null, null, null, null,null];           
               write[Number(val[3][3].match(/(\d+)/)[0]) + 7] = val[8][5]; 
  records.appendRow(write);  

  var referrals = ss.getSheetByName('DisciplineReferrals'); //Save some data to the DisciplineReferrals tab.
  var val2 =source.getRange('D1:J1').getValues(); // gather content that shows only if B3=Disipline Referral
  var write = [val2[0][1], val2[0][2], val2[0][3], val2[0][0], val2[0][4], val2[0][5], val2[0][6]]; //Zero indext row from D1 to J1
  referrals.appendRow(write); //Write the data to the first available row on the selected (line 8) term tab.

  //Clear the cells for the next use.
  source.getRange('B3').clearContent();
  source.getRange('D3').clearContent();
  source.getRange('F4').clearContent();
  source.getRange('B5').clearContent();
  source.getRange('D5').clearContent();
  source.getRange('B7').clearContent();
  source.getRange('G10').clearContent();
  source.getRange('B7:D7').mergeAcross(); // this merges the cell to self heal potential user error.
  source.getRange('B11').clearContent();

}

//Clear the form without saving.
function clearDNRForm() { 
  var ss = SpreadsheetApp.getActive();
  var source = ss.getSheetByName('Form');
  source.getRange('B3').clearContent();
  source.getRange('D3').clearContent();
  source.getRange('F4').clearContent();
  source.getRange('B5').clearContent();
  source.getRange('D5').clearContent();
  source.getRange('B7').clearContent();
  source.getRange('G10').clearContent();
  source.getRange('B7:D7').mergeAcross();
  source.getRange('B11').clearContent();  
}

What is the problem specifically? You say the email doesn't work, but does it not send, or is it missing data? Does it send if you don't pull any data, and instead insert data manually into the MailApp.sendEmail() function? Also, in reference to (send ==='Send') as they should both be strings it should work, but you can test remove the 'If' function to see if that's the issue. — HDCerberus
Great point, sorry, for the lack of detail. The script runs and the data is saved and moved to other cells as it should and it clears the form. It asks for permission to send an email, the first time the script runs and I have given permission. but I never revive the email in any tests. No error message but no email either. Running without the "if" statement I still don't get an email. — Giantbean
You don't receive the email..... But is it sent? If you're using your own account to send to yourself, as far as I recall it won't appear in your inbox (you must send to another address!) So does the message appear in your sent items at all? — HDCerberus
Good thought however, I'm not seeing it in my sent mail either. If I send an email to myself I still get it in my inbox. Either way I see no indication that it was ever sent or received. — Giantbean
Yes, but sending via a script produces different behaviour. If you just run the MailApp.sendemail command with manually input data, does it work? — HDCerberus

cwatson1988 cwatson1988 · Accepted Answer · 2016-08-25T20:49:28

I tried your code and I have modified it slightly but in essence this is all your own code.

Your issue was on the if statement. The if statement below has "==" instead of "===" it could not determine the type. There possibly is a cleaner method but it works. It sends me the email, just a small fix :) (two eyes and all that!)

function emailReferals(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('sheet1');
  var dataRange = sheet.getRange('W44:Z47'); 
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var send = row[0];
    var recipients = row[1];
    var subject = row[2];
    var message = row[3];
    Logger.log(data);
    if(send == 'Send'){
      MailApp.sendEmail(recipients,subject,message);

    }
  } 
}

I would suggest taking a look at this: Tutorial: Sending emails from a Spreadsheet . It is directly from the Google Apps team and is nice a simple solution!

Good luck and hope it all works, anything else just ask!

EDIT: I have had another look at your script with fresh eyes and tested it again using the same methods as yours and there are a few things that need to be modified, here they are:

sheet.getRange('X44:Z53'); This only selects 3 columns and you need 4 this could be missing the send column, just check to see if it does include all columns or else it may not send or it will error
var send = [0][0]; you do need to use a similar method as I have used i.e. var row = data[i];var send = row[0]; because if you don't you are not actually querying the array, I checked and it was just showing blank data (this may be the main reason as to why it was not sending). The important bit is not the var row it is data[i].
I also tried it on =CONCATENATE(X44,W44)and it worked fine.

You are really close with it, like I say try and use the debug feature or Logger.log() to log the values, it really is so useful!

Good Luck! :)

email from google sheets with (recipient, subject, body) set by cells in a nested tab

1 Answers