Google Spreadsheet Example 2

Invoke RESTful Services from a Google Spreadsheet

You can complete one of the following:

Obtain the Import Java Script

Click this GitHubGist link and download the script by clicking Download Gist:

Load the Script

Using Google Sheets, create a custom function. For more information about creating custom functions using Google Sheets, see the Apps Script site.

  1. Paste the Java Script that you downloaded, and alter the first six lines by completing the following:
    • Obtain the URL from the REST Lab and add the authorization parameter:
    • Determine your auth value using API Creator:

The following result is expected:

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 Google Sheet. Create a Google Sheet for the REST result. For our example, we named the spreadsheet "Rest Result Sheet".

The following image shows how to rename a Google Sheet:

Define the Sheet Columns

In the Google Sheet for the REST result, define column names that match the JSON result:

    • Column A: name
    • Column B: balance
    • Column C: credit_limit

Invoke the Script

Get the script.

In the Google Sheet for the REST result, click Tools, Script manager, select the getJSON function, and then click Run.

On first attempt, you are prompted for authorization. Accept and then re-execute the script.

The following result is expected:

Run from a Cell

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

=getJSON("","Rest Result Sheet")

Alert! Under investigation. The following results can be expected:

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