Automation in google sheet using Apps Script

We had a requirement to send an SMS to a patient when there is an entry for an appointment for them in google Sheets.

Sample data:

Link for sample sheet:

Google Sheets: Sign-in

Step 1: Add button in google sheet

Insert -> Drawing

Step 2: Create a custom function that can be triggered on click 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: 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 button

Click on the button > assign a script > write the name of the function which we just made

Now add new appointments to the sheet and keep the appointment status blank. When we click on 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.

We at Code B are a team of Fullstack software developers, passionate and dedicated to growing businesses for clients.

We have experience in Web Applications(Frontend and Backend), Mobile Applications(Native and Hybrid), DevOps (AWS, GCP, Azure, Digital Ocean, and Heroku), Blockchain(Solana), and UI/UX Design(Figma).

Contact us if you need any help with Software.