Extend Cloud Functionality

Empower spreadsheet users with custom cloud blocks

Essential Tools for Cloud Platform

The Cloud SDK is a set of tools for Cloud Platform. It contains gcloud, gsutil, and bq, which you can use to access Google Compute Engine, Google Cloud Storage, Google BigQuery, and other products and services from the command-line. You can run these tools interactively or in your automated scripts.

What Are We Building?

As a city manager, we’re faced with monitoring the city for pollution and taking corrective measures. The old system forced us to view historical and real-time data in different systems, and we had no way of having a unified place to view, manage and act upon environmental city data.

In this tutorial we’ll look at how MintData allows us to:

  1. Create a unified pane of glass for both historical and real-time sensor data.
  2. Drill in on various parts of the city and sensors of interest.

City Planner shows on a map the joined data from external weather APIs and sensors located in the city for both real-time and historical perspectives.

This is what we’ll be building here:

Step 1: UI in Surface

Surface Visual Components Properties Preview

In MintData App Maker, you can create screens based on your design intent: what components to use, how to position/group them and what properties they should have.

Surface is an area where you work with visual components. To preview you User Interface click eye icon in the toolbar (Preview Mode).

To add a visual component click the Add button in the app toolbar. Like traditional drawing tools, you can simply click-and-drag out a component to your desired position.

To set properties just open Properties Panel and make your changes.

Step 2: App Logic in Spreadsheet

In MintData App Maker, you express logic of designed application in Spreadsheet: values, cells, functions and formulas work like in Excel or Google Spreadhssets, except special types of cells (will be described below).

2.1 Cell, Client Functions

cell / cell editing / client functions / formulas / IF

App logic can be expressed in many ways. Add labels and variables to display app states, use client functions (like IF) to add interactivity.

City Planner may be active and inactive, displayed data is real-time and historical.

2.2 Lazy Cells for actions

lazy cell simple action complex action PUT_CELL DO

For actions we use Lazy Cells -- cells that aren't recalculated automatically, only by event from user or system. Actions may be simple - just one step - or complex -- require many steps.

Toggling buttons between real-time and historical data we implement with PUT_CELL that rewrite cell value. On/Off of Application may be harder -- so we reserve some cells for that and use DO to execute them.

2.3 Link Components with Cells

linking properties linking events linking (onClick)

New we want UI elements and cells work together -- when pressing buttons, cells change and vise versa.

For that purpose we choose visual components and link events (onClick) and properties to cells. Note events can be linked only to Lazy Cells.

Look how it works:

Step 3: Data Work with Frames

3.1 Frames for real-time and historical data

frames nested frames cross sheet references FRAME

MintData uses Frames to work with data. On this step we get static data from other tab (FRAME function) and use IF to choose data source according to switcher. Frames that are stored in a cell named Nested Frames.

Copy sensors event to use in Tab:

Data frame for sensors
sensor_iddistrict_namelonlatsensor_valuesensor_typetimestamp
110North-west-122.46990437.78459373.35temperature1536318003263
309South-west-122.48106437.72553075.12temperature1536318003273
310South-west-122.47751237.71603373.25temperature1536318004277
1009South-east-122.43371437.71301776.99temperature1536318005280
310South-west-122.47751237.71603375.44temperature1536318006283
Data was copied to clipboard.

Look how it works:

3.2 Resulting data on Map

advanced binding (arrays nested) --- result: dots on map

To show sensors’ values on the map we should link columns onto map’s properties. Here we link latitude, longitude and size from nested frame of resulting data. Note that switching between real-time and historical data displays different data sets.

Step 4: Data from Cloud Functions

4.1 Date and time for cloud functions

date and time functions UI behaviour

To get real-time and historical data we need to know the time interval of data retrieval.

In this episode we create UI widget for time choosing and Spreadsheet logic for date and time calculation and formatting.

4.2 Cloud function for real-time data

cloud functions cloud function editor cloud components run/stop

MintData uses Cloud Functions to connect to external sources or perform backend data transformation or other operations.

Here we create cloud function named GET_REALTIME. We read sensors data from Kafka, then enhance sensors frame with sensors locations.

Copy credentials:

Look how it works:

4.3 Streaming, =*, +=*'

streaming in Spreadsheet

Now we can use cloud function in cells. Streaming Cell runs function continiously and is set by “=*”. After adding “+=*” the cell collects data. Note: we should enhance scale of linking to map, becase sensor_velues have different scales.

4.4 Cloud function for historical data

cloud functions cloud function editor cloud components run/stop

The same way we create GET_HISTORICAL function. It uses from to timestamps.

Copy credentials:

Look how it works:

Step 5: Data Filtering

5.1 UI: districts and sensors

Desc

Copy credentials:

Look how it works:

5.2 Filter by districts and sensors

Desc

Copy credentials:

Look how it works:

5.3 Last events, data for map

Desc

Copy credentials:

Look how it works:

5.4 Sensors on map

Desc

Copy credentials:

Look how it works:

Step 6: Data Visualization

6.1 Table

Desc

Look how it works:

6.2 Chart

Desc

Look how it works:

Final Application

Desc

Make sense of data with a spreadsheet from the future.