Integration Guide: Cumulocity IoT & Google Spreadsheet with Zapier in a no-code approach

[Tamer_Mazen]

Use Case

Let’s start with a typical IoT use case: We have a device registered in Cumulocity IoT and it sends a Battery Measurement value every 5 seconds. Now we would like to have an automated way to export all new measurements to a spreadsheet stored on Google Docs for further analysis.

There are multiple ways how this can be achieved. For example you can implement such use case by implementing a custom microservice, which, on a predefined schedule, will pull data from Cumulocity IoT and add them to Spreadsheet on Google Docs. Also this microservice should keep track with the last added rows, so it will add only new rows. The downside of this approach: To implement such microservice you should have a technical/developer background and are experienced using Java or Python. Also you have to take care of microservice hosting cost, maintenance and support.

Now with minimum or no technical background, you can easily implement the same use case in 2 hours. All you have to know is which Cumulocity IoT API endpoint you shall call to pull the required data.

The following sections will illustrate how to implement this use case in a no-code approach, using the Zapier Platform.

Zapier

Before we dig into the implementation let’s get an overview about Zapier.

Zapier Interfaces is a no-code, automation-powered app builder that lets you create your own personalized solution —from forms and landing pages to trackers and Kanban views—all in one spot.

You turn to Zapier to connect your favorite tools and automate work behind the scenes. Zapier Interfaces brings your work center stage by letting you create your own front-end web app—the thing you actually interact with—to go along with everything you’re automating on the back end.

You don’t have to ditch your existing apps, either. Interfaces is powered by Zapier automated workflows called Zaps. That means you don’t have to hop between various apps: You can connect your favorite tools and automatically update an interface with the latest info.

Zapier offers the following:

• Build and launch fast: You don’t need to be a technical wizard or a design pro to create a custom app that looks good. Interfaces’ no-code builder lets you easily create your own web app with navigation, multiple pages, and customizable interactive components.

• Automate and streamline work with your existing tools: Interfaces has its own Zapier integration, so you can connect it to the thousands of apps Zapier already supports (currently Zapier supports 6500+ application).

• Create an interface for internal or external use: You can create customer-facing interfaces, password-protected client portals, or private custom apps for yourself or your team. Your Zaps have separate permissions from your interfaces, so you control who can access your automated workflows.

• Workflows are front-and-center, and you’re in control: Since Interfaces is powered by Zaps, you can easily access your workflows from the Interfaces builder or your Zap dashboard. If something goes wrong, you’ll know—and can fix it right away.

The scope of this article is to illustrate how you can use Zapier Interfaces to export data from Cumulocity IoT using Cumulocity Rest APIs, and import them to Google Spread Sheet.

Step-by-Step Guide

Prerequisites

This integration guide is based on the following assumptions:

• Cumulocity IoT Release 10.18
• Device registered on Cumulocity IoT and sending Measurements to it.
• Account on Zapier
• Account on Google Docs

Device’s Measurement Structure

The registered device in Cumulocity IoT will send a new Battery measurement value every 5 seconds to the platform. Each measurement will have the following custom fragment for Battery Value .

"c8y_BatteryMeasurement": { 
      "B": { 
              "unit": "mAH", 
              "value": 1200 
           } 
 } 

Google Spreadsheet Integration in Zapier

1. Log in to your Google Docs account.

2. Create blank spreadsheet with the following name “Device_Battery_Values”.

3. Set sheet’s header as following:

A	B	C	D	E	F
ID	Type	Source	Time	Value	Unit

Zapier Zap

1. Log in to your Zapier account.

2. Click Create Zap from left menu. Zap is a workflow with multiple actions.
   

3. Set Zap name to Export Device Data.

   

4. Click on Trigger block, and search for Webhook by Zapier.
   

   

5. Select Webhook by Zapier from the results.

6. The popup window will be closed, and Zapier will set Triger type to Webhooks by Zapier.
   

   

7. On the right menu set Trigger properties as following:

   a. Choose Event. Type & Select Retrieve Poll.

   

   b. Click the Continue button.

   

   c. In Trigger Section set the following Cumulocity API Information:

   • URL: Is Cumulocity IoT API URL to retrieve device measurements starting from a certain date in a descending order,
     https://<Tenant URL>/measurement/measurements?source=<Device ID>&revert=true&dateFrom=<From Date>.
     Replace Tenant URL, Device ID and From Date with your suitable data.

   • Key: This is the key of the JSON element, which is part of the API response, and it holds a list of measurements. In our case, it should be measurements.

   • Deduplication Key: Is the unique ID, which identifies a single measurement, and in our use case it should be ID.

   • Basic Auth: Is a user credential, who has the privilege to call the above-mentioned Cumulocity IoT API, and it should be in the following format

     <Tenant ID>/<User Name>|<Password>

   • Headers: Is the Request Header, set
     Key to Accept
     Value to */*

   

   d. Click the Continue button.

   e. Expand the Test Tab and click the Test trigger button.

   f. If the above configuration is correct, you should see the API response as follows:

   

   g. Select any record and click the Continue with selected record button.

8. To parse the list of measurements from Cumulocity IoT API response, you will have to loop over the list, and that’s why we need to add Loop Action.

9. Once you click the Continue with the selected record button in the previous step, a new popup window shows up, to select the next action in the Zap. In search type loop, and select Looping by Zapier.

   

10. On the right-side menu select Create Loop From Line Items from the Event dropdown and click the Continue button.

    

11. In the Action section, you will start to map individual measurement’s properties to the item’s properties.
    Set the name of item properties to match header column names in the spreadsheet, which is created in Google Spreadsheet section.

    

12. Click the Continue button.

13. In the Test Tab click the Test button. If everything is configured correctly, you should have something like the following:

    

14. In the middle screen you should have Zap looks like:

    

15. Click + to add new step after the loop step.

16. A new popup window will show up, to select the next step, key in Google Sheets.

    

17. Select the Google Sheets action.

18. A new step will be added to the Zap.

19. Click the new action, and the right-side menu will be opened.

20. In the right-side menu, and in App & event tab select Create Multiple Spreadsheet Rows.

    

21. Click the Continue button.

22. In the Account Tab choose your Google account.

23. Click the Continue button.

24. In the Action tab do the following:
    a. Select Drive from the list of Google drives in your Google account.

    b.Select Spreadsheet that you have created in Google Spreadsheet.

    c. Select a worksheet, for example Sheet 1.

    d. In the Rows section, select properties from Create Loop From Line Items in Zapier.

    

25. Click the Continue button.

26. In the Test Section, click the Test button. If everything is configured correctly, a new record should be added to the Google Spreadsheet.

    

27. Click the Publish button on the top right side, to publish the Zap.

28. The final Zap should look like:

    

29. In Zapier Dashboard turn the Zap On, and it will start to pull data from Cumulocity IoT and add the new rows to Google Spreadsheet.

    

Congratulations! You’ve successfully created a Cumulocity IoT Google Spreadsheet integration!

Zapier Security Limitation

During our investigation in Zapier, we discovered that some Zapier triggers like webhook don’t support any type of authentication, which means anyone who knows the Zap URL can call it without any verification.

However, you can overcome this limitation by customizing the integration with Zapier using Zapier Platform.

[rameshkt (Ramesh Kumar Tadapaneni)]

Interesting one .Thanks for posting .