CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Gilgamesh1401
Gilgamesh1401

Reputation: 59

Send an e-mail if a cell value meets a given criterion

I'm working on a scoreboard automation process using Google Sheets, but I've come up with a little problem. I tried searching here but the question has been treated in a unclear way to me.

What am I trying to do is quite simple:

I want an automatic e-mail to be sent to a specific person IF cell value > X (threshold). I already know I need to use Google Apps Script for that, but I haven't found much interesting code lying around yet so I was wondering if you guys had an idea how that would work?

I tried something based on this thread with no success: How do I make a Google Sheet script send an email when a specific cell's value changes?

Upvotes: 1

Views: 10762

Answers (2)

Gilgamesh1401
Gilgamesh1401

Reputation: 59

function sendMail() {
  if (SpreadsheetApp.getActive().getSheetByName('Scoreboard').getRange('AL2:AL1000').getValue()<2.70) return;
  MailApp.sendEmail("yvan@********.com", "******** new potential candidate available ! ", "Dear ****, a new candidate with strong potential is available for due dil !", {
        name: "SOURCE NAME"
    });
}

Here is an example of code that works the best and is the shortest. The thing is, I want it to scan only the cells that has changed (updated) within the column in question and not the old responses (data source is a form). What do you think i should do ? Because i don't want to erase the previous response within the same column.

edit: I use an "onChange" trigger.

Upvotes: 0

mongoose36
mongoose36

Reputation: 799

@Yvan1401, You can use a script like this along with an installable trigger like onEdit or onChange to accomplish what you wish. To set up the installable trigger follow the steps below (found here):

  1. From the script editor, choose Resources > Current project's triggers.
  2. Click the link that says: No triggers set up. Click here to add one now.
  3. Under Run, select the name of function you want to trigger. Under Events, select From spreadsheet.
  4. Select and configure the type of trigger you want to create (e.g. on Change or on Edit trigger).
  5. Optionally, click Notifications to configure how and when you will be contacted by email if your triggered function fails.
  6. Click Save.

Please see comments in code for portions to change.

function SendEmail() {
  var ui = SpreadsheetApp.getUi();
  var file = SpreadsheetApp.getActive();
  var sheet = file.getSheetByName("Sheet1");  //Change as needed

  if(sheet.getRange(5,1).getValue()>10){      //change row and column in get range to match what you need
    MailApp.sendEmail("[email protected]", "subject", "message");
  }
}

Upvotes: 1

Related Questions