ImportJSON with request Header

[phrak]

Hi all, I'm trying to import a JSON payload into Google Sheets, but the service I'm trying to consume requires an "authorization" key/value pair supplied in the request header: https://groups.caremonkey.com/api/docs/

I'm failing to work out how to send this header in the ImportJSON function.

I've read through the documentation, tested the API with PostMan (success) and tried a few variations of the function call below. =importjson(https://groups.caremonkey.com/api/v2/organizations/ORG_ID/child_groups, "authorize=tokenkey")

I've also tried the ImportJSONViaPost function (even though the operation is a GET), as well as tried changing Line 118 from: postOptions["method"] = "POST"; to postOptions["method"] = "GET";

I just can't figure out a way to send a header as part of the request.

Can anyone help me figure out what I've missed please?

Cheers

[Vadorequest]

I had the same issue, I created my own proxy using aws lambda, I query my proxy/connector who fetches the API itself and return results in a csv-friendly way

[krijnsent]

I made some code that might help:

function ImportJSONRaw(url, fetchOptions, removeFormatting) {

  //Url e.g.  'https://api-global.morningstar.com/sal-service/v1/fund/securityMetaData/F00000QERN?clientId=MDC'; 
  //fetchOptions e.g.  '{"method":"GET","headers":{"apikey":"lstzFDEOhfFNMLikKa0am9mgEKLBl49T"}}'; 

  if (fetchOptions != undefined) {
    if (fetchOptions.length == 0) {
      fetchOptions = "{}";
    }
  } else {
    //no fetchoptions
    fetchOptions = "{}";
  };
  
  var fetchOptionsSend = JSON.parse(fetchOptions);
  if (fetchOptionsSend["method"] == null) {
    fetchOptionsSend["method"]="GET";
  }
  if (fetchOptionsSend["contentType"] == null) {
    fetchOptionsSend["contentType"]="application/json";
  }
  if (fetchOptionsSend["muteHttpExceptions"] == null) {
    fetchOptionsSend["muteHttpExceptions"]=true;
  }

  var jsonData = UrlFetchApp.fetch(url, fetchOptionsSend);
  var responseCode = jsonData.getResponseCode();

  if (responseCode === 200) {
    var returnText = jsonData.getContentText();
  } else {
    var returnText = '{"ERROR":' + responseCode + ',"message":"' + jsonData.getContentText() + '"}';
  }

  //remove enters, tabs and spaces
  if (typeof removeFormatting != "boolean") {
    removeFormatting = false;
  }
  
  if (removeFormatting) {
    returnText = returnText.replace(/(\r\n\t|\n|\r\t)/gm,"");
    returnText = returnText.replace(" ","");
  }
  
  return returnText;
}

function ParseJSON(JSONstring, query, parseOptions) {
  var object = JSON.parse(JSONstring);
  return parseJSONObject_(object, query, parseOptions, includeXPath_, defaultTransform_);
}

[Vadorequest]

@krijnsent Would you mind make a PR to integrate your feature within the existing script? Would be much easier to update/reuse.

[krijnsent]

@krijnsent Would you mind make a PR to integrate your feature within the existing script? Would be much easier to update/reuse.

I would love to, but 1) I'm a windows user & git noob (using git desktop) and 2) #145 is blocking me a bit. Have put some more code in other replies and would love to push this code to the next version with several updates (coding & testing of code in google sheets I can do), my main issue is getting the hang of Git (and removing the not-checkout obstacle).

[Vadorequest]

I've answered your question regarding #145.

Regarding git itself, I advise you follow https://learngitbranching.js.org/ which is the best tutorial I know of, and the one my interns have to go through before working on real projects. :)

[krijnsent]

I've answered your question regarding #145.

Regarding git itself, I advise you follow https://learngitbranching.js.org/ which is the best tutorial I know of, and the one my interns have to go through before working on real projects. :)

Back to intern level... I feel overwhelmed with joy and will have a swing at it.

[Vadorequest]

😂 I didn't mean it in a belittle way. 😅

No matter how experienced, we all go through that "intern-ish" phase as new tech emerges 😛