Riddhesh Ganatra
3 min readMar 25

--

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')
}
}
}

--

--

Riddhesh Ganatra

Software Architect, Full Stack Web developer, MEAN/MERN stack, Microservices, etc