This article is about how to use Zapier in conjunction with Google Drive, Google Contacts, Gmail and Google Calendar to send SMS (text) and/or email reminders for appointments to your clients. I had a fun time making this and wanted to share.

The basic process, entirely automated except the first step, is this:

Add an appointment to a Google calender. Zapier monitors the calender for appointments that are beginning within 24 hours. When it finds one that matches the criteria, it puts the appointment title and date/time info into a new row on Google Drive Spreadsheet. The Google Drive Spreadsheet uses a script to look up the client in Google Contacts, and grab SMS and email contact info, which it then places on the spreadsheet. Zapier picks up the updated info and sends reminder emails and SMSs through Gmail. I use the SMS@carrier-SMS.com format to send SMSs.

Sounds simple enough? It is, it just takes a bit to set up. I’m going to describe everything you need to know to set this up, hopefully, depending on your competence with computers. I’m not going to get into too much detail on some of the technical stuff in this article, so you might have to search the web for some finer details.

I was using IFTTT and Apptoto for a similar process, which was slightly less complex, but which would have started costing me money after my initial free trial with Apptoto expired. The hack I’m describing here is free. It’s complex enough that most people are probably going to want to just shell out money for Apptoto or a similar service. But for the brave and geeky solopreneurs out there, this might be useful.

Limitations: this hack allows you to send weekly reminders to 12-25 clients (depending on whether they prefer SMS or email reminders, or both). More than that, and you’re going to have to pay Zapier to send out more reminders, starting at $15/mo. At that price, you might start looking at Apptoto or something similar. Also note that this (probably) doesn’t work with scheduling apps.

Set up your calender

Set up a Google Calendar for your client appointments. I use a code for each client: their initials (sometimes adding the second or third letters of their last name to distinguish them from similarly-named clients). I use that code as the title of the event. The spreadsheet script, described later, requires that you put some sort of code in as the first string (not including the time, which won’t be part of the event title once it is entered). This code must match what you put in Google Contacts for your clients. You can also include whatever other info you want in the event title, just be sure to include the code first, followed by a space if you include more info. I’m going to use “AA” for “Anonymous Aardvark” as an example.

Set up your client contacts

In Google Contacts, create a contact for each client that you want to send reminders to. There is one very important piece of information you need to include: the client code. At the bottom of the contact, click on “Add custom field”, and type in the contacts client code in the box that appears. Then edit the title of that field by clicking on “type here”, and changing it to “Client Code” (very important to use that exact text). Again, you can use whatever you want for the code – just make sure this field is consistent with the client code you used in the title of your event in the calendar.

The other two pieces of information you probably want are for the reminders. These are the email address and SMS (cell) number to send reminders to. You may have one or both. Or neither – it won’t break the process, it just won’t produce anything. Leave either one out if the client doesn’t want to be reminded that way.

For the email address, be sure the label for the email address is “Reminder” (as shown above). I set it up this way so you can still have your clients primary (Home or Work) email address in the contact, but not send reminders to it if it isn’t labeled reminder. Note that you may end up with two of the same email addresses for your client – one as the “Home” or “Work” email, and one as the “Reminder” email. Again, it is very important that you use the label “Reminder” exactly as such.

For the cell number to send SMS (text) messages to, use the client’s mobile phone number, and put it in the format required by the carrier as needed to send SMS via email. You can look up the carrier here, and find the required format here. It’s going to look something like “10digitphonenumber@txt.att.net”. It is very important to use this format, because you’re going to be sending the SMS through Gmail. I couldn’t find any free services that interface with Zapier that use standard SMS protocol. As shown in the images, set the label for this phone number/email address to “SMSemail”. Again, very important to use this exact label.

You are going to have to go through this process for every client you want to send reminders to.

Create a Google Drive (Docs) Spreadsheet

You can title your spreadsheet whatever you want. I recommend using the same row headers that I show here, so when you create the Zapier tasks later, it will be easier to follow along. Use “Appointment Title”, “Appointment Date/Time”, “SMS Number”, and “Email”, in that order. You can leave the rest of the spreadsheet blank.

Now you want to add a script to fill in the SMS Number and/or the Email. Go to Tools:Script Editor. On the next screen, click “Create a New Project”. Close the help screen, and paste the following into the Code window:

