Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎Resources‎ > ‎REST APIs‎ > ‎

Google Spreadsheet Example

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.


Business Context

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.


API Creator makes it fast and easy to build a REST Server

API Cretor 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:

API Creator 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 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:

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


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.
  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. Use the Logic Designer to create ResourcesLogic and Security.

User Authorization details are described below.

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

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.
Once you have your REST API, you can import it to a spreadsheet as described below.

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

Run the Script

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



Login

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:
  1. The Project URL is described above

  2. User Name and Password are predefined above



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 (described below), or create a new sheet and specify its name


Result

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.