Sending Email from Google Apps

Question

I'm confused with my customized script for Google Apps (particularly for Google Spreadsheets).

I reproduced the lesson from https://developers.google.com/apps-script/articles/sending_emails#section-2-improvements (see "Section 2: Improvements") but it doesn't work: script resends all the emails once again even if the cell already contains "EMAIL_SENT" (I use "NOTIFICATION_SENT" instead of this).

Here is the source of customized script:

var NOTIFICATION_SENT = "NOTIFICATION_SENT";

function NotifyNewBooking() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 47;   // Number of rows to process
  // Fetch the range of cells A2:N48
  var dataRange = sheet.getRange(startRow, 1, numRows, 17)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = "[email protected]";
    var subject = "Строка № " + row + ": " + "новое бронирование";
    var message = "Уведомление о новом бронировании!" + "\n" + "\nДанные нового бронирования:" + "\n" + "\nЗаезд: " + row[0] + "\nВыезд: " + row[1] + "\nНомер: " + "«" + row[2] + "»" + "\nТип размещения: " + row[3] + "\nЦена за ночь: " + row[4] + " руб." + "\nВнесённый депозит: " + row[8] + " руб." + "\n" + "\nИмя и фамилия гостя: " + row[5] + "\nМобильный телефон: " + row[6] + "\nЭлектронная почта: " + row[7] + "\n" + "\nПримечание: " + row[11] + "\n" + "\n* Просмотреть список всех бронирований можно тут: " + "www.bitly.com/b56guests";  
    var notificationSent = row[17];     // The column where notification status is put
    if (notificationSent != NOTIFICATION_SENT) {  // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message, {name: "Мини-гостиница Бердянская 56", replyTo: "[email protected]"});
      sheet.getRange(startRow + i, 17).setValue(NOTIFICATION_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

The goal here is to send emails only if the cells in the column row[17] doesn't contain NOTIFICATION_SENT. Please, provide me with strong advice how to fix it.

Thank you so much in advance!

m5khan m5khan · Accepted Answer · 2016-06-20T08:24:12

The problem is in this line : var notificationSent = row[17];

Try debugging the code by logging the values Logger.log(row[17]) and Logger.log(row[16]) and you will get undefined in row[17] and NOTIFICATION_SENT in row[16].

Why? Because Range.getValues returns a 2 dimensional array and array indexes start from 0.

It is also specified in the documentation.

Remember that while a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].

So while row is fine because you have initialized i to 0 in your for loop.

for (var i = 0; i < data.length; ++i) { 
    var row = data[i]; 
    ....

you should subtract 1 from index when getting a column.

var notificationSent = row[17 - 1];

because if you have 17 columns, first value in array will start from index 0 up to 16.

Sending Email from Google Apps

1 Answers