Error using ImportJSONBasicAuth to get data from my Bank API into Google Sheet

[benblaine]

I am trying to get my bank balances in Google Sheets and decided to use this script https://github.com/bradjasper/ImportJSON to pull data from my Investec bank account via the API here: https://developer.investec.com/programmable-banking/#authorization

I am calling the function ImportJSONBasicAuth("https://openapi.investec.com/identity/v2/oauth2/token","{username}","{secret}","","") with my username and secret, in my Google Sheet... But I get an error as follows

Error Exception: Request failed for https://openapi.investec.com returned code 400 (line 220).

Any ideas what I might be doing wrong?

---

EDIT

Digging a little deeper into the error...

This is the function I'm calling

function ImportJSONBasicAuth(url, username, password, query, parseOptions) {
  var encodedAuthInformation = Utilities.base64Encode(username + ":" + password);
  var header = {headers: {Authorization: "Basic " + encodedAuthInformation}};
  return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_);
}

Which calls this function in turn - which has the line producing the error i.e. line 220 commented below

function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) {
  var jsondata = UrlFetchApp.fetch(url, fetchOptions);  //line 220
  var object   = JSON.parse(jsondata.getContentText());
  
  return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc);
}

[benblaine]

I tried editing the ImportJSONBasicAuth function to include post as the method but it didn't work.

i.e.

var header = {method:'post', headers: {Authorization: "Basic " + encodedAuthInformation}};

[christales]

Hi, you are getting '400's since your bank uses OAuth2, which in your case means the necessity for two requests - one to obtain a Bearer Token and a second one to retrieve data you want using said token, which has an expiration time. This also means you will have to repeat the procedure pretty much every time you want to retrieve data from the API.

The ImportJSONBasicAuth is meant for one-step Basic Authorization (simply logging in with username & password), so you'll need to play around a little with UrlFetchApp directly instead, but you can still utilize parseJSONObject_ to insert the retrieved data into your spreadsheet.

To be honest Investec's API docs are not too user friendly, but you should be able to retrieve your token using some of the built-in parameters and then follow the similar pattern to retrieve your data based on provided examples.

So as an example:

const YOUR_API_KEY_STRING = "e2NsaWVudElkfTp7c2VjcmV0fQ==";
/* const payload = 'grant_type=client_credentials&scope=accounts'; 
// an optional requested access scope as a string- depends on you authorization level */
const options = {method: 'post', headers: {Authorization:  `Basic ${YOUR_API_KEY_STRING}`}} /*, payload}*/
const res = UrlFetchApp.fetch('https://openapi.investec.com/identity/v2/oauth2/token', options);
/* note the lack of Content-Type header in this case - UrlFetchApp uses the required one by default.
Same thing with that Accept: application/json header - no need for it. */
console.log(res.getContentText()); /* you should be able to see your access_token in the response here */

Now you're set to parse it to JSON, get that key and use it for the second API call, only now the authorization in the header uses that Bearer token per examples in the docs. Just mind the expiration date ;)

[benblaine]

Wow, thank you so much @christales - you provided me with the context I needed to understand what is going on.

I thought I could use ImportJSONBasicAuth to get the bearer token and then take it from there.

I will play around with your suggested code.

How can I thank you more!?

[benblaine]

Using @christales answer and rewriting the ImportJSONViaPost function I managed to create a function that gets the Basic Auth information from POST to import to my Google Sheet :)

/**
 * Helper function to authenticate with basic auth informations and import a JSON feed via a POST request. 
 * Returns the results to be inserted into a Google Spreadsheet. 
 * The JSON feed is flattened to create a two-dimensional array. 
 * The first row contains the headers, with each column header indicating the path to 
 * that data in the JSON feed. The remaining rows contain the data.
 *
 * To retrieve the JSON, a POST request is sent to the URL and the payload is passed as the content of the request using the content 
 * type "application/x-www-form-urlencoded". If the fetchOptions define a value for "method", "payload" or "contentType", these 
 * values will take precedent. For example, advanced users can use this to make this function pass XML as the payload using a GET 
 * request and a content type of "application/xml; charset=utf-8". For more information on the available fetch options, see
 * https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app . At this time the "headers" option is not supported.
 * 
 * By default, the returned data gets transformed so it looks more like a normal data import. Specifically:
 *
 *   - Data from parent JSON elements gets inherited to their child elements, so rows representing child elements contain the values 
 *     of the rows representing their parent elements.
 *   - Values longer than 256 characters get truncated.
 *   - Headers have slashes converted to spaces, common prefixes removed and the resulting text converted to title case. 
 *
 * To change this behavior, pass in one of these values in the options parameter:
 *
 *    noInherit:     Don't inherit values from parent elements
 *    noTruncate:    Don't truncate values
 *    rawHeaders:    Don't prettify headers
 *    noHeaders:     Don't include headers, only the data
 *    allHeaders:    Include all headers from the query parameter in the order they are listed
 *    debugLocation: Prepend each value with the row & column it belongs in
 *
 * For example:
 *
 *   =ImportJSON("http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json", "user=bob&apikey=xxxx", 
 *               "validateHttpsCertificates=false", "/feed/entry/title,/feed/entry/content", "noInherit,noTruncate,rawHeaders")
 * 
 * @param {url}          the URL to a public JSON feed
 * @param {username}     the Username for authentication
 * @param {password}     the Password for authentication
 * @param {payload}      the content to pass with the POST request; usually a URL encoded list of parameters separated by ampersands
 * @param {fetchOptions} a comma-separated list of options used to retrieve the JSON feed from the URL
 * @param {query}        a comma-separated list of paths to import. Any path starting with one of these paths gets imported.
 * @param {parseOptions} a comma-separated list of options that alter processing of the data
 * @customfunction
 *
 * @return a two-dimensional array containing the data, with the first row containing headers
 **/

function ImportJSONBasicAuthViaPost(url, username, password, payload, fetchOptions, query, parseOptions) {
  var postOptions = parseToObject_(fetchOptions);

  var APIKey = Utilities.base64Encode(username + ":" + password);

  const headers = {
    'Authorization': `Basic ${APIKey}`
  };

  if (postOptions["headers"] == null) {
    postOptions["headers"] = headers;
  }
  
  if (postOptions["method"] == null) {
    postOptions["method"] = "POST";
  }

  if (postOptions["payload"] == null) {
    postOptions["payload"] = payload;
  }

  if (postOptions["contentType"] == null) {
    postOptions["contentType"] = "application/x-www-form-urlencoded";
  }

  convertToBool_(postOptions, "validateHttpsCertificates");
  convertToBool_(postOptions, "useIntranet");
  convertToBool_(postOptions, "followRedirects");
  convertToBool_(postOptions, "muteHttpExceptions");
  
  return ImportJSONAdvanced(url, postOptions, query, parseOptions, includeXPath_, defaultTransform_);
}