How to Use App Scripts to Automate in Google Sheets

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