CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetssms
tinman
tinman

Reputation: 21

Google Sheets Send SMS to a specific person based on status change of a cell

If you are reading my question, Thank you for taking the time out of your day to help.

Background: I have a form that my field techs use in order to request parts, I would like to keep them updated on the status of a part order with an automated sms text to their phone.

Specifics: Link to test Sheet https://docs.google.com/spreadsheets/d/1hEDEk-3-z3Wh6PNLoY6PgmbSJZ7OcdMR0kFCl0BRrYU/edit?usp=sharing

Parts Request (Picture)

When a Status is changed in (column G), this will trigger an SMS to be sent.

Employee Info (Picture)

Employee Information: The Script pulls the Employee Telephone number(Employee Info! B2) from the Employee Info Sheet

Text Body: The Message that is sent would be the entire row in the text message -Team Lead -Type of Request -Job Name -Part Description -QTY Missing -Status

The Script i have tried so far has been a simple one, based on a trigger of anytime a change is made to the sheet. Here is what i have used so far, this has worked and has been sending generic texts. Any Help would be greatly appreciated.

   function sendText() {
  var EmailTo = "'Mobile Number'"@txt.att.net";
  var subject = "Whatever";
  var body = "Text";

  MailApp.sendEmail(EmailTo, subject, body);
}

Upvotes: 1

Views: 2052

Answers (1)

PatrickdC
PatrickdC

Reputation: 2421

The processes needed to achieve your goal involves fetching the desired details on the row wherein the status of the request changes, incorporating the given phone number to the appropriate carrier domain, and adding an installable trigger so that the script will automatically work when there are changes to the Status Column.

Here is the script:

function sendUpdates(e) {
  var ss = e.source;
  var shEmployeeInfo = ss.getSheetByName("Parts Request");
  var shPartsRequest = ss.getSheetByName("Employee Info");
  var row = e.range.getRow();
  var column = e.range.getColumn();
  if(column == 7) { //To limit checking of changes to the Status column
    var info = shEmployeeInfo.getRange(row, 2, 1, 6).getValues();
    var header = shEmployeeInfo.getRange(1, 2, 1, 6).getValues();
    var carrier = shPartsRequest.getRange(row,4).getValues();
    const subject = "Insert Subject Here."; //Edit this to change the subject
    const carrierMap = { "ATT": 'txt.att.net',
      "T-Mobile": 'tmomail.net',
      "Sprint": 'messaging.sprintpcs.com',
      "Verizon": 'vtext.com',
      "Cricket": 'mms.mycricket.com' }
    var phoneNumber = shPartsRequest.getRange(row,2).getValues();
    var emailTo = `${phoneNumber}@${carrierMap[carrier]}`;
    var body = "";
    for(let i = 0; i <= 5; i++) {
      body += header[0][i] + ": " + info[0][i] + "\n";
    }
    MailApp.sendEmail(emailTo, subject, body);
  }
}

The installable trigger should be set as: enter image description here

Please refer to the Installable Triggers Guide for more information.

As for the result, I made a test case and got this:

enter image description here

Upvotes: 1

Related Questions