Using Workflow Builder to store data in Google Sheets

RingCentral Intelligent Workflow Builder enables you to easily create automated workflows for RingCentral’s voice, SMS, video, and team messaging events — with no programming required.

In this tutorial I’ll show you how you can track received SMS text messages based on the sender’s opt-in or opt-out preferences. We will be tracking that information within a Google Sheets file so that you can use the recorded data for future communications to only those responders that have agreed to receive your communications.

To get started, after you log into Workflow Builder you will be taken to the dashboard area that lists all your existing workflows. Click on “New Automation” in the top right of the display to start creating your new workflow. See figure 1.

Figure 1 — Creating a new workflow

Once you select the “New automation” button and you choose to “Build your own workflow” you will be asked to define a new trigger for your workflow. Select “SMS received” and click “Add”. You will then be taken to the Workflow Builder’s design area where you can further build out your workflow.

The next thing you want your workflow to do is to make a determination on a course of action based on the content of the incoming SMS message. So click on the “+” symbol and select “Add switch” from the popup menu. This option allows the workflows to take different paths of action depending on the content of the condition. In our situation we want to do two slightly different things to a Google Sheet when an SMS message is received. So we will add two conditions or branches to our Switch. First, though, we have to set up the condition itself, so after you added the Switch you should see a screen like Figure 2.

Figure 2 — Defining the Switch condition basis.

Here you can name your Switch condition for future reference [1] then pick the condition you want to watch for. Here [2] we are picking a triggering variable of the message text [3]. Then we add branches based on the Message text [4].

Click “Add branch” to establish the first branch of this Switch condition. You will see a screen like figure 3. Here we have already filled out the label for the branch [1] so that we can see what path of the logic this branch is taking. We have added two conditions [3] to react to, that of the Message text containing the string of either “START” or “SUBSCRIBE” [4]. We control the OR situation here by selecting “Any” from the “if…” area [2] Click on “Save” when you are done with that part of the setup.

Figure 3 — Adding a branch to the Switch condition

You will be taken back to the Switch condition screen where you can add another branch. This time do the exact same process except name the branch “Opt-out path” and create the condition of the message text containing the text of either “STOP” or “UNSUBSCRIBE”. You can have as many branches as you like but keep in mind it may get complicated if you define too many of them. When you save the overall Switch node by clicking “Confirm” you should see the design area looking like that of figure 4.

Figure 4 — Workflow design with Switch condition.

Now we just have to add Actions to the paths where we want events to happen based on the entered logic. We can leave the “False” path alone for now as this will be followed if the incoming SMS text does not contain either opt-in or opt-out language. Going down the “Opt-in” path we want to make a connection to a predefined Google Form that we will be using as a connection to a Google Sheet.

Setting up a Google Form for Use

So now we will jump over to Google Forms and create a new form with three fields in it. We want the phone number of the person that sent us the text message, we want the contents of the full text message so that we can double-check the context of the message, and we want a place to store the opt in or opt out keyword so that we can manipulate the Google Sheet by sorting or selecting certain rows later on. The form should look like the following with two short answer fields and one to hold a long answer, figure 5.

Figure 5 — Google Form used for storing SMS consent

You next need to link the Google Form to a Google Sheet. To do that select the “Responses” tab on the form [1] and click on [2] “Link to Sheets”. You can then create a new sheet or connect it to an
existing sheet.

When you connect the form to a sheet a new tab is created in the sheet with the field names that are existing in the form along with a timestamp column that will auto-fill each time a new row is added to the sheet.

There are a few settings that need to be checked on the Google Form that allows for data to be added to it without authorization. This allows us to add information to the form freely with our Workflow process and not need any “human” interactions.

Return to the Google Form if you have navigated away from it and select the Settings tab. Make sure “Collect email addresses” is turned off and disable “Requires Sign In — Limit to 1 response” both are in the “Responses” section. As a caution be sure not to share the form link with anyone that is not authorized to see the information that you are collecting here to keep the data safe and so that the setup does not change which would affect how the form collects the data.

The last step to this setup process with the Google Form is to generate a pre-filled URL link to the form that contains the field variables for use in our workflow. To do this click on the context menu of the form in the top right of the screen [1] and select “Get pre-filled link” [2].

A new tab should open with a “Get link” button at the bottom. Fill in the form with placeholder information like that shown in figure 6 and click on the “Get link” button.

Figure 6 — Generating the pre-filled links string

Next click “COPY LINK” at the bottom of the screen when it appears. You will have a string of text copied to your memory clipboard that looks like this when you paste it into a simple text editor. Figure 7 shows this.

NOTE: I have wrapped the text here for viewing purposes, this should be all one connected long string of text when we have it ready to add back into the Workflow.

Figure 7 — Generated link for auto-filling in the Google Form from within our Workflow

Now replace three sections of this string with connecting identifiers from Workflow as follows:

1. viewform to formResponse
2. phoneNumber to {trigger.sender.phoneNumber}
3. subject to {trigger.message}

The string should now look like this:

Be sure to restore this string to one long continuous text before you use it within the Workflow.

Adding Google Form connection to the Workflow

Getting back to our workflow we now want to use the above string within the “Opt-in path” branch. Click on the “+” under “Opt-in path”, select “Send HTTP request”, and click “Add”. Fill out the subsequent form as shown in figure 8 with the long URL string pasted into the URL field, select the GET method and choose “text/plain” for the content-type. Click on “Add” and you are done with the opt-in side of the branch. Be sure to save your workflow as you go along.

Figure 8 — Setting up the HTTP Request to connect to the Google Form

Duplicate this process for the Opt out and simply change the section of the URL that says Opt-in to Opt-out.

You should now have a Workflow design that looks like this with a node under both the Opt-in path and the Opt-out path:

Testing the Workflow

Now let’s test our workflow to see if the connection to the Google Form and Google Sheets is working. Click on the “Test” button at the top of the Workflow Builder design area and fill in the test information similar to that shown in Figure 9. The result of this test shows in Figure 10 with the row being inserted into the spreadsheet via the Google Form.

Figure 9 — Testing the workflow with opt-in language

Figure 10 — Test results with data stored in Google Sheets

That’s all there is to it! Be sure to save your workflow as you enhance it and test it further. One enhancement you might consider is on the Opt-in side you can send an SMS reply to the sender that you have received their Opt-in request and that you are glad they joined your list or some other friendly greeting. Optionally on the Opt-out side you could send the sender a goodbye message acknowledging their desire to be removed from your contact list, that you are sorry to see them go, and that they will no longer receive any SMS messages from you until they opt back in.

RingCentral Intelligent Workflow Builder is a great time saver for taking care of the many repeating mundane tasks that can take up so much valuable time. Connecting to outside data storage like this can also help to maintain accurate information that you collect from clients over time.

You can try out Workflow Builder yourself and play with its many potential scenarios by going here and logging in with your RingCentral account. Read more about the platform here.