CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheets-api
Sander Vanhemel
Sander Vanhemel

Reputation: 29

Importing Importing API data via importJSON

This is my code:

function pricesV2(){
  var url='https://prices.runescape.wiki/api/v1/osrs/mapping'
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  let myItems = new Map()
  let myItem = new Map1()
  json=eval('data')
  json.forEach(function(elem){myItems.set(elem.id.toString(),elem.name)})
  json.forEach(function(elem){myItem.set(elem.id.toString(),elem.examine)})
  var url='https://prices.runescape.wiki/api/v1/osrs/latest'
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var result = []
  result.push(['#','name','examine','high','low','lowTime', 'highTime'])
  for (let p in eval('data.data')) {
    try{result.push([p,myItems.get(p),myItem.get(p),data.data.item(p).high,data.data.item(p).low,convertTimestamp(data.data.item(p).lowTime),convertTimestamp(data.data.item(p).highTime)])}catch(e){}
  }
  return result
}

This is maybe important to know the variables of the API:

function prices(url){
  //var url='https://prices.runescape.wiki/api/v1/osrs/latest'
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var result = []
  result.push(['#','high','low','highTime','lowTime'])
  for (let p in eval('data.data')) {
    try{result.push([p,data.data.item(p).high,data.data.item(p).low,data.data.item(p).lowTime, ,data.data.item(p).highTime])}catch(e){}
  }
  return result
}

function naming(url){
  //var url='https://prices.runescape.wiki/api/v1/osrs/mapping'
var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  var result = []
  result.push(["id","name","examine","members","lowalch","limit","value","highalch"])
  json=eval('data')
  json.forEach(function(elem){
    result.push([elem.id.toString(),elem.name,elem.examine,elem.members,elem.lowalch,elem.limit,elem.value,elem.highalch])
  })
  return result
}

These are 2 API combined (Importing API data via importJSON, solution did work out for 1 element, (element.name)). But when I want to add more from mapping it is giving an error. Could someone help me out? I want to combine all results in one table.

Upvotes: 1

Views: 240

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal is as follows.

  • You want to integrate 2 returned data (JSON data) with the value of id.
  • From your reply of The colums doesn't need in this specific order., you are not required to check the order of columns.
  • You want to run the script as a custom function.
    • From your showing script, I thought that you might have wanted to use this script as a custom function.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please put a custom function =SAMPLE() to a cell. By this, the script is run.

function SAMPLE() {
  const url1 = "https://prices.runescape.wiki/api/v1/osrs/mapping";
  const url2 = "https://prices.runescape.wiki/api/v1/osrs/latest";
  const [res1, res2] = [url1, url2].map(url => JSON.parse(UrlFetchApp.fetch(url).getContentText()));
  const head = [...Object.keys(res1[0]), ...Object.keys(res2.data[Object.keys(res2.data)[0]])];
  const obj1 = res1.reduce((o, e) => (o[e.id] = e, o), {});
  const obj2 = Object.entries(res2.data).reduce((o, [k, v]) => (o[k] = v, o), {});
  const keys = Object.keys(obj1).map(e => Number(e)).sort((a, b) => a - b);
  const values = [head, ...keys.map(k => {
    const o = Object.assign(obj1[k], obj2[k]);
    return head.map(h => o[h] || "");
  })];
  return values;
}

Testing:

When this script is run, the following result is obtained.

enter image description here

Note:

  • If you want to set the specific order of the columns, please modify head in the above script.

  • When the custom function of =SAMPLE() is put to a cell, if an error occurs, please reopen Spreadsheet and test it again.

  • If you want to directly put the values to the Spreadsheet instead of the custom function, please modify the script.

References:

Added:

From the following 3 new questions,

  1. Now how can I change like the top row to- > id, name, examine, members, lowalch, highalch, limit, high, low, lowtime, hightime? How can this be done in the function head, can't edit them individualy?
  1. And also how can I format/convert highTime and lowTime to time (hh:mm:ss)?

From The colums doesn't need in this specific order., I didn't check the order of the column. In that case, as I have already mentioned in my answer, please modify head as follows. About your 2nd new question, in this case, please parse the unix time as follows.

So, when these new 2 questions are reflected in my sample script, it becomes as follows.

Sample script:

function SAMPLE() {
  const url1 = "https://prices.runescape.wiki/api/v1/osrs/mapping";
  const url2 = "https://prices.runescape.wiki/api/v1/osrs/latest";
  const [res1, res2] = [url1, url2].map(url => JSON.parse(UrlFetchApp.fetch(url).getContentText()));
  const head = ['id', 'name', 'examine', 'members', 'lowalch', 'highalch', 'limit', 'high', 'low', 'lowTime', 'highTime'];
  const obj1 = res1.reduce((o, e) => (o[e.id] = e, o), {});
  const obj2 = Object.entries(res2.data).reduce((o, [k, v]) => (o[k] = v, o), {});
  const keys = Object.keys(obj1).map(e => Number(e)).sort((a, b) => a - b);
  const timeZone = Session.getScriptTimeZone();
  const values = [head, ...keys.map(k => {
    const o = Object.assign(obj1[k], obj2[k]);
    return head.map(h => o[h] ? (['lowTime', 'highTime'].includes(h) ? Utilities.formatDate(new Date(o[h] * 1000), timeZone, "HH:mm:ss") : o[h]) : "");
  })];
  return values;
}

Note:

  • About your following 3rd question,

    1. How can this database also be added <prices.runescape.wiki/api/v1/osrs/volumes>?
    • I think that this is a new question. In this case, please post it as a new question.

Upvotes: 1

Related Questions