google-sheets-apps-script.js

function us_cpi() {
  try {
    const url = "https://api.bls.gov/publicAPI/v1/timeseries/data/CUUR0000SA0"

    const response = UrlFetchApp.fetch(url)
    const data = JSON.parse(response.getContentText())

    if (data && data.Results && data.Results.series && data.Results.series.length > 0) {
      const currentData = data.Results.series[0].data[0]
      const previousYearData = data.Results.series[0].data[13]

      return Number(currentData.value) / Number(previousYearData.value) - 1
    }

    return "data not found"
  } catch (e) {
    return `error fetching data: ${e.message}`
  }
}

function usd_brl() {
  try {
    const endDate = new Date()
    const startDate = new Date()
    startDate.setDate(endDate.getDate() - 30)

    const formatDate = (date) => Utilities.formatDate(date, 'UTC-3', 'MM-dd-yyyy')

    const url = `https://olinda.bcb.gov.br/olinda/servico/PTAX/versao/v1/odata/` +
      `CotacaoDolarPeriodo(dataInicial=@dataInicial,dataFinalCotacao=@dataFinalCotacao)` +
      `?@dataInicial='${formatDate(startDate)}'&@dataFinalCotacao='${formatDate(endDate)}'` +
      `&$top=1&$skip=0&$orderby=dataHoraCotacao%20desc` +
      `&$format=json` +
      `&$select=cotacaoCompra,cotacaoVenda,dataHoraCotacao`

    // console.log(url)
    const response = UrlFetchApp.fetch(url)
    const data = JSON.parse(response.getContentText())

    if (data && data.value && data.value.length > 0) {
      return data.value[0].cotacaoVenda
    }

    return "data not found"
  } catch (e) {
    return `error fetching data: ${e.message}`
  }
}

function br_selic() {
  try {
    const url = "https://api.bcb.gov.br/dados/serie/bcdata.sgs.4189/dados/ultimos/1?formato=json"

    const response = UrlFetchApp.fetch(url)
    const data = JSON.parse(response.getContentText())

    if (data && data.length > 0) {
      return Number(data[0].valor) / 100.0
    }

    return "data not found"
  } catch (e) {
    return `error fetching data: ${e.message}`
  }
}

function br_ipca() {
  try {
    const url = "https://api.bcb.gov.br/dados/serie/bcdata.sgs.13522/dados/ultimos/1?formato=json"

    const response = UrlFetchApp.fetch(url)
    const data = JSON.parse(response.getContentText())

    if (data && data.length > 0) {
      return Number(data[0].valor) / 100.0
    }

    return "data not found"
  } catch (e) {
    return `error fetching data: ${e.message}`
  }
}

// console.log("us_cpi():", us_cpi())
// console.log("usd_brl():", usd_brl())
// console.log("br_selic():", br_selic())
// console.log("br_ipca():", br_ipca())

// US Interest Rate
// =INDEX(IMPORTHTML("https://www.federalreserve.gov/releases/h15/", "table", 1), 2, 6) / 100