Sending Group Notifications with Google Sheets and Node-RED
Tags: googlesheets, node, nodered, tutorial
Published: 2020-03-06 13:28:10

---
title: Sending Group Notifications with Google Sheets and Node-RED
published: true
date: 2020-03-06 13:16:53 UTC
tags: googlesheets, node, nodered, tutorial
canonical_url: https://www.nexmo.com/blog/2020/03/06/sms-notifications-google-sheets-nodered-dr
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/q5p0uaua1fhhrtaq13zq.png
---

Ever got a phone call at 7:59 AM telling you your kid’s school is closed? It was handy, as you were only a couple miles away – on your way home, after dropping them off.

The announcement process in most schools is still manual nowadays, which works in most cases. When something unexpected happens though, like the school is snowed in, a handful of people scramble to call hundreds of parents. You might get the notification in time, or you could be part of the lucky bunch that ended up in the last batch at 7:59.

In this tutorial we’re going to build a Node-RED flow that programmatically sends out notifications to a list of contacts, using Google Sheets and the Nexmo SMS API.

Follow along and pitch it to the principal? Saves you being stuck in traffic on a snow day.

## Prerequisites

Before getting started, you’ll need a few things:

- A [Node-RED](https://nodered.org/docs/getting-started/installation) installation, be it a hosted version or on your machine
- A [Google account](https://myaccount.google.com/)
- A Nexmo account — [create one for free](https://dashboard.nexmo.com/sign-up) if you haven’t already
- A way to expose your server to the internet. This either means you’re running a hosted version of Node-RED or using a tunneling service like [ngrok](https://flows.nodered.org/node/node-red-contrib-ngrok) – get up to speed with this [Getting Started with Ngrok in Node-RED](https://dev.to/nexmo/getting-started-with-ngrok-in-node-red-4cn7) tutorial

## Setting Up Your Editor

Once you open your Node-RED Editor, make sure you have the following packages installed:

- [node-red-contrib-google-sheets](http://flows.nodered.org/node/node-red-contrib-google-sheets)
- [node-red-contrib-nexmo](https://flows.nodered.org/node/node-red-contrib-nexmo)

To do this, click on the hamburger menu, select _Manage Palette_. Check for already installed packages under _Nodes_, and get new ones from the _Install_ tab.

When you’re done, make sure to restart Node-RED and you’re good to go!

## Configuring Your Google Account

To interact with the _Google Sheets API_, you’ll need to use a service account – an identity that an instance can use to run API requests on your behalf. It will be used to identify apps running on your instance to other Google Cloud services.

In this case, your flow that reads a Google Sheet must first authenticate to the Google Sheets API.

You’ll have to create a service account and grant it access to the Google Sheets API. Next, update your app to pass the service account credentials to the Google Sheets API. This way, your flow authenticates seamlessly to the API without embedding any secret keys or user credentials.

### Step 1: Create a New Service Account

We’ll start by creating a new project on the [Service Accounts Page](https://console.cloud.google.com/projectselector2/iam-admin/serviceaccounts?_ga=2.184919274.-272657095.1578084478&supportedpurview=project) of the Google Cloud Platform. Click on _CREATE_ to get started.

![google service accounts create project](https://www.nexmo.com/wp-content/uploads/2020/03/google-service-accounts-create-project.png)

Next, give your project a name, either select an organization or leave it blank, then press _CREATE_.

![google service accounts name project](https://www.nexmo.com/wp-content/uploads/2020/03/google-service-accounts-name-project.png)

You’ll shortly see a notification pop up that your project has been created.

![google service accounts project created](https://www.nexmo.com/wp-content/uploads/2020/03/google-service-accounts-project-created.png)

Now that you have a project, let’s add a service account to it!

![google create service account](https://www.nexmo.com/wp-content/uploads/2020/03/google-create-service-account.png)

![google name service account](https://www.nexmo.com/wp-content/uploads/2020/03/google-name-service-account.png)

Next, you’ll need to create a key that you’ll use to authenticate with the GSheet node in your flow. Click on _Create Key_, then select _JSON_ as a key type. Save this file when prompted – keep it handy as you’ll need it soon, then hit _Done_.

![google service accounts create key](https://www.nexmo.com/wp-content/uploads/2020/03/google-service-accounts-create-key.png)

### Step 2: Enable Google Sheets API for Your Project

From the hamburger menu select _APIs and Services_ -> _Dashboard_, then click the _ENABLE APIS AND SERVICES_ button. Look for the _Google Sheets API_ in the API Library, open it and click _Enable_.

![google sheets enable api for project](https://www.nexmo.com/wp-content/uploads/2020/03/google-sheets-enable-api-for-project.gif)

### Step 3: Sharing Google Sheets with Your Service Account

Go to the _Service Accounts_ page and make a note of the email address associated with the service account you’ve just created. You’ll need to use this email address to share your spreadsheets with the Service Account.

![google service accouns email](https://www.nexmo.com/wp-content/uploads/2020/03/google-service-accouns-email.png)

## Sending Group Notifications with Google Sheets and Node-RED

### Create a Spreadsheet

In case you don’t have a Google Sheet ready, go ahead and create one now.

Google Sheets use a cell-matrix system, where each column can be identified with a letter (starting with A as the first column) and rows are numbered (1 being the first row). In case you’d like to select the second element of the first row, this would be **B1**.

You can also select ranges of cells by using the **TOP\_LEFTMOST\_CELL:BOTTOM\_RIGHTMOST\_CELL** notation. For example, to select the second and third element of rows 1-5, use **B1:C5**.

After creating a spreadsheet you’ll see a _Sheet1_ tab at the bottom of the screen, which is the worksheet you’re currently one. You can rename it or add more worksheets to your spreadsheet.

For this tutorial, I’m using one worksheet with 4 columns: Name, Surname, Phone, and Email – you’ll need at least 2 rows of data to follow along.  
Make sure you add a phone number you have access to, so that you can test your flow later on.

Once your spreadsheet is ready, it’s time to share it with your Service Account.

![google sheets share google sheet](https://www.nexmo.com/wp-content/uploads/2020/03/gsheets-share-google-sheet.png)

### Getting the Data from Your Google Sheet

Start your flow by adding a **GSheet** node to your workspace. Configure this node to pull in the data from your Google Sheet by filling out the following fields accordingly:

| PARAMETERS | DESCRIPTION |
| --- | --- |
| **Creds** | Press the edit button to provide your Service Account key. Remember the JSON file you downloaded earlier? Copy and paste this JSON key in the text field. |
| **Method** | Select _Get Cells_ from the drop-down menu. This will grab the data from the Google Sheet and pull it into your flow. |
| **SpreadsheetID** | You can figure out your spreadsheet ID from the URL of your Google Sheet. For example, if the URL is https://docs.google.com/spreadsheets/d/1mmXhj40aeSooxmtku3ma4auLyrHhO8xCSQsklZ1_BU/edit#gid=0, the SpreadsheetID will be the string found in between `d/` and `/edit`: _1mmXhj40aeSooxmtku3ma4auLyrHhO8xCSQsklZ1\_BU_. Have a look at your spreadsheet URL and find your SpreadSheetID. Then paste this string in the **SpreadSheetID** field. |
| **Cells** | Select the cells where your data is located on the spreadsheet. In the example below, this value will be: `Sheet1!A2:D30`, as the data is found on the worksheet named “Sheet1”, in columns A-D on rows 2-30. Note that we’re not including the table headers. |

Once you’re done editing the **GSheet** node, press _Done_.

![google sheets node setup](https://www.nexmo.com/wp-content/uploads/2020/03/google-sheets-node-setup.gif)

Next, let’s have a look at the data we’re getting from the Google Sheets API.

Add an **inject** and a **debug** node to your workspace and connect them to the **GSheet** one. Hit _Deploy_, click on the **inject** node’s button, then have a look at your debug sidebar.

![google sheets node](https://www.nexmo.com/wp-content/uploads/2020/03/google-sheet-node.png)

You’ll notice that the response in **msg.payload** is an array of arrays, each of these arrays having 4 elements – one line worth of data.

#### Split the msg.payload Array

This data structure isn’t ideal for further processing, so let’s split the array of arrays into individual arrays.

Fortunately, there is a default node already in your palette that will do the heavy lifting for you.

Find the **split** node under _sequence_ in your node palette o the left side of your screen. Add it to your workspace, connect it after the **GSheet** node, follow with a **debug** , then press _Deploy_ and run your flow again.

Glance over to the debug sidebar and notice the response coming through as a sequence of individual arrays. This way we can process them one at a time, as they are coming in.

![google sheets sms split node](https://www.nexmo.com/wp-content/uploads/2020/03/gsheets-sms-split-node.png)

#### Set Delay

In most cases, you wouldn’t want to send out messages at this speed, be it via email, SMS or the channel of your choice.

For example, the Nexmo SMS API has a [throughput limit](https://help.nexmo.com/hc/en-us/articles/203993598-What-is-the-Throughput-Limit-for-Outbound-SMS-) for outbound SMS – all API keys are set with 30 API request per second throughput restriction by default. On top of this, there are also restrictions when sending from certain numbers, so you might be restricted to 1 SMS per second.

To make sure you’re not reaching the throughput limits, it’s a good idea to set a delay on each array coming through **msg.payload**.

To do this, find the **delay** node in the _function_ section of your node palette, and connect it after the **split** node. Double-click on it to open up the node properties and set the delay to 1 second – this should cover most use cases, but feel free to adjust it as needed.

### Preparing the Message

At this point, we have all the information we need about the recipients, so let’s move on to the message!

Although you could send the same message to all recipients, it’s always a good idea to make it a little more personal. Getting the bad news is frustrating enough, and a bad user experience won’t make it any better.

Adding a bit of templating won’t only give your message a personal touch, it will also make it appear more professional.

Add a **template** node after **delay**. Double-click on it, set _Property_ to **msg.text** and get creative with your message in the text field!

This text field supports [Mustache templating](https://mustache.github.io/), so you could start with greeting the recipient using their name: `{{payload.0}}`. This expression references the first element of the **msg.payload** array, the recipient’s first name.

![google sheets sms template node](https://www.nexmo.com/wp-content/uploads/2020/03/gsheets-sms-template-node.png)

When you’re done editing, press _Done_, then _Deploy_.

### Sending SMS Notifications

There are many channels available to deliver your notifications, but in bad weather conditions SMS might be your best bet, so we’ll start with this option.

To send the SMS messages, we’ll use the Nexmo [SMS API](https://developer.nexmo.com/api/sms).

Scroll down to the _nexmo_ section of your node palette and add **sendsms** to your workspace, connected after the **template** node.

Set up this node by double-clicking on it and filling in the parameters below. You’ll find _API KEY_ and _API SECRET_ by clicking on the edit button next to _Nexmo Credentials_.

| KEY | DESCRIPTION |
| --- | --- |
| **API KEY** | Your Nexmo API key, shown in your [account overview](https://dashboard.nexmo.com/getting-started-guide). |
| **API SECRET** | Your Nexmo API secret, shown in your [account overview](https://dashboard.nexmo.com/getting-started-guide). |
| **TO** | The number you are sending the SMS to, `{{msg.payload.2}}` in this case. |
| **FROM** | The number or text shown on a handset when it displays your message. You can also set a custom alphanumeric FROM value if this feature is [supported in your country](https://help.nexmo.com/hc/en-us/articles/115011781468). |
| **TEXT** | The content of your message. Use `{{msg.text}}` to reference the templated message you’ve created earlier. |

Make sure _Unicode_ is ticked to keep the formatting of your message, then press _Done_ and _Deploy_.

![google sheets send sms node setup](https://www.nexmo.com/wp-content/uploads/2020/03/gsheets-sendsms-node-setup.png)

Run your flow again and see your templated messages appear in the debug sidebar.

![google sheets templated sms in debug](https://www.nexmo.com/wp-content/uploads/2020/03/gsheets-templated-sms-debug.png)

### Delivery Receipts

When you make a successful request to the SMS API, it returns an array of message objects. Ideally, each of these has a status of 0, indicating that your message has successfully been scheduled for sending. These are the response objects that you’ve just seen in the debug area.

While inspecting this output is quite helpful in determining what the Nexmo SMS API did, there is no guarantee that the message reached the recipient’s handset. Not exactly what you want to hear while sending out snow day alerts, is it?

Once the message reaches its destination, the carrier returns a [**Delivery Receipt**](https://developer.nexmo.com/messaging/sms/guides/delivery-receipts) to Nexmo – so don’t panic! All you need to do is set up a webhook endpoint that Nexmo can forward these **Delivery Receipts** to.

Connect a **http** input node to a **http response** node, as well as to a **debug** node, then fill in the _URL_ field with `/receipt` in the **http** input node.

Next, you’ll have to let the Nexmo SMS API know where it should forward the delivery receipts. Go to your [API settings](https://dashboard.nexmo.com/settings) in the **Default SMS Setting** section.  
Set the default webhook URL for delivery receipts to `YOUR_URL/receipt`, then _Save changes_.

![default sms settings nexmo dashboard](https://www.nexmo.com/wp-content/uploads/2020/03/default-sms-settings-nexmo-dashboard.png)

Now you can rest assured that your snow day notifications have indeed reached everyone on your list! Don’t take my word for it though, head over to the debug sidebar and read through your delivery receipts.

![google sheets sms event webhook](https://www.nexmo.com/wp-content/uploads/2020/03/gsheets-sms-event-webhook.png)

## Where Next?

### Extra Credit: Write Your Delivery Receipts to the Google Sheet

Although the debug sidebar gives you all the insight you’ll ever need, sometimes it’s easier to grasp the result if the data is presented in a more organized fashion.

In this section, we’ll look into writing your delivery receipts back to the same spreadsheet, on a different worksheet(tab).

#### Pick Your Data

The delivery receipts will contain the following properties of the **msg.payload** object:

| PROPERTY | DESCRIPTION |
| --- | --- |
| msisdn | The number the message was sent to. |
| to | Your Nexmo number or the SenderID you’ve set when sending the SMS. |
| network-code | The Mobile Country Code Mobile Network Code (MCCMNC) of the carrier the destination phone number is registered with. |
| messageId | The Nexmo ID for this message. |
| price | The cost of this message. |
| status | Will be one of: _delivered_, _expired_, _failed_, _rejected_, _accepted_, _buffered_ or _unknown_, based on where the message is in the delivery process. |
| scts | When the delivery receipt was received from the carrier in _YYMMDDHHMM_ format. For example, 2001011400 is at 2020-01-01 14:00 |
| err-code | The status of the request. Will be a non 0 value in case of an error. See the [Delivery Receipt documentation](https://developer.nexmo.com/messaging/sms/guides/delivery-receipts#dlr-error-codes) for more details. |
| api-key | Your Nexmo API key. |
| message-timestamp | The time when Nexmo started to push this Delivery Receipt to your webhook endpoint. |

Decide on which of these parameters matter to you, then using a **change** node, set **msg.payload** to an array of the respective properties.

For example, I’m interested in the timestamp, recipient’s number, status, error code and message ID, so I’ll set **msg.payload** to the following _expression_:

```
[payload.`message-timestamp`, payload.msisdn, payload.status, payload.`err-code`, payload.messageId]
```

Connect this **change** node into the **/receipt** webhook, then follow with a **GSheet** node.

#### Write Your Data to the Google Sheet

Add another worksheet(tab) to your Google Sheet and make a note of its name – will be “Sheet2” by default.

Next, head back over to your Node-RED editor and open up the **GSheet** node properties. Select your credentials from the _creds_ drop-down, select **Append Row** as a _Method_, fill in your _SpreadsheetID_, then specify the cell range where you’d like the data to be written. In my case this will be _Sheet2!A:E_, as I’d like the data to be spread accross columns A-E on worksheet “Sheet2”.

![google sheets append row setup](https://www.nexmo.com/wp-content/uploads/2020/03/google-sheets-append-row-setup.png)

When you’re ready, click _Done_ and _Deploy_, then run your flow again.

![🎉](https://s.w.org/images/core/emoji/11.2.0/72x72/1f389.png)Congratulations! Your Delivery Receipts have now been logged onto the second worksheet of your spreadsheet. Head over to your Google Sheet and check them out!

![google sheets delivery receipts logged](https://www.nexmo.com/wp-content/uploads/2020/03/google-sheets-delivery-receipts-logged.png)

### Further Hack Ideas

Tired of having to open up your Node-RED Editor to start your flow? Experiment with different ways to kick it off!

- Try replacing the **inject** node with an [inbound SMS](https://dev.to/nexmo/how-to-receive-sms-messages-with-node-red-55ll) webhook. Send an SMS to your Nexmo number to achieve your task!
- [Inbound calls](https://dev.to/nexmo/how-to-receive-phone-calls-with-node-red-3ihc) would be another great option! You could even build on it and set up an [Interactive Voice Response Menu](https://dev.to/nexmo/how-to-build-an-ivr-using-node-red-and-the-nexmo-apis-4mji)
- Set up a user interface using the [dashboard nodes](https://flows.nodered.org/node/node-red-dashboard)

### Resources

- [SMS API Reference](https://developer.nexmo.com/api/sms)
- [Getting Started with Ngrok in Node-RED](https://dev.to/nexmo/getting-started-with-ngrok-in-node-red-4cn7)
- Get a better understanding of [delivery receipts](https://developer.nexmo.com/messaging/sms/guides/delivery-receipts)
- [Mustache templating](https://mustache.github.io/)
- [JSONata Docs](http://docs.jsonata.org/overview.html)
- [JSONata Exerciser](https://try.jsonata.org/)

### Try Another Tutorial

- [How to Build an IVR using Node-RED and the Nexmo APIs](https://dev.to/nexmo/how-to-build-an-ivr-using-node-red-and-the-nexmo-apis-4mji)
- [Build Your Own Voicemail With Node-RED and the Nexmo Voice API](https://dev.to/nexmo/build-your-own-voicemail-with-node-red-and-the-nexmo-voice-api-617)
- [Forward a Call via a Voice Proxy with Node-RED](https://www.nexmo.com/blog/2019/10/17/forward-call-via-voice-proxy-node-red-dr)
- [Build a Conference Call with Node-RED](https://dev.to/nexmo/build-a-conference-call-with-node-red-5c45)
- [Verify Phone Numbers with Node-RED](https://dev.to/nexmo/verify-phone-numbers-with-node-red-39b5)
- [How to Stream Audio into a Call with Node-RED](https://dev.to/nexmo/how-to-stream-audio-into-a-call-with-node-red-1c1m)
- [How to Make Text-to-Speech Phone Calls with Node-RED](https://dev.to/nexmo/how-to-make-text-to-speech-phone-calls-with-node-red-2nfj)
- [How to Receive Phone Calls with Node-RED](https://dev.to/nexmo/how-to-receive-phone-calls-with-node-red-3ihc)
- [How to Send SMS Messages with Node-RED](https://dev.to/nexmo/how-to-send-sms-messages-with-node-red-2l2o)
- [How to Receive SMS Messages with Node-RED](https://dev.to/nexmo/how-to-receive-sms-messages-with-node-red-55ll)