CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-apps
Sreenath
Sreenath

Reputation: 13

How to get the values from a range of cells in Google Sheets and compare the same with a predefined value to send mail if the condition mets

I was setting up an gooleappscript to automatically send mail if certain condition meets for a range o value in spreadsheet.I need to read values of multiple cells (A1 to A10) and check whether its less than 3 , if so then send a mail to mentioned mail id. I have done the script but its only reading value form the first cell and sends mail accordingly. My purpose is to read each selected cell values and then compare it . Any idea to correct it.

Google sheet

Below is the code am using.

function sendMail() {
  if (SpreadsheetApp.openById('1bmAFWR7YsfMlgxExLKY4**********').getRange("A2:A10").getValue()<3) ;
  MailApp.sendEmail("s*****@gmail.com", "*Subject*******", "Dear Sreenath, Your **** is getting expired in two days !");
	}

i have tried getRange("A2:A10").getValue()<3 but its reading and comparing the value from A2 only.ie in this case only value 10 is read and compared.i need to read all values from A2 to A10 and check whether its less than 3. and if any one of it is less than 3 then need to trigger the send mail command. Any help highly appreciated.

Upvotes: 1

Views: 8294

Answers (1)

ADW
ADW

Reputation: 4247

You'll need to fix a few things:

getValue() gets just one value. You want all the values in the range. So use getValues(). Ref doc here.

The values you receive are an array. You'll need to loop through the array and check each value.

Note that this

"Returns a two-dimensional array of values, indexed by row, then by column"

You may also want to specify which sheet to get the values from.

So I would try something like this:

function sendMail() {

  var ss = SpreadsheetApp.openById('1bmAFWR7YsfMlgxExLKY4**********'); // The spreadsheet

  var sheet = ss.getSheetByName("The_Name_Of_Your_Sheet_Goes_Here"); // The sheet that has the data

  var data = sheet.getRange("A2:A10").getValues(); // The values from the sheet

  for (var i = 0; i < data.length; i++) { // Loop throught the values

    if ( data[i][0] < 3 ) { // The array is a 2-d array. See ref doc in this post

      MailApp.sendEmail("s*****@gmail.com", "*Subject*******", "Dear Sreenath, Your **** is getting expired in two days !"); // Send email

    }

  }

}

Upvotes: 3

Related Questions