FFXIV API

/*
Created by RemcoE33
19 september 2021
https://www.reddit.com/r/sheets/comments/pqw55q/how_can_i_import_data_from_other_sites_into_my/
*/


function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('FFXIV')
    .addItem('Refresh prices', 'FFXIV')
    .addItem('Show History', 'createChart')
    .addToUi();
}

function FFXIV(worldName = 'famfrit', headers = true) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('RemcoE33');
  const ids = sheet.getRange(2, 2, sheet.getLastRow() - 1).getValues().flat();
  const output = [];

  ids.forEach((id, index) => {
    const object = getApiData(worldName, id);

    if (index == 0 && headers) {
      output.push(Object.keys(object))
    }

    output.push(Object.values(object))
  })

  const rowStart = (headers) ? 1 : 2;
  sheet.getRange(2,3,sheet.getLastRow(),sheet.getLastColumn()).clearContent();
  sheet.getRange(rowStart, 3, output.length, output[0].length).setValues(output);

}

function getApiData(world, id) {
  const response = UrlFetchApp.fetch(`https://universalis.app/api/${world}/${id}`, { muteHttpExceptions: true });

  if (response.getResponseCode() == 429) {
    Utilities.sleep(500);
    getApiData(world, id)
  } else {
    const data = JSON.parse(response.getContentText());
    const object = {
      'World Id': data.worldID,
      'Last Upload': new Date(data.lastUploadTime).toLocaleDateString(),
      'Regular Sale Velocity': data.regularSaleVelocity,
      'NQ Sale Velocity': data.nqSaleVelocity,
      'HQ Sale Velocity': data.hqSaleVelocity,
      'Average Price': data.averagePrice,
      'Average Price NQ': data.averagePriceNQ,
      'Average Price HQ': data.averagePriceHQ,
      'Min price': data.minPrice,
      'Min price NQ': data.minPriceNQ,
      'Min price HQ': data.minPriceHQ,
      'Max price': data.maxPrice,
      'Max price NQ': data.maxPriceNQ,
      'MAx price HQ': data.maxPriceHQ
    }
    return object;
  }
}


function getChartData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const activeSheet = ss.getActiveSheet();
  const cell = activeSheet.getActiveCell()
  const [ title, id, world ] = activeSheet.getRange(cell.getRow(), 1, 1, 3).getValues().flat();
  const response = UrlFetchApp.fetch(`https://universalis.app/api/history/${world}/${id}`)
  const json = JSON.parse(response.getContentText());
  const data = [];
  const labels = [];

  json.entries.forEach(entrie => {
    data.push(entrie.pricePerUnit)
    labels.push(Utilities.formatDate(new Date(entrie.timestamp * 1000), Session.getScriptTimeZone(), 'yyyy-MM-dd'))
  })
  return { title, data, labels };
}

function createChart() {
  const html = HtmlService.createHtmlOutputFromFile("chart");
  html.setHeight(500).setWidth(800);
  SpreadsheetApp.getUi().showDialog(html);
}