Writing your first App

In this tutorial you’ll learn how to create a new Gridarrow App using the Python editor in our web console.

As a data source for the App we’ll use a simple API that simulates a network of Internet of Things sensors. There’s a feed of temperature, humidity, radiation and brightness for 10 appliances updated every 5 seconds, accessible via HTTP. We’ll convert that data to an automatically-updated table in Excel with the metrics as columns, and the names of the sensors as rows.

Assumptions

This tutorial assumes that you are already familiar with Python. There are many excellent resources online if you aren’t, Udacity’s course is particularly highly recommended, as is the Codeacademy course.

Creating an App

Open the Gridarrow console, log in and click the + icon in the lower right corner. On the New App screen, specify a name, display name and description for the app.

Since we’re building an IoT App, we’ll give it the name Sensor Data and the description This data feed provides information from a network of artificial sensors.

Creating a new App in Gridarrow Console

Once the App is created you’ll see the editor screen. Right now it contains the minimum Python code required for the App to start up and to stream the current timestamp into a single Excel cell every 10 seconds.

New Gridarrow App view

Let’s turn it into a proper App which will download data from our demo API and stream it into an Excel table.

App source code

A typical Gridarrow App will consist of 3 sections:

  • Initialization - where we set up all the necessary objects
  • Data gathering - where we get the data we’re interested in and process it as required
  • Data sending - where we send the processed data into Excel spreadsheet

In our example we’d like to download fresh data from our Internet of Things sensors API every 5 seconds and send it to Excel.

Let’s go step-by-step through the code explaining how to do it.

Initialization

12345
from gridarrow import Arrow
import requests
import os

arrow = Arrow()
  • We need to import the Arrow class from the Gridarrow library. This class is the main integration point between the application code and the Gridarrow infrastructure. It’s full API and capabilities are described in the API reference.

  • We also import the requests library to be able to easily download HTTP data and os library to be able to read environment variables.

1234567
LIVE_DATA = "https://demo.gridarrow.io/sensor-data/live?key={}".format(os.environ['API_KEY'])

@arrow.initialize
def initialize():
    grid = arrow.grid("live", description="Live data from IoT sensors",
                  rows=10, columns=["humidity", "temp", "radiation", "brightness"])
    return grid
  • Next, we define the LIVE_DATA URL from which we’ll download the sensors data. The key param is used for authentication to our data endpoint. We’ll set its value using API_KEY environment variable.

You can read more about configuring your Apps and handling sensitive data in the handling sensitive data section

  • We use the function annotated with @arrow.initialize to create a grid with the name live. This is what will appear in the list of grids in the Excel Add-In.
  • We’d like the grid to have 10 rows - one for each sensor
  • We provide the grid with the column headings ["humidity", "temp", "radiation", "brightness"]

As we shall see in the next section, the return value of the @arrow.initialize function is passed as the argument to the @arrow.on_timer, @arrow.on_schedule, @arrow.on_start, and @arrow.webhook functions.

Data gathering

12345678
@arrow.on_timer(period=5)
def on_timer(grid):
    response = requests.get(LIVE_DATA)
    if response.status_code != requests.codes.ok:
        arrow.log.warning(response.text)
        return

    data = response.json()
  • We want to download a fresh data set every 5 seconds so we use the @arrow.on-timer decorator to make the on_timer() function execute periodically.
  • The grid argument is the same grid that was created in the initialize function.
  • We grab data from the API using the requests.get() function from the Requests library
  • When we have a response we first check for errors, logging them with arrow.log.warning() if found.
  • Finally we convert the JSON data set contained in the response to a dict for easier processing.

Data sending

12
    for i, sensor in enumerate(data):
        grid[i, grid.columns] = [sensor['data'][c] for c in grid.columns]

Our data is now ready to be sent to Excel, so we enumerate through our data dict and update the grid using the square brackets [] operator. As you can see from the API docs there’s a wide range of possibilities for updating the data in a grid, but the simplest way to understand this operation is as following:

grid[row_reference, column_reference] = value

Multiple rows and columns can be set at the same time, as long as the value being passed matches the dimensions implied by the row and column references.

In our example we’re using an integer row reference i and the property grid.columns as the column reference. The list comprehension [sensor['data'][c] for c in grid.columns] converts the sensor dict into a list in the same order as grid.columns.

Here’s an example of how the expression might look as it’s evaluated:

grid[3, ["humidity", "temp", "radiation", "brightness"]] = [68.564, 28.9, 99.39, 199.05]

Complete code

The complete source code of your App should now look like this:

 1 2 3 4 5 6 7 8 910111213141516171819202122232425
from gridarrow import Arrow
import requests
import os

arrow = Arrow()

LIVE_DATA = "https://demo.gridarrow.io/sensor-data/live?key={}".format(os.environ['API_KEY'])

@arrow.initialize
def init():
    grid = arrow.grid("live", description="Live data from IoT sensors",
                      rows=10, columns=["humidity", "temp", "radiation", "brightness"])
    return grid

@arrow.on_timer(period=5)
def on_timer(grid):
    response = requests.get(LIVE_DATA)
    if response.status_code != requests.codes.ok:
        arrow.log.warning(response.text)
        return

    data = response.json()

    for i, sensor in enumerate(data):
        grid[i, grid.columns] = [sensor['data'][c] for c in grid.columns]

Adding dependencies

Because we use the Python Requests library to download the data in our App, we need to make it available to the Python interpreter running on the Gridarrow server.

To add requests to your app dependencies, switch the editor to the requirements.txt file by clicking on the file menu and make sure its content looks like:

Editing requirements of Gridarrow App

Full specification of the requirements.txt file is available in pip user guide.

Handling sensitive data

Credentials for connecting to APIs or databases should never be stored in the source code. Gridarrow includes a Secrets mechanism where sensitive data can be added to the system securely. The data is persisted in encrypted storage and made available to the App as it is run in the form of environment variables.

To make the API_KEY environment variable available to our App, go to Secrets tab, click the + icon in the lower right, and add the key API_KEY with the value:

  • 8c1878a0-fbe2-4770-a561-6816a73095a4

Click on Save button to save the secret.

Remember that secrets are not available inside the App until you restart it.

Adding secrets to Gridarrow App

Running the App

Click on Save and restart to start the App. In few seconds it should be up and available in your Excel spreadsheet.

You can connect to it and add the data grid to a new spredsheet by clicking on add to new spreadsheet button.

Connecting to Gridarrow App in Excel

After adding a little bit of conditional formatting and a few charts, you might end up with something like this:

Real-time Excel dashboard with different charts

What’s next