mat_usage_update.gs

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