Google Script to Send Email containing values from Sheet

Question

I am trying to get a script working that hooks into a google sheet, pulls information that is taken of the phone, and sends it via email once marked. So far I have this, from sources on the internet and a bit of customising.

function sendApprovalEmail() {

var sheetNameToWatch = "SHEETNAMEGOESHEREUSUALLY";
var columnNumberToWatch = 12; // column A = 1, B = 2, etc.
var valueToWatch = "SENT";

var date = 5;
var name = 9;
var number = 10;
var message = 11;

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveCell();

if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
var emailAddress = "MYEMAILGOESHEREUSUALLY";
var subject = "Missed Call Notifcation";
var email = "Hello! "+date+" "+name+" "+number+" "+message+"";
MailApp.sendEmail(emailAddress, subject, email);
}
}

The above works, triggers, and sends properly. The only thing it doesn't do is suck in the Date, Name, Number and Message.

These need to be unique for each email, based on the line that was just marked as SENT. Usually, there are only a couple a day, never at the same time.

So if Row 23 is marked as SENT, it needs A23, B23, and C23. If row 66 is marked as SENT, it needs A66, B66, and C66.

How do I get the script to look up values in column 5, 9, 10 and 11 OF THE ROW that it has detected 'SENT' in?

Answer 1

The code is using var range = sheet.getActiveCell() the get the active cell. Then you could use range.getRow() to get the row number, then you could use something like sheet.getRange(range.getRow(), columnNumber) to get the desired cells where columnNumber could be 5, 9, 10 and/or 11. In other words, instead of

var date = 5;

use

var date = sheet.getRange(range.getRow(), 5).getValue();

but put the above line after

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getActiveCell();

Do the same for the name, number and message variables.

Note: There are other more efficient ways like using sheet.getDataRange().getValues() which will return an array of arrays having all the sheet values, the use

var date = data[range.getRow()][5];

and so on.

Answer 2

This is probably close to what your looking for:

First you have to settle on a format for your spreadsheet so that all operators will record the information in a standard format. I used the following format for each of my sheets.

All of the sheets used for recording information start with the prefix of 'ph:'. I get the entire array of sheets and I loop through them looking for the sheets that begin in 'ph:' name.slice(0,3). Once found I get the range of A1:B6 and collect all of the data into one array and then loop through the array putting all of the information into an associative array using the following algorithm dObj[value in column1]=value in column2. So in my example you can get the name with dObj.Name and the message is obtained by using dObj.Message and so on. So this associates the key in column one with the value in column2.

Now here is the code:

function sendApprovalEmails(){
  var ss=SpreadsheetApp.getActive();
  var shts=ss.getSheets();
  for(var i=0;i<shts.length;i++){
    var sh=shts[i];
    var name=sh.getName();
    if(name.slice(0,3)=='ph:'){//finds the operator log sheets
      var dObj={};
      var rg=sh.getRange('A1:B6');//Use a fixed range so you can use the rest of the sheet for whatever
      var vA=rg.getValues();
      for(var j=0;j<vA.length;j++){
        dObj[vA[j][0]]=vA[j][1];//Builds the associative array
      }
      if(dObj.Approval=='Yes'){
        Logger.log('Send Email:\nDate: %s\nNumber: %s\nName: %s\nMessage: %s,To: %s',Utilities.formatDate(new Date(dObj.Date),Session.getScriptTimeZone(),"E MMM dd, yyyy"),dObj.Number,dObj.Name,dObj.Message,dObj.Email);
        var recipient=dObj.email;
        var subject="Missed Call Notification";
        var body = Utilities.formatString('Hello! %s %s %s %s',Utilities.formatDate(new Date(dObj.Date),Session.getScriptTimeZone(),"E MMM dd, yyyy"),dObj.Name,dObj.Number,dObj.Message);
        //MailApp.sendEmail(recipient, subject, body);
      }
    }
  }
}

In my example B1 has a data validation of either Yes or No.

Of course this is probably just a starting point. You can create the code to copy a master sheet and open it up with each phone call. I don't know if there is a limit to the number of sheets but there is a a limit to the number of cells in one Spreadsheet.

Documentation Reference:

By the way this version of your code works.

function sendApprovalEmail() {

var sheetNameToWatch = "test";
var columnNumberToWatch = 1; 
var valueToWatch = "SENT";
var date = 5;
var name = 9;
var number = 10;
var message = 11;

var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var range = sheet.getActiveCell();

if (sheet.getName()==sheetNameToWatch && range.getColumn()==columnNumberToWatch && range.getValue() == valueToWatch) {
var emailAddress = "[email protected]";
var subject = "Missed Call Notifcation";
var email = Utilities.formatString('Hello! %s %s %s %s',date,name,number,message);
//MailApp.sendEmail(emailAddress, subject, email);
  Logger.log('\nemailAddress: %s\nsubject: %s\nemail: %s',emailAddress,subject,email);
}
}

I don't like to send out a lot of useless emails while debugging so I just use the Logger to record the email information.