Google Console Project

Once you have a Google account, go to the Google Console and create a new app. By default the Google Console page shows you the dashboard of the most recent app that you’ve worked on. But if you haven’t work on any projects yet it will show the following:

From there you can click on the select project menu at the upper right corner and select create a project. This opens a modal window which allows you to enter the title of the project.

Once the project is created, the dashboard is displayed. From there you can click on the use Google APIs, search for the Google Calendar API and enable it.

Once the API is enabled, it will ask you to create credentials. Click Go to Credentials to begin setting it up. This will show you the following:

Click on the Add credentials button then select OAuth 2.0 client ID.

This will ask you to configure consent screen first. Click on configure consent screen.

Enter a value for the Product name shown to users text field and click on save.

Once that’s configured you can now create the client ID. Select Web application for the application type, leave the default name (if you want), enter http://localhost:3000/login for the Authorized redirect URIs then click create.

This opens a modal which displays the client ID and client secret. Take note of those for now as we will be using them later.

Twilio

Once you’ve created a Twilio account, go to the settings page and take note of the values for the AccountSID and AuthToken under the Live API Credentials.

Next go to the programmable voice dashboard. This is where you can see the sandbox number. You can use this number for testing twilio. But Later on you will need to buy a phone number so that the text messages sent by twilio won’t have “sent from twilio sandbox” added to it. Another limit of the Twilio sandbox number is that it can only be used with verified numbers. Which means you have to register a phone number with twilio in order to send a message to it. You can do this from the manage caller IDs page.

Building the App

Now we’re ready to build the app. Before we proceed I’d like to provide a brief overview on how we’re going to implement the app. There’s going to be three major files: one for the server, one for caching events from Google Calendar and one for reminding the user. The server is used for allowing the user to login and obtaining an access token. The events will be saved in the MySQL database and the global app configuration will be added in a .json file. Node’s implementation of cron will be used for executing the task for caching events and reminding the user.

Installing the Dependencies

On your working directory, create a package.json file and add the following:

twilio – the official Twilio client for Node.js. This allows us to send text reminders.

Execute npm install from your terminal to install all the dependencies.

Database

As mentioned earlier, we’re going to use the MySQL database for this app. Go ahead and create a new database using the database management tool of your choice. Then use the following SQL dump file to create the tables: appointment-notifier.sql.

There are two tables in the database: users and appointments. The users table is used for storing the user’s data. In the case of this app, we’re only going to store one user, and only the access token is stored.
The appointments table is used for storing the events which we got from the Google Calendar API. Note that it has no user_id field in it because we only have one user. And we’re going to fetch all the rows which have zero as the value for the notified field.

App Configuration

On your working directory, create a config folder then inside it create a default.json file. This is where we will put the global app configuration. This includes the timezone, the phone number to which we’re going to send the reminders, the database, google app and Twilio settings.

Common Files

As good developers we need to avoid code repetition as much as we can. That’s why we need to put code that’s needed by those three major files (server, cache, notify) that I mentioned earlier into separate files. Create a common folder on your working directory. This is where we’re going to add the common files.

Database

Create a db.js file inside the common directory then add the following:

This uses the config library to get the configuration values that we’ve added earlier on the config/default.json file. Specifically we’re getting the database configuration so that we can connect to the database. Then we’re exporting this module so that we can use it later on from another file.

Time

The time.js file is used for setting the default timezone with the moment-timezone library. We also export the value for the timezone since we’re going to use it later when running the two cron tasks (caching events and notifying users).

Google

The google.js file is used for initializing the Google client and the OAuth2 client. In order to initialize the OAuth2 client we need to pass in the client ID, client secret and the redirect URL which we have added in the configuration file earlier. Then we initialize the Google Calendar service. Lastly, we export the OAuth2 client, calendar and the Google configuration.

Creating the Server

Now we’re ready to work on the server. The server is responsible for getting the access token. Which can be used for talking to the Google Calendar API without the user being currently logged in. Begin by creating a server.js file and adding the following:

Define an updateAccessToken function. This accepts two arguments: tokens and response. The token is the access token which we get from Google after the user has given the necessary permissions. And the response is the response object that comes from Express. We’re passing it to this function so that we could send a response to the user. Inside the function, we update the access_token of the first row. As mentioned earlier, this app only works for a single user. Once the access_token is updated, we send the response.

Add the route for the home page. This gets executed when http://localhost:3000 is accessed. From here we generate the authentication URL. This uses the generateAuthUrl method from the oauth2Client. It accepts an object containing the access_type and the scope. We’re getting those values from the app configuration file that we’ve created earlier. Lastly, we send the actual link that the user can click on. Note that you should always do this inside a view, but in order to simplify things we’re just going to directly return the link.

Add the route for the login. This is the route where the user gets redirected after giving the necessary permissions to the app. Google passes in a query parameter called code. And we’re getting it through the query object in the request. We then call the getToken method and pass in the code as the argument. This would give us the access token. So we call the updateAccessToken function to save it into the database.

Creating the Cacher

The cacher is responsible for saving the users appointments into the database. This prevents us from having to query the Google Calendar API directory every time we send reminders. Create a cache.js file and add the following:

The addAppointment function is responsible for saving the appointments into the appointments table. This accepts the event_id, summary, start and end datetime of the appointment. The event_id is basically the ID of the specific appointment in Google Calendar. We’re using it as the value for the primary key, which means that duplicates won’t get inserted into the appointments table. What’s lacking here is the means to compare the appointments that are already in the database and the ones returned by the API. If for some reason the schedule of the appointment changes, the database won’t be updated since all we’re doing here is inserting into the table. I’ll be leaving that for your todo list.

