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:
  • Use the Logic Designer to determine your auth value as shown in the link above

function getJSON(aUrl,sheetname) {
  var sheetname = "test";
  var aUrl = "";
  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("","Rest Result Sheet")

Alert!   Under investigation (currently results in...

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