Take the 2-minute tour ×

I'm still learning the ropes here & was hoping somebody could kindly suggest the best way of going about this (I have searched for a solution but to no success!):

I have very simple spreadsheet:

Column A: e-mail addresses; Column B: messages to send. (Column A is generated using a formula which grabs a few e-mail addresses from a longer list of addresses in Sheet2.)

Sheet2 contains a 'master' list of all the e-mail addresses.- Column A: Name; Column B: e-mail addressess

I am using the code below as a starting point. It send e-mails to the addresses in Sheet1, and marks the cells in Column C with "EMAIL_SENT" whenever the e-mail is sent (or rather when the script is done processing that row).

I'd like to keep count of the total number of e-mails sent to each person in column C of Sheet2, however I don't know what the best way to approach this would be, or if I'm honest, where to start!

function sendEmails() {

  var sheet = SpreadsheetApp.getSheetByName('Sheet1');
  var startRow = 1;  // First row of data to process
  var dataRange = sheet.getDataRange()
  var data = dataRange.getValues();

    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column

    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates

      var subject = "Sending emails from a Spreadsheet";

      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);

So as mentioned, I'd like to keep count for each person in Sheet2, because it contains everyones e-mail addresses (whereas Sheet1 only has a handful of these and this list will change daily).

Can anyone please shed some light on this? Any suggestions would be very much appreciated.

Many thanks for looking.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Step 1: Find the row in Sheet2 where that email is located.

var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var numRows = sheet2.getDataRange().getNumRows();
var emailList = sheet2.getRange(1,2,numRows,1).getValues();
var emailRow = -1;
for (var j=0; j<numRows; j++) {        
  if (emailList[j][0]==WHATEVER_EMAIL_YOU_ARE_LOOKING_FOR) {
    emailRow = j;
if (emailRow==-1) { /*ERROR! Couldn't find email.*/ }

Step 2: Get the old count of emails.

var oldValue = sheet2.getRange(emailRow+1,3).getValue();

Step 3: Write in the new value + 1.


Note: emailRow is zero-based, while getRange() is one-based.

share|improve this answer
Hello Phil, Thank you for your solution. Unfortunately I can't get the code to loop through all of the emails in sheet1. It was working before I added your code, so I guess I'm putting your code in the wrong place? Please could you tell my why this isn't working: I replaced 'WHATEVER_EMAIL_YOU_ARE_LOOKING_FOR' with 'emailAddress' and I added Step1 code at the bottom of my script, directy under "SpreadsheetApp.flush();". I pasted step 2 & 3 code under step 1 code directly under " { /*ERROR! Couldn't find email.*/ }". Thanks in advance. –  Vin Feb 10 '13 at 22:05
Apologies. That's because I unintentionally reused the variable i. Change all times that I used i in my Step 1. That should fix it. I will update my answer. –  Phil Bozak Feb 11 '13 at 1:12
Thank you very much indeed, Phil. The script now works perfectly. My apologies for not spotting that either! –  Vin Feb 11 '13 at 1:51
I just noticed another 'i' that needs replacing, which I can't edit since it's less than 6 characters. It's within: "for (var j=0; i<numRows; j++) {". Also, should it not be j<=numRows, rather than j<numRows? –  Vin Feb 11 '13 at 2:34
Fixed it. =) All better now. –  Phil Bozak Feb 11 '13 at 3:14
show 3 more comments

Your Answer