The cache method is the one that’s making the actual call to the Google Calendar API. This is through the use of the Google Client. Here we’re calling the list method on the calendar.events object. This accepts two arguments: first is the object containing the options for the query and the second is the function to be executed once a result is returned.

auth – this is the oauth2Client. This is used for authenticating the request.

calendarId – the ID of the calendar in which we’ll be getting the appointments. In this case we’re using the primary calendar. Google Calendar actually allows you to create many calendars. Others can also share their calendars to you. And each of those calendars have their own ID. That is what we’re specifying here. If you’re interested in accessing other calendars be sure to check out the API documentation on Calendars.

timeMin – the base datetime to be used in the query. In this case we’re using the current datetime. Because who wants to get notified of an event that happened in the past? Note though that this uses the ISO 8601 standard for representing the time. Thankfully there’s a method called toISOString in moment which we can use to get that.

maxResults – the total number of results you want to return.

singleEvents – allows you to specify whether to return only single one-off events. Here we’ve used true which means that recurring events wouldn’t get returned.

orderBy – allows you to specify the order in which the results will be returned. In this case we’ve used startTime which orders the result in ascending order based on their start times. This is only available if singleEvents option is set to true.

Get the access_token from the database and use it for setting the credentials for the oauth2Client client. Once that’s done, create a new cron job which will run the cache method every day at 12 midnight.

Creating the Notifier

Last but not least we have the notifier (notify.js). This is responsible for getting the appointments from the database and determining if they’re ripe for notification. If they are, then we send them.

Create an updateAppointment function. This accepts the ID of the appointment as its argument. All it does is set the value for the notified field to 1 which means that the notification for the specific appointment has already been sent.

Next we have the sendNotifications function. This is responsible for actually sending the text reminders with Twilio. This function gets called after fetching the appointments from the database. That’s why it has the error, results, and fields arguments passed into it. The error contains any error from the database. The results contain the rows returned from the database. And the fields contain information about the returned results fields.

Check if there are any errors and if there are none proceed with looping through all the results that were returned.

if(!error){
for(var x in results){
...
}
}

Inside the loop we extract all the values that we need and construct the actual message to be sent. We also get the hour difference between the current time and the start time of the appointment. We check if the hour difference is less than or equal to 24 hours.

If it’s less than or equal to 24 hours, we send the notification. This is through the use of the Twilio client. We call sendMessage and pass in an object containing the to (the users phone number), from (Twilio’s sandobox number or a phone number that you bought from Twilio), and the body which contains the text message. If there aren’t any errors returned, we assume that the notification has been sent. So we call the updateAppointment function to set the notified field to 1, so it won’t get selected the next time the task runs.

Finally we have the startTask method. All it does is to select all the appointments from the appointments table whose notification hasn’t been sent yet. This function gets executed every 12 noon and 6pm.

Conclusion

That’s it! In this tutorial you’ve learned how to create an SMS reminder app with Twilio. Specifically we’ve taken a look at how to get the user’s appointments through the Google Calendar API. We have saved those in the database and notified the user via Twilio. You can find the code used in this tutorial from the github repo.

Wern is a web developer from the Philippines. He loves building things for the web and sharing the things he has learned by writing in his blog. When he's not coding or learning something new, he enjoys watching anime and playing video games.

Free Guide:

7 Habits of Successful CTOs

"What makes a great CTO?" Engineering skills? Business savvy? An innate tendency to channel a mythical creature (ahem, unicorn)? All of the above? Discover the top traits of the most successful CTOs in this free guide.

waltergms

Awesome! Will try it latter at home!
Thank you sr!

normjp

I need help running the application. My Calendar-Notifier started okay and I got through the login on my Chrome browser and received the response “connected” on the web page (from the nodejs Console command). Not sure where to go from here and actually run the application so that I can get my twilio to SMS reminders. Is there much more to this course that I’m missing? I did notice the one login token info in my MySQL file for the one user and that of course is my Google Account Info. Is there specific directions how to list appointments and add appointments etc?
Also, if I want to run this as a service on my web server, can I add all my clients and their independent Calendars. Do you have any suggestions and procedures for adding multiple users?

Thanks,

normjp

Got through the login, got “connected”. Where to now?? Not sure of the routes or the html pages that will enable me to see appointments, and set up the notifier’s. Google Calendar only enables email and popup notifications. Shouldn’t I now have the SMS option on my event page for a specific appointment?? Not sure of what web pages I am going to see? Thanks

normjp

Please disregard my two comments below as I’ve been able to figure this out on my own and it is working now as I’ve come to understand the setup and the Calendar-Notifier integration with nodejs and the Google Calendar api. It is a “slick” application and I appreciate the SitePoint lesson. I had run the server.js in nodejs, and I got through all the Auth and the login and the “connected” webpage, however, I had failed to run the cach.js and the notify.js applications via node which execute the Cron jobs (which I have very little experience with).

Now that it is working with my Google Calendar, is there some suggestions for using this application for a hosting service where respective Google Calendar users can utilize this nice Twilio SMS feature as the Google Calendar notification only supports email and popup. They used to support SMS which was nice and I used it often.

Wern, thanks for the lesson and the input. Sincerely, Norm

Wern_Ancheta

hi @norm you can replace localhost with a URL on your website. Then login on your website instead of in localhost to authenticate the app.