Google Spreadsheet Example

You can connect Google Spreadsheets to corporate data with zero friction, while ensuring security and integrity. CIOs report that organizations spend significant time defending data analysis based on spreadsheets with copied corporate data. Such data 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. The current data from the databases-of-record is needed.

But now, solutions are emerging: making data available through RESTful APIs. Mobile apps, web apps, SOA services buses can consume such data... 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.

Build a REST Server using API Creator

You can build a complete RESTful server for SQL data, including logic and security, using Live API Creator. 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.

Live API Creator consists of a REST server (provided as a service. REST server installation is not required), and a browser-based API Creator for specifying your Resources, Logic and Security.

Use the Default Project

The signup process creates a project / database which we'll use for this example. It is called Demo (Customers, Orders, Items). At the completion of the signup process, you will be running API Creator on this project.

Obtain the Project URL

The Project URL is required to run the Google Script. API Creator includes a Rest Lab you can use to test your server without having to write a program. This shows your REST URL (upper red box in the following diagram), which you need. It will be a string like this:

https://server.acme.com/rest/val2/demo/v1/Customers

The Customers 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:

https://eval.acme.com/rest/val/demo/v1

Build a Project for your own database

While it is not necessary for this exercise, you can adapt this to your own databases.

  1. Obtain a copy of CA Live API Creator.
  2. Just create a project, connect to your database... and your server is running.
  3. 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 API Creator.
  4. Create ResourcesLogic and Security using API Creator.
User Authorization details:

Authorize Users

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, 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.

Invoke RESTful services from a Google Spreadsheet

Understanding the Script

This demonstration script is a good illustration of how to access RESTful APIs from Google Script.

See here for a code walk-through.
After you have your REST API, you can import it to a spreadsheet. Alternatively, you can use this function.

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 API Creator.
  1. Create a new Google Spreadsheet. Tools, Script Editor, Blank Project.
  2. Download this file, open it an text editor, and copy/paste it into the script code editor (complete replace the default contents).
  3. File, Save, calling it REST Accessor.
  4. Return to your original Spreadsheet, and Refresh.
    The API Creator menu displays.

Run the Script

Reload your spreadsheet, and a new menu item will appear: API Creator, Get Data.

Log In

On first access, you will be asked to authorize the script. After you've done so, you will be asked to login using the information provided by your administrator:

Select Resource, Target Sheet

Specify your parameters:

  1. Select a predefined Resource.
  2. Optionally, specify a filter (e.g., name > "B").
  3. Select an existing sheet or create a new sheet and specify its name:
Result

Click Get Data. The new sheet created with your results displays:

Use Existing Sheets

You will often want to refresh your data into an existing sheet - simply select it from the list. The system ensures that your sheet's column headers match the data. This is flexible - you can drop columns or rearrange them.
ċ
catalog-permission.png
(189k)
Val Huber,
Feb 16, 2016, 2:30 PM
ċ
create sheet.png
(185k)
Val Huber,
Feb 16, 2016, 2:30 PM
ċ
get-data.png
(117k)
Val Huber,
Feb 16, 2016, 2:30 PM
ċ
getJSON.png
(334k)
Val Huber,
Feb 16, 2016, 2:30 PM
ċ
import script.png
(239k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
invoke script.png
(229k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
load script editor.png
(152k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
login.png
(141k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
name columns.png
(165k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
obtain script.png
(261k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
parms-newSheet.png
(145k)
Val Huber,
Feb 16, 2016, 2:30 PM
ċ
paste script.png
(209k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
result-newSheet.png
(138k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
result.png
(183k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
run script.png
(240k)
Val Huber,
Feb 16, 2016, 2:29 PM
ċ
run-script.png
(139k)
Val Huber,
Feb 16, 2016, 2:30 PM
ċ
script-gallery-1.png
(126k)
Val Huber,
Feb 16, 2016, 2:29 PM
Comments