Converting a String Retrieved by an API-induced Script to Manageable Google Sheet

Question

I am attempting to use a Google Sheets API-driven script to pull metadata from a website. Essentially, I input the DOI of a scholarly article, run the formula from the script, and it spits out a string of information (all in one cell, I might add) that looks like this:

{"status": "ok", "paper": {"classification": "OK", "title": "The Psychophysiology of Social Action: Facial Electromyographic Responses to Stigmatized Groups Predict Antidiscrimination Action", "records": [{"splash_url": "https://doi.org/10.1080/01973533.2013.823618", "doi": "10.1080/01973533.2013.823618", "publisher": "Informa UK Limited", "issue": "5", "journal": "Basic and Applied Social Psychology", "issn": "0197-3533", "volume": "35", "source": "crossref", "policy": {"romeo_id": "1139", "preprint": "can", "postprint": "can", "published": "cannot"}, "identifier": "oai:crossref.org:10.1080/01973533.2013.823618", "type": "journal-article", "pages": "418-425"}], "authors": [{"name": {"last": "Stewart", "first": "Tracie L."}}, {"name": {"last": "Amoss", "first": "R. Toby"}}, {"name": {"last": "Weiner", "first": "Brittany A."}}, {"name": {"last": "Elliott", "first": "Lisa A."}}, {"name": {"last": "Parrott", "first": "Dominic J."}}, {"name": {"last": "Peacock", "first": "Chloe M."}}, {"name": {"last": "Vanman", "first": "Eric J."}}], "date": "2013-09-01", "type": "journal-article"}}

Right? So... I would like to know what sort of Script I can use to push this string of data into various columns based on pre-ordained words included in this string, such as "title," "doi," "issue," "journal," etc. I keep trying crazy roundabout ways to this, but I would like something concise and easy.

Please help.

This looks like a JSON format. Take a look at the `jsonlite` package. For reference see: https://stackoverflow.com/questions/2061897/parse-json-with-r — Dave2e, Jul 12 '17 at 22:02

score 0 · Answer 1 · answered Jul 12 '17 at 22:38

0

Here is some sample code that calls the Google Books API and parses the JSON results.

function isbnLookup(id) {
  var url = "https://www.googleapis.com/books/v1/volumes?country=US&q=isbn:"+id+getApiKey();
  var options = {contentType : "application/json"};

  var resp = UrlFetchApp.fetch(url, options);
  if (resp == null || resp == "") return "N/A";

  var respdata = JSON.parse(resp.getContentText());
  if (respdata["items"] == undefined) return "Not found";
  if (respdata["items"].length == 0) return "Not found";

  var data = respdata["items"][0]["volumeInfo"];
  return (data["subtitle"] == undefined) ? 
    data["title"] : data["title"] + ": " + data["subtitle"];
}

The call to JSON.parse() will return an object that you can inspect by property name.

answered Jul 12 '17 at 22:38

terrywb

3,740
3
25
50

Thanks, Terry. This seems to have attempted to work, but I got an error saying the returned data is a string, not a function. Here's the full code I'm working with: function class(doi) { var parameters = {method : "get"}; var xmlText = UrlFetchApp.fetch("http://dissem.in/api/" + doi, parameters).getContentText(); var options = {contentType : "application/json"}; if (xmlText == null || xmlText == "") return "N/A"; – Aajay Murphy Jul 14 '17 at 13:37
var respdata = JSON.parse(xmlText()); if (respdata["items"] == undefined) return "Not found"; if (respdata["items"].length == 0) return "Not found"; var data = respdata["items"][0]["volumeInfo"]; return (data["subtitle"] == undefined) ? data["title"] : data["title"] + ": " + data["subtitle"]; return xmlText; } – Aajay Murphy Jul 14 '17 at 13:39

Converting a String Retrieved by an API-induced Script to Manageable Google Sheet

1 Answers1