Docs‎ > ‎CA Live API Creator‎ > ‎Create‎ > ‎Resources‎ > ‎REST APIs‎ > ‎Google Spreadsheet Example‎ > ‎

Google Spreadsheet Example 2


Invoking RESTful services from a Google Spreadsheet

Once you have your REST API, you can import it to a spreadsheet as described below.

Alternatively, you can use this function.


Obtain the Import Java Script

Using this GitHubGist link, download the script:




Load the Script

Open the Script Editor like this:


Paste the script from above, and alter the first 6 lines as shown below:
  • Obtain the URL from the Rest Lab as shown above, and add the authorization parameter.  You should get a result something like:
https://mycloudeserver.com/rest/default/demo/v1/customer?auth=L4qvirW28TVHbblkaT8e:1
  • Use the Logic Designer to determine your auth value as shown in the link above


function getJSON(aUrl,sheetname) {
  var sheetname = "test";
  var aUrl = "https://mycloudeserver.com/rest/dfault/demo/v1/customer?auth=L4qvirW28TVHbblkaT8e:1";
  var response = UrlFetchApp.fetch(aUrl); // get feed
  var dataAll = Utilities.jsonParse(response.getContentText()); //
  var data = dataAll; //.value.items;

Create a Sheet for the Result

The getJSON function fills the JSON response into a Sheet, so you need to create one as shown below (here named "Rest Result Sheet"):

Define Sheet Columns

Next, define columns that match the JSON result:

Invoke the script

Click Tools > Script Manager, and run the getJSON script as shown below:


On first attempt, you will be prompted for authorization - just accept, and then re-execute the script.

And you should see this result:

Running from a cell

Return to your spreadsheet, and paste this into a cell:

=getJSON("https://mycloudeserver.com/rest/default/demo/v1/customer?auth=L4qvirW28TVHbblkaT8e:1","Rest Result Sheet")

Alert!   Under investigation (currently results in...

error: You do not have permission to call clear (line 18, file "getJson"