I used this method to send SMS messages as emails:
The context is that Chefs will mark the status of the order. This updates column B of my “Orders” sheet.
Columns G-I of this sheet contain the phonenumer@carrier information and a message. The message column (I) is a template that contains the order status and the name of the restaurant.
I wanted to send an email (SMS message) when the status changes in Column B to either “Being Prepared” or to “Ready” (there are 4 possible statuses).
Here was the code I used:
// The column you want to check if something is entered.
var COLUMNTOCHECK = 2;
// Sheet you are working on
var SHEETNAME = 'Orders';
function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//checks that we're on the correct sheet.
if(sheet.getSheetName() == SHEETNAME) {
var selectedCell = ss.getActiveCell();
var selectedRow = selectedCell.getRow();
var dataRange = sheet.getRange(selectedRow, 7, 1, 3);
var data = dataRange.getValues();
//checks the column to ensure it is on the one we want to cause the date to appear.
if(selectedCell.getColumn() == COLUMNTOCHECK) {
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[0]; // First column
if (emailAddress.match('@') === null){
continue; // skip this iteration of the loop and go to the next one
};
var message = row[2]; // Third column
if (message.match('!') === null){
continue; // skip this iteration of the loop and go to the next one
};
if (message.match('complete') !== null){
continue; // skip this iteration of the loop and go to the next one
};
var subject = 'Potluck Order Status';
MailApp.sendEmail(emailAddress, subject, message, {name:'Potluck', noReply: true});
SpreadsheetApp.flush();
}
}
}
}
Finally, had to create an onChange trigger since the spreadsheet doesn’t recognize Glide writes-to-sheets as onEdit triggers.
Works like a charm!