Automaton in Google apps script and google sheet - CodyLab

 Send Automatic SMS or Email to  particular users if Criteria met

Example of automatin Flows in apps script projcet

        In this article we are going to show how we can send automatic sms or email using apps script.

Before star creation, we have to focus on following topics to doing well.

• To set automation in apps script, we need to set trigger to run function on given time frequency.
• We have to set criteria to prevent sending sms or email unnecessarily.
• We need a spreadsheet to store data and system settings. We'll use data like email, mobile number, username and data that have to send the users time to time. 
• We suggest you to set trigger weekly to save sms balance from getting waste.

        Lets create a spreadsheet and arrange the data as need. see following example for hints.

• In above example sheet, You can see  that first column of sheet containts UserId. We have used userId to controll data by CRUD web app. to know about CRUD, click here.
• First row of the sheet is reserved for system setting and information. You cant controll on function from this row. To prevent the function from sending sms/email, you have to set Deactivate value in column E1.
• Column C and D containt mobile number and email id that I have to send sms/email using it.
• Columns G and H contain Balance and Status details of the users, We have used 'column H'  as a criteria. Function looks the criteria before sending sms or email to the users. 
• If criteria column H, display value 'Dues', Function sends sms/email to all due listed user. 
• Column H has  a fourmula "=if(G3>0,"Dues","Advance")" to  show user balance status.

            Now, we move to Google script page, and creating a new project. In this project paste the following given code, and rearrange it as your need or given criteria.

---

Code.gs                     For Sending SMS only           Fast2SMS API  

function sendsms(){

  var ss = SpreadsheetApp.openById("1EmyJvdxxxxxxxx  YOUR SHEET ID  xxxxx7Pg");

  var sheet = ss.getSheetByName("Users");

  var userlist = sheet.getDataRange().getValues();

     for(var i = 0; i<userlist.length ; i++){

  var check    =  userlist[i][7];

    if(check === "Dues"){// chech if user is in due list.

  var contacts = userlist[i][2];

  var userId   = userlist[i][0];

  var userName = userlist[i][1];

  var balance  = userlist[i][6];

  if(contacts !=""){ // if user have no contact, function skips for him.

  var smsBody  = "Dear "+userName+" ["+userId+"], Your total due balance is"+balance

+". Please pay this soon.";

  var apikey   = "Paste FAST2SMS API";

  var param    = apikey+"&sender_id=TXTIND&message="+smsBody+"&route=v3&numbers="+contacts+"";

  var url = "https://www.fast2sms.com/dev/bulkV2?authorization="+param;

  var options = {

  "async": true,

  "crossDomain": true,

  "method": "GET"

    }

  }

  var response = UrlFetchApp.fetch(url, options);

  var data = JSON.parse(response.getContentText())

var d = new Date();// set last run date

  sheet.getRange(1,7).setValue(data.message+" "+d)

    }else{

  return false;

    }

   }

}

---

        To add trigger in your project, Click on clock icon and set as following example.

        Now set trigger run time in your project, like following hints.

Code.gs For sending automatic Emails to Due-listed users.

        Now we are going to write a code that send automatic email to the due-listed

users. Same as above example, you have also to set trigger in following code.

---

function sendEmail(){

  var ss = SpreadsheetApp.openById("1EmyJvdxxxxxxxx  YOUR SHEET ID  xxxxx7Pg");

  var sheet = ss.getSheetByName("Users");

  var userlist = sheet.getDataRange().getValues();

     for(var i = 0; i<userlist.length ; i++){

  var check    =  userlist[i][7];

    if(check === "Dues"){

  var emailid  = userlist[i][3];

  var userId   = userlist[i][0];

  var userName = userlist[i][1];

  var balance  = userlist[i][6];

  if(emailid !=""){

  var emailBody = "Dear "+userName+" ["+userId+"], Your total due balance is"+balance

+". Please pay this soon.";

  MailApp.sendEmail({

    to: emailid,

    subject: 'DUE INFO OF ID: '+userId,

    htmlBody: emailBody,

    });

  }

  var d = new Date();

  sheet.getRange(1,7).setValue(d)

    }else{

  return false;

    }

   }

}

---

        Note:- After saving Code.gs file you have to pass permission to run the function.

We suggest you to run the function manually for first time. If any error you got

let me know about.

>>>TRY TO CHECK OUT , IF ANY ERROR FOUND. PLEASE LET ME KNOW BY COMMENT.

I'LL TRY MY LEVEL BEST TO FIX THE PROBLEM.

THANKS FOR VISITING CodyLab

                                                                                 Have a nice day!

-------------------------- -------------------------

Donate me through - PayPal or RozorPay or Paytm