How to automatically send gmail by using event triggers when a spreadsheet is edited

GAS
この記事は約8分で読めます。
Advertisements

Target Users

  • who wanna use GAS(google apps script)
  • who wanna send gmail contained spreadsheet data
  • who wanna try to use event trigger
  • who wanna try to do with judging the condition of a specific part in the sheet when executing the event trigger

what you can do after reading this article

Purpose of this article is automatically sending e-mail by GAS with using event trigger when spreadsheet data is updated and met specific conditions.

It would be the best if GAS could detect a specific cell change in a spreadsheet and set an event trigger by GAS. However there are only 4 types of installable event triggers(spreadsheet related) at Open, Change, Edit, and Form submission. So, If want to fire when a particular column or row is changed, you can only do is creating your own trigger.

Therefore, we try to be implemented as following steps:

-by setting installable trigger that is executed at the time of editing(installable trigger > Edit)
-acquiring all the values in spreadsheet,
-after that sending an e-mail when the applicable data exists.

Output image(e-mail) and Target sheet image(spreadsheet)

In this article, using as following img spreadsheet.

In actual practice, more data may be included in one sheet, but for simplicity, only the columns for status management are extracted from the sheet.

This time, specific condition as follow:

  • setting No.1 ( as expressed “設定1” in img, columnB) is without “Done” (as expressed “完了”) -> so pickup on-progress(as expressed “途中”) and other (as expressed “その他”)
  • setting No.2 (as expressed “設定2” in img, columnC) is changed to “Done” (as expressed “完了”)

This time, editing the item of No.4 from incomplete(as expressed “未了”) to completed(as expressed “完了”) and receiving email with the execution result .

spreadsheet before editing
spreadsheet after editing
received gmail from GAS script

OK, well-done. let’s check actual script!

Actual Script

function sendEmailByEvent() {
  
 //Get all data from spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
 
/*
Get the edited cell, get the number of cell rows and set the variable to -1 so that it can correspond to the array
*/
  var targetCell = sheet.getActiveCell();
  var targetCellRow = targetCell.getRow()-1;
 
 //Set the email address and email subject
  var recipient = "input recipient's address", subject = "テスト";
  
 //Push data contained in acquired cell row to body array for inserting email body
  var body = []
  body.push(data[targetCellRow])
 
/*
This time main. 
Determine whether cell edited in the first if is the column C corresponding to setting 2.
In the next if, check if setting 2 is complete except for setting 1 completion. 
And send the content mail with GmailApp   
*/
  if(targetCell.getColumn() === 3){
    if(data[targetCellRow][1] !== "完了" && data[targetCellRow][2] ==="完了")
     GmailApp.sendEmail(recipient, subject, body)
  }
}

Script Explanation

As you can see from the explanation in script, it is a supplement.

/*
Get the edited cell, get the number of cell rows and set the variable to -1 so that it can correspond to the array
*/
  var targetCell = sheet.getActiveCell();
  var targetCellRow = targetCell.getRow()-1;

Since the event trigger described later is being “edited”, somebody should be editing the cell when the trigger fires. And if you can get that cell with getActiveCell, you will be able to uniquely identify which cell is edited.

Because this method is used, No.3 is the same information as No.4, which is Setting 1: Intermediate(as expressed “途中”), Setting 2: Completed(as expressed “完了”), but only No. 4 data is sent by e-mail.

Set the event trigger

setting the event trigger

For event triggers, set the event source to “from spreadsheet” and set the type “Edit”. If you don’t know how to set the trigger, it will be activated if you click the stopwatch button in the GAS editor.

Finally,

If you are in position that creating spreadsheet for controlling tasks, may often be requested to notify the people involved when sheet is edited specific condition. This script was made because I was asked a question at the non-professional programmer community where I belong.

It’s good to chat when it’s over, but do not forget , let GAS automate these worthless/meaningless tasks.

Have a nice GAS days!

コメント