How to detect row and relatively go to the cell in next column?

Question

I wrote a code to send mails automatically from google sheet. Every day if column Status has the cell "Timing" I want to send email. And it works. But I don't want to send mails to the people that received mail in previous days, so that I created CHECK to fill the another column by 'sent' after sending an email. It failed. I can't detect the row with an emailaddress that program used to go relatively to another column to insert "sent".

Could you help me?

function sendEmails() {
  var she = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("basic");
  var startRow = 2;  
  var numRows = 1000;   
  var dataRange = she.getRange(startRow, 1, numRows, 1000)
  var data = dataRange.getValues();
  var text = she.getRange("L2"); 
  var message = text.getValues();
  for (i in data) {
    var col = data[i];
    var emailAddress = col[1];    
    var subject = "Project reminder";
    var status = col[3];
    var check = col[8];
    if (status == "Timing"){
      MailApp.sendEmail(emailAddress, subject, message) && 
check.setValue('sent');} //moment when I want to insert 'sent'
  }
}

Updated part of the code:

for(var a=0; a < values.length; a++ ){
   if(values[a][1] == "not" && values[a][3] == "Timing"){
     var emailAddress = values[a][0];
     Logger.log("NEED TO SEND AN EMAIL TO  " + values[a][0]);
     var message = messag + " " + values[a][5];
     MailApp.sendEmail(emailAddress, subject, message);
    }
   }
}

noogui noogui · Accepted Answer · 2017-10-12T09:38:17

Here's a mini solution for your use case. Just play with it to understand how it works. The (0,0), (0,1),etc represent the index number of the array.

Here's the sample table

function checkIfSent(){

  var ss = SpreadsheetApp.openById("123454567890abcdefghi").getSheetByName("12345");

  var values = ss.getSheetValues(1, 1, 6, 2);

  for(var i=0; i < values.length; i++ ){
    for(var j=0; j < values.length; j++ ){
      if(values[i][j] == "NOT"){
         Logger.log("NEED TO SEND AN EMAIL TO  " + values[i][0]);
         //Send an Email method here()
         //. . .
      }
    }
  }
}

If the values under column CHECK are labeled as NOT, you can place your method to send an email to that person (values[i][0]).

How to detect row and relatively go to the cell in next column?

1 Answers