Google Spreadsheet Example

You can connect Google Spreadsheets to corporate data with zero friction, while ensuring security and integrity. Chief information officers (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, it can be incorrectly assembled.

The pressures are real. Business users need the data to complete their assignments. So they bring pressure to bear on Information Technology (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.

Solutions are emerging, making data available through RESTful APIs. Mobile apps, web apps, SOA services buses can consume such data, as well as 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, such as 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 Java Persistence API (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, you'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 an API/database which you'll use for this example. The API project sample is called Demo (Customers, Orders, Items, and Parts). At the completion of the signup process, you will be running API Creator on this project.

For more information about the Demo API, see Business Logic API Project Sample.

Obtain the Project URL

The project URL is required to run the Google Script. You can test your server without having to write a program using the REST Lab. The REST Lab shows your REST URL (upper red box in the following image), which you need.

For more information:

The REST URL is a string, like this:

The Customers resource has already been defined, returning multi-table JSON like this.

Aside, click Send request to issue the command and shows the JSON response, like this:

Obtain the URL for your API using the REST Lab.

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. Create an API, connect to your database. 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 (for example, Jersey), write SQL, convert it to JSON (for example, Jackson), and so forth. API Creator provides this.
  3. Create resources, logic, and security using API Creator.

For more information:

User authorization details:

Authorize Users

You must authorize users to access the data. If you are using the built-in authentication provider, 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 previously for the default API), 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.
After you have your REST API, you can import it to a spreadsheet. Alternatively, you can use the function described in the Google Spreadsheet Example 2.

Install the Google Script

First, download this file, open it, and copy it to your cli. While editing your spreadsheet, go to Tools > Script Gallery > REST access for API Creator.
  1. Go to Tools, Script Editor, Blank Project and create a new Google Spreadsheet.
  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, and call it "REST Accessor".
  4. Return to your original spreadsheet and refresh.
    The API Creator menu displays.

Run the Script

Reload your spreadsheet. A new menu item appears: API Creator, Get Data.

Log In

On first access, you are prompted to authorize the script. You are then asked to log in using the information provided by your administrator. The user name and password are predefined.

Select Resource, Target Sheet

Specify your parameters:

  1. Select a predefined resource.
  2. Optionally, specify a filter (for example, name > "B").
  3. Select an existing sheet or create a new sheet and specify its name:

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. Select it from the list. API Creator ensures that your sheet's column headers match the data. This is flexible. You can drop columns or rearrange them.