CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Prime
Prime

Reputation: 1

Trigger for Google Sheets

I have a Google Spreadsheet that will send me an email once a figure in a cell reaches a certain number. I tried setting up triggers, but it doesn't work for what I need.

I need some code to open up the spreadsheet every 2 hours and then check the cell. If the number is greater than what I want, then it emails me.

Does anyone have their own code that I can add to my email function that will do this?

Any help would be appreciated.

Upvotes: 0

Views: 157

Answers (1)

Hari Krishnan K R
Hari Krishnan K R

Reputation: 93

The following code will check the value inside a cell and if the number inside the cell is greater than your specified limit, it will send an alert to the given email address.

function sendEmail() {
  // get the number in the specified cell. Here I used the cell B2 for reference
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowNum = sheet.getRange('B2').getValue(); //take the value to rowNum

  if(rowNum > your_number_limit ){
    var message = 'This is your Alert email!'; // Second column
    var subject = 'Your Google Spreadsheet Alert';
    MailApp.sendEmail("youremailaddress", subject, message); 
  }
// Send Alert Email.

}

To check the value every 2 hours do the following

From the Script Editor,

  • choose Resources > Current project's triggers. You see a panel with the message No triggers set up. Click here to add one now.
  • Click the link that says No triggers set up. Click here to add one now.
  • Under Run, select the function you want executed on schedule.
  • Under Events, select Time-driven.
  • On the first drop-down list that appears, select Hour timer
  • set interval of hours for 2 hours.
  • Click Save.

To ensure that the script runs at the correct time for a particular time zone, click File > Properties, select a time zone and click Save.

Upvotes: 2

Related Questions