2

I have the following script in google sheets script which works fine.

function morningemail() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName("clock script");
    var startRow = 9;  // First row of data to process
    var numRows = 1;   // Number of rows to process

    var dataRange = sheet.getRange(startRow, 1, numRows, 4)
    var data = dataRange.getValues();
    for (i in data) {
        var row = data[i]
        var emailAddress = row[0];  // First column
        var message = row[2] + row[3]; // Second & third column
        var subject = row[1];    // First column
        MailApp.sendEmail(emailAddress, subject, message);
    }
}

I will set the project trigger to activate every 5 minutes but only want the script to send an email if cell "A20" (on the same spreadsheet) is ">10".

I've tried a few ways of doing this but can't get it to work. Any suggestions would be great.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Tony
  • 21
  • 1

1 Answers1

1

Short answer

Add if( data[19][0]>10) return; between the following lines:

var data = dataRange.getValues();
for (i in data) {

Explanation

In order to minimize the number of calls to the API, and considering that your script already got the data range values, use data[19][0] to get the value of A20, then use if to test if the desired condition is met and in such case use return to finalize the execution of the script.

References

Community
  • 1
  • 1
Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Hi Ruben, thanks for that. It is still not working. I think I have simplified things by putting all my data on a single line in the spreadsheet. My code is still not working – Tony Dec 23 '16 at 07:36
  • I'm sorry the row/columns were switched. I just corrected them. – Rubén Dec 23 '16 at 08:44