Importing JSON with authentication in Google Spreadsheet (Campaign Monitor)

Question

I am using google spreadsheet to get subscriber data from Campaign Monitor, however I am hitting one issue

Using ImportJSON script - http://blog.fastfedora.com/projects/import-json

I put the URL of: http://[APIKEY]:[PASSWORD]@api.createsend.com/api/v3/subscribers/[LISTID].json?email=[EMAILADDRESS]

However it gives me a "Bad Request" error in the cell. I am guessing that this is because if i put this url in the browser, I get a confirm popup box saying "You are about to log in to the site "api.createsend.com" with the username: [USERNAME]"

Please can I get some ideas how to get around this or other ways I can put the Campaign Monitor JSON in Google spreadsheet

Answer 1

I found a useful comment on the homepage from Joey @ March 19, 2014 at 5:18 pm (no permalink).

Basic Auth in the URL is not implemented by the Spreadsheet API that the script is accessing. You can get around this by passing fetchOptions with a Base64 encoded username and password.

I modified it to remove the dummy variables and have had some success.

Add this to the very end of your ImportJSON Script Editor code:

function ImportJSONBasicAuthentication(url, query, parseOptions, username, password) {

  var fetchOptions = {
    "headers" : {
       "Authorization" : 'Basic ' + Utilities.base64Encode(username + ':' + password)
    },
    muteHttpExceptions: true
  };

   return ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeXPath_, defaultTransform_);
}