The functionality to import JSON data into Google Spreadsheets is compromised when the API includes the characters " | "

Question

The functionality to import JSON data into Google Spreadsheets is compromised when the API includes the characters " | "

I am attempting to bring JSON data into Google Sheets using a Wikidata API.

However, one of the symbols utilized in this API is the vertical bar: |. It serves as an "and". For instance, if I wish to import data in BOTH English and Greek, then I need to use the "|".

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

https://i.sstatic.net/r4wQj.png

Note the usage of "|" in the API URL between "en" and "el".

I am using a customized function called importJSON which can be located here

When I paste the API URL WITH the "| to fetch the JSON data to my Google sheet, I encounter an error:

https://i.sstatic.net/2Vda1.png

The error points to line 139 in the script:

var jsondata = UrlFetchApp.fetch(url);

HOWEVER, when I OMIT the "|", the API URL works smoothly:

https://i.sstatic.net/XYAS1.png

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

I have limited knowledge in coding, so could someone modify the custom script I provided above or advise me on what needs to be added to the script for it to function correctly?

javascript json google-apps-script google-sheets wikidata

Answer 1

Answer №1

To secure the url, it is essential to encode it.

You can experiment with this simplified json function substitute

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)
}

https://i.sstatic.net/vbNuc.png

encode

Answer 2

To secure the url, it is essential to encode it.

You can experiment with this simplified json function substitute

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)
}

https://i.sstatic.net/vbNuc.png

encode

The functionality to import JSON data into Google Spreadsheets is compromised when the API includes the characters " | "

Answer №1

Similar questions

Scenario-specific blueprints

Altering the texture of a mesh in Three.js can completely transform the appearance

The second tab on Bootstrap5's tab content feature seems to generate an endless amount of white

Tips for concealing a div when the mouse is moved off it?

What is the best way to fetch json data and load it into jqgrid using ajax?

Uncovering the hidden gems within a data attribute

Running two blocks of scripts (utilizing lab.js as a loading manager)

Is there a way to extract "keys" from a mysterious .json file stored on my computer using groovy?

The sort function in Reactjs does not trigger a re-render of the cards

The font remains the same despite the <Div style=> tag

Dreamweaver restricts my ability to utilize JavaScript for coding

Reveal the MongoDB database connection to different sections within a Next.js 6 application

The output for JQuery $(this).data("id") is not defined

The server fails to properly receive the JSON object when utilizing Volley on an Android device

Error in Redux-tookit: The store is missing a valid reducer. Ensure that the argument provided to combineReducers is an object containing reducers as values

Avoiding server requests in Firefox by refraining from using ajax

Execute a function when a selection option is chosen consecutively two times

Loading screen featuring symbol percentage

When attempting to access /test.html, Node.js server returns a "Cannot GET"

Parent's hover element