CODEWITHSUNDEEP
javascriptjsongoogle-apps-scriptgoogle-sheetswikidata
BegCoder
BegCoder

Reputation: 103

Function for importing JSON data into Google Spreadsheets doesnt work when the api contains " | "

I am trying to import JSON data to google sheets using a wikidata API.

However, one of the symbols that are used in this api is the vertical bar: | . It is used as an "and" . For example if I want to import data in BOTH english and greek, then I have to use the " | ".

Example API url: https://www.wikidata.org/w/api.php?action=wbgetentities&props=labels&languages=en|el&format=json&ids=Q192151

enter image description here

Notice the use of the | on the api url between en and el.

I use a custom function named importJSON that can be found here

When i paste the api url WITH the | to import the json data to my google sheet, I get an error:

enter image description here

The error says that is on the line 139 which is this in the script:

var jsondata = UrlFetchApp.fetch(url);

BUT if I DONT contain the " | " in the api url, it works fine:

enter image description here

How can I import the JSON data from the api WITH THE " | " ?

I don't know much from coding so if someone could modify the custom script i linked above, or tell me what should I add to the script to make it work?

Upvotes: 0

Views: 759

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

You need to encode the url.

Try this light import json function alternative

function myFunction(url='https://www.wikidata.org/w/api.php?action=wbgetentities&props=labels&languages=en|el&format=json&ids=Q192151') {
  const res = UrlFetchApp.fetch(encodeURI(url));
  Logger.log(res.getContentText())
  const obj = JSON.parse(res.getContentText());
  var result = []
  for (let p in obj.entities['Q192151'].labels) {
    var o = obj.entities['Q192151'].labels[p]
    result.push([o.language,o.value])
  } 
  Logger.log(result)
  return(result)
}

enter image description here

encode

Upvotes: 4

Related Questions