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;
}
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:
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 usedIMAGE
formula.
Reference:
- 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