Импорт дополнительных данных API через importJSON + использование данных из json в формулах

Я сделал гугл лист при поддержке Танайке. Он полностью функционален, но я хочу что-то изменить на входе, а также столбец, полученный в другом формате или формуле. Это то, на чем я застрял. Прежде всего, это мой код:

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;
}

JSON обновляется каждую секунду, а метка даты печатается на листе. Также при обновлении появляется столбец под названием «lowTime» и «highTime». Можно ли добавить 2 дополнительных столбца со следующими данными, при этом данные теперь вычитаются с помощью «lowTime» или «highTime»? На листе это возможно, но я не мог понять, как это сделать в коде. Смотрите следующую картинку: https://postimg.cc/GTRJ3FP5

Идентификатор известен, на основе идентификатора я хотел бы показать изображение, которое можно найти по следующей ссылке: например, id=2: https://static.runelite.net/cache/item/icon/2.png. Можно ли использовать ценный идентификатор в формуле, например =IMAGE("https://static.runelite.net/cache/item/icon/2.png") в дополнительном столбце? Я объяснил на следующем рисунке: https://postimg.cc/nCbczdDz

Я пытался добавить третий json в базу данных, но он все время выдает ошибку (лишний столбец). Это следующее: https://prices.runescape.wiki/api/v1/osrs/volumes. Он дает объемы, соответствующие идентификатору.

Мой последний вопрос, как я могу использовать данные из базы данных json в формуле (дополнительный столбец)? Например посмотрите картинку по ссылке, пробовал некоторые функции но результата не получилось https://postimg.cc/PCPNBLss.

Надеюсь, вам всем все понятно, если нет, прокомментируйте (это таблица Google, я добавил столбцы: https://docs.google.com/spreadsheets/d/1fFrVLCWyCwDcqSU5-Bpt_C4sHXzLHFA30gk84TaUwOE/edit#gid=0 ).

Решение проблемы

Чтобы выполнить ваши 4 запроса, как насчет следующего примера сценария? К сожалению, для выполнения ваших 4 запросов нельзя использовать пользовательскую функцию. Итак, в этом примере скрипта запустите скрипт с помощью редактора скриптов.

Пример сценария:

Скопируйте и вставьте следующий скрипт в редактор скриптов электронной таблицы. И, пожалуйста, установите имя листа. И, пожалуйста, запустите sample2с помощью редактора сценариев. При этом запускается скрипт.

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);
}

Тестирование:

Когда этот сценарий запускается с помощью редактора сценариев, получается следующий результат. Числовые форматы используются путем извлечения из предоставленного образца электронной таблицы.

Примечание:

• Сначала я подумал, что изображения можно напрямую помещать в ячейки с помощью CellImageBuilder. Но в вашей ситуации данные большие. В этом случае я заметил, что при IMAGEиспользовании формулы стоимость процесса может быть снижена по сравнению с CellImageBuilder. Итак, в этом примере я использовал IMAGEформулу.

Справка:

• setValues ​​(значения)


• setNumberFormats (числовые форматы)