Home > OS >  Importing extra API data via importJSON usage of data from json in formulas
Importing extra API data via importJSON usage of data from json in formulas

Time:04-30

I have made a google sheet with support of Tanaike. It is fully functional but I want to change something in the input and also a colum resulted in another format or formula. This are the things where I was stuck. First of all this is my code:



function updateStampInSheet(e) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
    var stamp = new Date();
    sheet.getRange(1,1).setValue(stamp);
}
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','icon'];
  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;
}
  1. The json is updating every second and the datestamp is printed in the sheet. Also when updating there is a column called "lowTime" and "highTime". Is it possible to add 2 extra columns with the following data with the data from now subtracted with "lowTime" or "highTime"? In a sheet is it possible but I couldn't figure out how I could do it in the code. See following picture: enter image description here

    Note:

    • First, I thought that the images can be directly put to the cells using CellImageBuilder. But, in your situation, the data is large. In this case, I noticed that when IMAGE formula is used, the process cost can be reduced from that of CellImageBuilder. So in this sample, I used IMAGE formula.

    Reference:

  • Related