This document explains how to connect Google Spreadsheets to corporate data with zero friction, while ensuring security and integrity. We begin by providing the business context that explains why this of strategic importance.
CIOs report that organizations spend significant time defending data analysis based on spreadsheets with copied corporate data. Such data is can be out of date, and worse can be incorrectly assembled.
The pressures are real - Business Users need the data to complete their assignments. So they bring pressure to bear on IT organizations to acquire the data they need, only to encounter the dreaded backlog. So, they copy. Nobody wins: not the IT organizations, not the Business Users, not the organization. What is needed is current data from the databases-of-record.
But now, solutions are emerging: making data available through RESTful APIs. Such data can be consumed by mobile apps, web apps, SOA services buses... and Spreadsheets. This provides always-current data to Business Users.
So the burden shifts back to IT: build RESTful servers for corporate data.
REST servers take time.. and lots of expertise
A conventional approach for building REST server is a formidable project. Actually, it is often a set of projects. Even using best-of-class frameworks (here we'll presume Java), it requires:
- Rest Listeners - code for each REST endpoint resource, perhaps using Jersey
- Persistence - Domain Objects encapsulating programmatic access, with services for persistence, perhaps using JPA
- Business Logic - code to assemble the REST response by analyzing requests, invoking Persistence Services, and converting results into JSON, perhaps using Jersey.
Each of these projects requires deep expertise, and significant time. Once built, changing them requires new projects to be scheduled. This does not result in an agile business.
Security is a requirement
IT is well aware that corporate data is a valuable resource, and there are important security requirements that govern who has access to what data. Such access is far more granular that "user X can access view Y". Fine grained security is required at the row and column instance level.
Espresso Logic makes it fast and easy to build a REST Server
enables you to build a complete RESTful server for SQL data, including logic and security. The process is largely point and click, so you can literally build a server in minutes. In this example, we'll be using a server pre-built by the sign-up process, but you can adopt this process to your own database:
Espresso consists of a REST server (provided as a service - nothing to install), and a Browser-based Logic Designer (shown below) for specifying your Resources, Logic and Security.
Using the Default Project
The signup process creates a project / database
which we'll use for this example. It is called Espresso Logic Demo (Customers, Orders, Items). At the completion of the signup process, you will be running the Logic Designer on this project.
Obtain the Project URL
The Project URL is required to run the Google Script. Here's how to obtain it.
The Logic Designer includes a Rest Lab
you can use to test your server without having to write a program. Note this shows your REST URL
(upper red box in the diagram shown below). You'll need this below. It will be a string like this:
Resource has already been defined, returning multi-table JSON like this
Aside - click
Send Request - this issues the command and shows the JSON response, like this:
Use the REST lab to obtain your Project URL. In the example above, it is:
Building a Project for your own database
While it is not necessary for this exercise, you can adapt this to your own databases, by following this simple procedure illustrated on the Espresso Home page.
- Just create a project, register your database... and your server is running.
- Your base tables are REST Resources, and you can create additional ones with a simple point and click. You do not need to build servers, build REST Listeners (e.g., Jersey), write SQL, convert it to JSON (e.g., Jackson), and so forth - it's all provided by Espresso.
- Use the Logic Designer to create Resources, Logic and Security.
User Authorization details are described below.
You must authorize users to access the data. If you are using Default Authentication, create a User and grant them access to Roles which control data access.
In defining Roles, be sure to authorize catalog access:
Provide User Login information
You will want to inform Users regarding their access, including their login id and password, the Project URL (determine as shown above for the default project), and optionally the Resource Names they will want to access.
Invoking RESTful services from a Google Spreadsheet
Once you have your REST API, you can import it to a spreadsheet as described below.
Install the Google Script
First, download this file, open it, and copy it to your cli
While editing your spreadsheet, invoke the menu Tools > Script Gallery, and select
REST access for Espresso.
- Create a new Google Spreadsheet
- Tools > Script Editor > Blank Project
- Download this file, open it an text editor, and copy / paste it into the script code editor (complete replace the default contents)
- File > Save, calling it
- Return to your original Spreadsheet, and Refresh
- The Espresso Logic menu should appear
Run the Script
Reload your spreadsheet, and a new menu item will appear: Espresso Logic > Get Data.
On first access, you will be asked to authorize the script. Once you've done so, you will be asked to login using the information provided by your administrator:
Select Resource, Target Sheet
Specify your parameters:
- Select a predefined Resource
- Optionally, specify a filter (e.g.,
name > "B")
- Select an existing sheet (described below), or create a new sheet and specify its name
After clicking the button, you should see a new sheet created with your results:
Using Existing Sheets
You will often want to refresh your data into an existing sheet - simply select it from the list. The system will ensure that your sheet's column headers match the data. This is flexible - you can drop columns or rearrange them.