Importing JSON Data into Google Sheets

Question

I am trying to import mining data from Slush Pool via their API. I am using a Google Sheets script called ImportJSON which can be found here. This code does not include a function for API keys or access tokens so I added a wrapper based off information from this post. This wrapper was added at line 255 of the original ImportJSON file.

/**
 *
 * Wrapper 
 *
 * @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-SlushPool-Auth-Token": api_key
    }
  }
  return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_)
}

The line "X-SlushPool-Auth-Token": api_key was included to satisfy Slush Pool's API authentication. They specifically say

An access profile token has to be included in the HTTP header field named SlushPool-Auth-Token or X-SlushPool-Auth-Token to authenticate your requests.

They also give this Python example using cURL:

curl https://slushpool.com/stats/json/btc/ -H "SlushPool-Auth-Token: <your access token>"

To import the JSON file I type the following formula in a Google Sheets cell.

=ImportJSON_words("https://slushpool.com/stats/json/btc/","xxxxxxxxxx","","noTruncate")

The URL is given by Slush Pool, the x's represent my API key, the query field is empty and "noTruncate" is one of the parse options given in the ImportJSON documentation which prevents the data from being shortened.

I am getting a #ERROR, namely a formula parse error and I am not seeing any imported data. I am not sure where I am going wrong with this API access token. Any advice would be appreciated.

Answer 1

Formula parse error usually occurs because there is a Google Sheets formula syntax error. This is unrelated to scripts for custom functions.

1. Double check that the spreadsheet region is correct and the decimal separator for such region. If the decimal separator is a . your formulas should have a , as function parameter separator otherwise they should use a ;

2. Check that your formula doesn't include:

   • curly quotes instead of strait quotes
   • any hidden character that might be inadvertently added when doing copy paste from a Wordpress / CMS generated web page.
   • a parenthesis like character instead of parenthesis (same reason as the above point)

3. Check that each parameter of your formula is properly set. One way to do that is to make a formula for each parameter i.e.

   ="https://slushpool.com/stats/json/btc/"
="xxxxxxxxxx"
   etc.

4. Try using Google Sheets in incognito mode with all the extensions disabled.

While formula parse errors are unrelated to scripts, if all the previous have not worked...

1. While there are very few restrictions for custom functions names, try changing the function name (remove the underscore, be sure to not use a reserved name i.e. simple triggers function names, JavaScript reserved words, Google Sheets functions names)

2. It might be a good idea to start from scratch but instead of modifying the original IMPORTJSON .gs files, add a new .gs file and add to it your wrapper. This because sometimes one spreadsheet does "strange" things but others not. Also include @customfunction in the JSDoc comment of your custom function.