CODEWITHSUNDEEP
javascriptjsonapigoogle-sheets
Juan Manuel Guiragossian
Juan Manuel Guiragossian

Reputation: 11

API Connections from external data to Google Sheets: JSON parse error

I'm trying to connect a marketing tool to google sheets to then use that data live. Info about API connection: https://developer.lemlist.com/#get-team-information

I'm attempting to connect it and get the results with the following script (I will then determine how to use that JSON in my sheets but it's not the issue right now).

function callNumbers() {
 
  var response = UrlFetchApp.fetch("https://api.lemlist.com/api/team/\
  --user:key");

var json = response.getContentText();
var data = JSON.parse(json);
Logger.log(data.title);

  

}

As you can see on the link, the JSON looks like this:

{
  "_id": "tea_aaqam5a3BkY8aje24",
  "name": "PiedPiper",
  "userIds": ["usr_aawMB5Gd5JJCFYvjp"],
  "createdBy": "usr_aawMB5Gd5JJCFYvjp",
  "createdAt": "2018-04-30T12:19:42.829Z",
  "apiKey": "aa13722b45b9c475cc686231b1af6583",
  "billing": {
    "quantity": 1,
    "ok": true,
    "plan": "freetrial"
  },
}

When running I get the error "SyntaxError: Unexpected token < in JSON at position 0" If the JSON is not formatted correctly, does this mean there's no way? Because how can I change it if I'm getting it from an external source.

Furthermore, there's a script to get a .csv file of the data I need. Do you know if it's possible to export that into sheets with a script? Any help on this would be much appreciated.

Thanks!

Upvotes: 1

Views: 109

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

According to your second question, here is a script that will give you data in sheet

Put these parameters in cells B2:I2

/_id    /name   /createdBy  /createdAt  /apiKey /billing/quantity   /billing/ok /billing/plan

Put this formula where you need =getDataJSON( url , B2:I2 ) url is either url or data and this script in the editor

// mike.steelson
let resultat = []; 
function getDataJSON(url,xpath){
  try{
    if (url.match(/http(s)?:\/\/?/g)){var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())}
    else{var data = JSON.parse(url)}
    var json = eval('data')
    if (typeof xpath == 'object'){var liste = xpath.join().split(",")} else {var liste = xpath.split("|")}
    if (json.length){json.forEach(function(elem){getData(elem,liste)})} else {getData(json,liste)}
    return resultat
  }
  catch(e) {
    return ('Pas de résultat - vérifier l\'url et les paramètres !');
  }
}
function getData(elem,liste){
  var prov=[]
  liste.forEach(function(chemin){
    var t=chemin.split('/');
    var obj=elem;
    for (var i=1;i<t.length;i++){obj=obj.item(t[i])}
    if(typeof obj=='object'){prov.push('['+obj+']')}else{prov.push(obj)}
  })
  resultat.push(prov)
}
Object.prototype.item=function(i){return this[i]};

Upvotes: 1

Mike Steelson
Mike Steelson

Reputation: 15328

Here is the correct way to write these json, there is an extra comma

{
  "_id": "tea_aaqam5a3BkY8aje24",
  "name": "PiedPiper",
  "userIds": ["usr_aawMB5Gd5JJCFYvjp"],
  "createdBy": "usr_aawMB5Gd5JJCFYvjp",
  "createdAt": "2018-04-30T12:19:42.829Z",
  "apiKey": "aa13722b45b9c475cc686231b1af6583",
  "billing": {
    "quantity": 1,
    "ok": true,
    "plan": "freetrial"
  }
}

Upvotes: 0

Related Questions