function updateContactInfo() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var lastrow = sheet.getLastRow(); var cell = sheet.getRange(lastrow,1); var client = cell.getValue().split(" ")[0]; var contacts = ContactsApp.getContactsByCustomField(client, 'Client Code'); for (var j in contacts) { var smsemail = contacts[j].getPhones('SMSemail'); if (smsemail[0]) { cell.offset(0,2).setValue(smsemail[0].getPhoneNumber()); } var email = contacts[j].getEmails('Reminder'); if (email) { for (var i in email) { cell.offset(0,3).setValue(email[i].getAddress()); } break; // stop after the first } } }

Go to File:Rename and name the project something clever. Now click on Resources:Current project’s triggers. In the window that opens, click on “No triggers set up. Click here to add one now.” “updateContactInfo” should be the default function, as well as “From spreadsheet”. Just change the third value to “on change”. That way the function will be run when Zapier (see below) adds a new row from your calender. Click “Save”.

You can close the project tab and return to the spreadsheet, and test it out. In a blank row, in the first column, insert “AA” or whatever your client code is for a client you have set up in Google Contacts, and after a few seconds, the SMS Number and/or Email fields should be filled out. Don’t worry about the date for now, Zapier will take care of this later. Note that this is just a test at this point, and no reminders will be sent out.

Go ahead and leave your spreadsheet open and move onto the next step: setting up Zapier.

Setting up Zapier

Zapier is a pretty nifty site. It allows linking of various services. Similar to If This Than That (IFTT), it has a streamlined interface for setting up and editing tasks, or “zaps” (sorry if I’m getting the terminology wrong here). We are going to set up three zaps for our hack to work. Start out by creating an account on Zapier, then we’ll move on to creating our zaps:

You can title your zaps whatever you want, but it might be easier for the purposes of following along to use the names I use here. First we’ll create “Add Certain client appointment to Zapier Reminders Spreadsheet” (wow, that’s a clumsy name). Note the Google Cal and Zapier’s version of the Google Docs icon. From your Zapier Dashboard, click “Make a New Zap”. For your Trigger app, select Google Calendar, and for the Trigger, choose Event Start. For the Action App, choose Google Docs, and for the Trigger, choose Create Spreadsheet Row.

Click Continue, and in Step 2 connect to your Google Calendar account. You will probably have to authorize the account. (Sorry, I can’t remember the exact process here, as it is different once you set it up the first time). Similarly, connect to your Google Docs account in Step 3. In Step 4, select your calendar, and change the time length to 24 hours (or whatever your want – I have it set to remind my clients 24 hours before the appointment). Leave the Search Term blank.

Here’s an important step: click on “add a custom filter”. For “Key”, select “summary”. This is the title of your Google Calendar event, the one that should contain the Client Code. For “Condition”, select “(Text) Contains”, and for “Value”, type in the Client Code. This is where things get a little tricky: you need to include a filter for every single client that you want to send reminders to. Use “+ Add OR Filter” (not AND Filter) to add a filter for each client. You can start out with one and add more later if you want.

Click “Continue” to go to Step 5. Here, select your “Zapier Reminders” spreadsheet, and select Sheet1 (which should be the only sheet available for that spreadsheet). Under “appointmenttitle” click “insert fields” and choose “summary”. Under “appointmentdatetime” click “insert fields” and choose “Event begins (pretty)”. Leave email and smsnumber blank – that’ll be filled in by your spreadsheet’s script, remember?

Click Continue, then click the test button. Testing won’t work, but that’s okay. Click continue to go on to step 7 and name your Zap. Again, I recommend “Add Certain client appointment to Zapier Reminders Spreadsheet”. But not really, because that’s embarrassing.

Great, your first zap is done! You can test it by setting up a new event on your Google Calendar, somewhere around 23 hours and 45 minutes from now, with a valid Client Code for the title, of course. You can then run the zap manually from the Zapier dashboard, or wait about 15 minutes for it to trigger. Then check your spreadsheet – you should see a new row with your event’s title, time/date, and, assuming the script did its job (because it was set to trigger on any changes to the spreadsheet), the reminder contact info filled out.

Setting up Sending Reminders through Gmail Zaps

Now to set up the zaps that will read the spreadsheet and send out reminders. They are both nearly identical, with just a few differences: which column of the spreadsheet we are looking at, either “SMS number” or “Email”. So I’ll describe how to make them both simultaneously, and you can read through this twice, changing the appropriate field when I say.

Create a new zap, and select “Google Docs”, trigger: “New Spreadsheet Row”, and Gmail, trigger: “Send Email”.

In steps 2 and 3, select your Google Docs account, and select your Gmail account. Authorization may be required.

In Step 4, select your “Zapier Reminders” spreadsheet and “Sheet1”. Now, here’s one of the differences between the two zaps. For the Email reminder zap, add a custom filter that checks to make sure the Email exists in the spreadsheet. Key should be “email”, and for the SMS Number reminder zap, Key should “smsnumber”. These show up with a “gsx$” prefix after you select them. Condition should be “Exists” for both zaps.

Continue to Step 5. The To: field is the only other difference between the zaps. For the Email reminder zap, insert field “email”, and for the SMS reminder zap, insert field “smsnumber”. Pretty straightforward, right?

Now fill out the reminder. Put in From Name and perhaps From Email as needed. Subject: “appointment reminder” or what have you. Body: “Hello. You have an appointment with Bob on”… and then insert field “appointmentdatetime”,and a period at the end.

Click Continue, and if you want to, test the zap. I recommend testing it with a sample client (“AA”) which has your email and cell phone set up as reminder contact info.

If it works, save the zap and your you’re set. The whole process should be automated every time you put a client appointment in and Zapier sees that the appointment is starting within 24 hours.

Zapier has a thing with editing your spreadsheet manually. If you delete any rows, it will freak out and send you an email letting you know what’s up. All you have to, according to the email, is turn off and turn back on the zaps (all three of them, in this case). You might have to do this once in a while, or just add a bajillion rows to your spreadsheet so it takes longer to fill up.

Well, that’s all I got. I hope this helps somebody. If you are inspired by this, do something nice for somebody this week, something that you wouldn’t ordinarily do.