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

How to Use App Scripts to Automate in Google Sheets

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

A Quick Way To Implement Infinite Scroll in React Native

10 Indications That You Should Invest in Automation Via APIs

10 Commandments for AI-Assisted Social Media Marketers

11 Best Automation Testing Tools to Try in 2021

12 Use Cases of AI and Machine Learning In Finance

A Quick Way To Implement Infinite Scroll in React Native

10 Indications That You Should Invest in Automation Via APIs

10 Commandments for AI-Assisted Social Media Marketers

11 Best Automation Testing Tools to Try in 2021

12 Use Cases of AI and Machine Learning In Finance

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps