Our Problem Statement: A requirement to send a text message in the form of an SMS message to a patient when there is an entry for an appointment scheduled for them and logged into the Google Sheets. Sample data: Step 1: Add a button in the Google sheet Insert -> Drawing Step 2: Create a custom function that can be triggered by clicking on the button. Extensions -> Apps Script Step 3: Code to send SMS for appointments function myFunction() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('appoinments'); ja // 2 dimentional array for sheets data var data = sheet.getDataRange().getValues(); var timeZone = Session.getScriptTimeZone() for (var i = 1; i < data.length; i++) { // condition to send message to whome we have not already sent message and first column is not blank if (data[i][4] != "done" && data[i][0] != "") { var date = Utilities.formatDate(data[i][2], timeZone, 'dd-MM-yyyy') var time = Utilities.formatDate(data[i][3], timeZone, 'h:mm a') var name = data[i][0] var phonenumber = data[i][1] Logger.log("date: "+date) Logger.log("time: "+time) Logger.log("name: "+ name) Logger.log("phonenumber: "+phonenumber) // API to send SMS(Any provider) // Hit api to send sms // var url = `Put your sms provider url here` // var response = UrlFetchApp.fetch(url); // var json = response.getContentText(); // set status to done, so we dont send messages again for same appoinments sheet.getRange(i + 1, 5).setValue('done') } } } Step 4: Run and give permissions. Click on Advance on the bottom left: Click on, “Go to the untitled project.” Click on allow: We can see logs as expected: Now, we can see the status column set to “done.” Step: Attach the function to the button. Click on the button > assign a script > write the name of the function that we just made. Now, add new appointments to the sheet, and keep the appointment status blank. When we click on the send message button, an SMS will be sent, and the status will be updated in the sheet. Share this with anybody you think would benefit from this. Have any suggestions or questions? Feel free to message me on . LinkedIn Originally Written by Riddhesh Ganatra on Medium.com

Too Long; Didn't Read

Making security fun one episode at a time

How to Use App Scripts to Automate in Google Sheets

Too Long; Didn't Read

Riddhesh

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

How to Use App Scripts to Automate in Google Sheets

Too Long; Didn't Read

Riddhesh

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES