Last active
April 26, 2022 13:12
-
-
Save squaresmile/841316bbff72d9936df652c16b2e22cd to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); | |
ui.createMenu('API data') | |
.addItem('Update Data', 'pullData') | |
.addToUi(); | |
} | |
const NICE_URL = "https://api.atlasacademy.io/export/JP/nice_servant_lang_en.json"; | |
function fetchData() { | |
const response = UrlFetchApp.fetch(NICE_URL, {'muteHttpExceptions': true}); | |
var json = response.getContentText(); | |
return JSON.parse(json); | |
} | |
function setTable(sheetName, headers, tableData) { | |
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); | |
sheet.clear({contentsOnly: true}); | |
sheet.getRange(1, 1, tableData.length + 1, headers.length).setValues([headers].concat(tableData)); | |
sheet.autoResizeColumns(1, headers.length); | |
} | |
function getServantMatUsage(servant, field) { | |
const mats = servant[field]; | |
const matUsage = new Map(); | |
for (const itemQp of Object.values(mats)) { | |
for (const item of itemQp.items) { | |
if (matUsage.has(item.item.id)) { | |
matUsage.get(item.item.id).amount += item.amount; | |
} else { | |
matUsage.set(item.item.id, { | |
itemId: item.item.id, | |
itemName: item.item.name, | |
amount: item.amount | |
}) | |
} | |
} | |
} | |
return matUsage; | |
} | |
function numArray(from_num, to_num) { | |
const out = []; | |
for (let i = from_num; i <= to_num; i++) { | |
out.push(i.toString()); | |
} | |
return out; | |
} | |
function pullData() { | |
const data = fetchData(); | |
data.sort((a, b) => (a.collectionNo - b.collectionNo)); | |
for (const [sheetName, fieldName] of [["Ascension", "ascensionMaterials"], ["Skill", "skillMaterials"], ["Append", "appendSkillMaterials"]]) { | |
const servantMatUsages = data.map(servant => { | |
return { | |
...servant, | |
matUsage: getServantMatUsage(servant, fieldName), | |
}}); | |
const allMaterials = new Set(); | |
const materialHeader = []; | |
for (const servant of servantMatUsages) { | |
servant.matUsage.forEach(mat => { | |
if (!allMaterials.has(mat.itemId)) { | |
allMaterials.add(mat.itemId); | |
materialHeader.push({itemId: mat.itemId, itemName: mat.itemName}); | |
} | |
}) | |
} | |
materialHeader.sort((a, b) => (a.itemId - b.itemId)); | |
const levels = sheetName === "Ascension" ? numArray(0, 3) : numArray(1, 9); | |
const sheetHeader = ["#", "Name", "Item ID", "Item Name"].concat(levels) | |
const sheetData = []; | |
for (const servant of servantMatUsages) { | |
for (const rowItem of servant.matUsage.values()) { | |
const levelUsageNumbers = levels.map(level => servant[fieldName][level].items.find(item => item.item.id === rowItem.itemId)?.amount) | |
sheetData.push([servant.collectionNo, servant.name, rowItem.itemId, rowItem.itemName].concat(levelUsageNumbers)); | |
} | |
} | |
setTable(sheetName, sheetHeader, sheetData) | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment