Passing API Key to importJSON Function Script for Google Sheets

Question

I am attempting to import data from WordsAPI using importJSON, a custom Google script function in Google Sheets from Github. It's easy to import information from an API that does not require authentication. However, the WordsAPI requires a key and I can not figure out how to pass the key to the function. My best guess is:

=ImportJSONAdvanced("https://wordsapiv1.p.mashape.com/words/example/examples", "wordsapiv1.p.rapidapi.com", "<MYKEYHERE>", "/examples/", "noInherit,noTruncate,Headers")

But, this results in an error: Bad value (line 220).

The relevant section of the code is below. Go to the ImportJSON.gs at Github for the complete code.

function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) {
  var jsondata = UrlFetchApp.fetch(url, fetchOptions);
  var object   = JSON.parse(jsondata.getContentText());

  return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc);
}

I've read through the documentation regarding authentication at the WordsAPI site and they suggest the following code snippet:

// These code snippets use an open-source library. http://unirest.io/nodejs
unirest.get("https://wordsapiv1.p.mashape.com/words/soliloquy")
.header("X-Mashape-Key", "<MYKEYHERE>")
.header("Accept", "application/json")
.end(function (result) {
  console.log(result.status, result.headers, result.body);
});

I have also read through the Google Apps Script documentation on Authorization for Google Services, but it doesn't mean much to me. Is it possible the problem is ImportJSONAdvanced uses POST and the WordsAPI wants GET? If that is the case, how can I modify the code to make it work?

Thank you for reading. Any help would be very much appreciated.

---

Edit: Based on @chuckx comments and additional help. I added the following at line 255 to the original code.

/**
 *
 * Wrapper for WordsAPI
 *
 * @param {url} the URL to a http basic auth protected JSON feed
 * @param {api_key} the api_key for authentication
 * @param {query} always = ""
 * @param {parseOptions} a comma-separated list of options that may alter processing of the data (optional)
 */
function ImportJSON_words(url, api_key, query, parseOptions) {
  var header = {
    headers: {
      'X-Mashape-Key': api_key,
      'Accept': 'application/json'
    }
  }
  return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_)
}

It works.

Answer 1

First of all, note that the documentation explicitly states that ImportJSONAdvanced() cannot be called from a spreadsheet (i.e. as an in-cell formula):

 * An advanced version of ImportJSON designed to be easily extended by a script. This version cannot be called from within a 
 * spreadsheet.

Furthermore, the arguments you provide in your best guess don't align with the argument documentation for ImportJSONAdvanced(). For reference:

 * @param {url}           the URL to a public JSON feed
 * @param {fetchOptions}  an object whose properties are options used to retrieve the JSON feed from the URL
 * @param {query}         the query passed to the include function
 * @param {parseOptions}  a comma-separated list of options that may alter processing of the data
 * @param {includeFunc}   a function with the signature func(query, path, options) that returns true if the data element at the given path
 *                        should be included or false otherwise. 
 * @param {transformFunc} a function with the signature func(data, row, column, options) where data is a 2-dimensional array of the data 
 *                        and row & column are the current row and column being processed. Any return value is ignored. Note that row 0 
 *                        contains the headers for the data, so test for row==0 to process headers only.

Of interest is the fetchOptions argument, which is passed to UrlFetchAPP.fetch(url, params) as the params argument. According to the documentation, params is an object which can contain a headers parameter, which can be used to set the headers for the HTTP request.

So, one approach is defining your own Apps Script function which wraps ImportJSONAdvanced() and prepares the required header.

function ImportJSON_WordsAPI(path, query, parseOptions) {
  var url = 'https://wordsapiv1.p.mashape.com/words' + path;
  var fetchOptions = {
       'headers': {
           'X-Mashape-Key': '<MYKEYHERE>',
           'Accept': 'application/json',
       },
  }
  return ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeXPath_, defaultTransform_);
}

And then, use the function in a cell like this:

=ImportJSON_WordsAPI('/words/soliloquy', '/results', 'noInherit,noTruncate')

Warning: none of this is tested since I don't have access to WordsAPI.