Importing extra API data via importJSON + usage of data from json in formulas

Question

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: https://postimg.cc/GTRJ3FP5

2. The id is known, based on the ID I would like to show a picture then can get found in the following link: for example id=2:https://static.runelite.net/cache/item/icon/2.png. Is it possible to the valuable id in a formula for example =IMAGE("https://static.runelite.net/cache/item/icon/2.png") in an extra column? I have explained in the following picture:https://postimg.cc/nCbczdDz

3. I tried to add a third json in the database but it gives all the time an error (an extra column). This is the following: https://prices.runescape.wiki/api/v1/osrs/volumes. It gives volumes corresponding to the id.

4. My last question, how can I use data from the json database in a formula (extra column)? For example look picture in the link, I tried some functions but the result didn't work out https://postimg.cc/PCPNBLss.

I hope it is al clear for you all, if not please comment (this is the google spreadheet, I have added the colums: https://docs.google.com/spreadsheets/d/1fFrVLCWyCwDcqSU5-Bpt_C4sHXzLHFA30gk84TaUwOE/edit#gid=0).

Answer 1

In order to achieve your 4 requests, how about the following sample script? Unfortunately, in order to achieve your 4 requests, the custom function cannot be used. So, in this sample script, please run the script with the script editor.

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please set the sheet name. And, please run sample2 with the script editor. By this, the script is run.

function sample2() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  // Set the value to the cell "A1".
  const stamp = new Date();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.clearContents();
  sheet.getRange("A1").setValue(stamp);
  const date = stamp.getTime();

  // Retrieve values from URLs.
  const url1 = "https://prices.runescape.wiki/api/v1/osrs/mapping";
  const url2 = "https://prices.runescape.wiki/api/v1/osrs/latest";
  const url3 = "https://prices.runescape.wiki/api/v1/osrs/volumes";
  const [res1, res2, res3] = [url1, url2, url3].map(url => JSON.parse(UrlFetchApp.fetch(url).getContentText()));

  // Create an array for putting to Spreadsheet.
  const head = ['id', 'name', 'examine', 'members', 'lowalch', 'highalch', 'limit', 'high', 'low', 'lowTime', 'highTime'];
  const exHead = ["Icon", "Low Time (mins)", "High Time (mins)", "margin", "profit", "tax", "roi", "volume"];
  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 obj3 = res3.data;
  const keys = Object.keys(obj1).map(e => Number(e)).sort((a, b) => a - b);
  const values = [[...head, ...exHead], ...keys.map(k => {
    const o = Object.assign(obj1[k], obj2[k]);
    const temp = head.map(h => o[h] ? (['lowTime', 'highTime'].includes(h) ? new Date(o[h] * 1000) : o[h]) : "");
    const colO = temp[7] - temp[8];
    const colQ = temp[7] * 0.01;
    const colP = colO - colQ;
    const exTemp = [`=IMAGE("https://static.runelite.net/cache/item/icon/${temp[0]}.png")`, temp[9] ? Math.floor((date - temp[9].getTime()) / 60000) : null, temp[10] ? Math.floor((date - temp[10].getTime()) / 60000) : null, colO, colP, colQ, colP && temp[8].toString() ? colP / temp[8] : 0, obj3[k] || 0];
    return [...temp, ...exTemp];
  })];
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);

  // Set number format. These formats are retrieved from your sample Spreadsheet.
  sheet.getRange(3, 10, values.length, 2).setNumberFormat("d-m-yyyy");
  const formats = ['#,##0 "minutes ago"', '#,##0 "minutes ago"', "0", "0", "0", "0.00%"];
  const numberFormats = Array(values.length).fill(formats);
  sheet.getRange(3, 13, values.length, formats.length).setNumberFormats(numberFormats);
}

Testing:

When this script is run with the script editor, the following result is obtained. The number formats are used by retrieving from your provided sample Spreadsheet.

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:

• setValues(values)
• setNumberFormats(numberFormats)