Importing JSON Data into Google Sheets

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.

2 answers

  • answered 2022-01-23 04:15 Rub&#233;n

    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.

  • answered 2022-01-23 07:27 PrematureCorn

    In order to create a custom function in the Google Sheets script you must follow a specific syntax. If the syntax is not followed when you try to use that function in the sheet Google's autocomplete function will not show it. The above wrapper had to be modified to include the return and customfunction parameters. Once these parameters were added the function was identified by Google in the sheet and the rest of the script ran correctly.

    /**
     *
     * 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)
     *  
     * @return a two-dimensional array containing the data, with the first row containing headers
     * @customfunction
     **/
    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_)
    }
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum