Get Australian award pay rates in Google Sheets

/**
 * RosterElf award rates - Google Sheets add-on.
 * Reads the public dataset published by rosterelf.com (single source of truth =
 * the award-rates data layer), so rates stay current with the 1-July refresh.
 */
var DATASET_URL = 'https://www.rosterelf.com/data/award-rates/index.json'
var CACHE_KEY = 'rosterelf_award_rates_v1'
var CACHE_TTL = 21600 // 6 hours

function fetchDataset_() {
  var cache = CacheService.getScriptCache()
  var cached = cache.get(CACHE_KEY)
  if (cached) {
    try {
      return JSON.parse(Utilities.ungzip(Utilities.newBlob(Utilities.base64Decode(cached), 'application/x-gzip')).getDataAsString())
    } catch (e) {
      // Unreadable cache entry - fall through and refetch.
    }
  }
  // The WAF blocks requests missing a User-Agent or Accept header; UrlFetchApp
  // omits Accept by default, so set both explicitly.
  var res = UrlFetchApp.fetch(DATASET_URL, {
    muteHttpExceptions: true,
    headers: { 'User-Agent': 'RosterElfSheets/1.0 (+https://www.rosterelf.com)', Accept: 'application/json' },
  })
  if (res.getResponseCode() !== 200) throw new Error('Could not load RosterElf award data (HTTP ' + res.getResponseCode() + ')')
  var text = res.getContentText()
  var data = JSON.parse(text)
  // CacheService caps a value at 100 KB and the dataset is ~155 KB, so gzip it
  // (compresses to ~25 KB). Caching is best-effort - never let it break the lookup.
  try {
    var packed = Utilities.base64Encode(Utilities.gzip(Utilities.newBlob(text)).getBytes())
    if (packed.length < 100000) cache.put(CACHE_KEY, packed, CACHE_TTL)
  } catch (e) {
    // Ignore cache write failures.
  }
  return data
}

function norm_(s) {
  return String(s || '')
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, ' ')
    .trim()
}

function findAward_(data, award) {
  var q = norm_(award)
  return data.awards.filter(function (x) {
    return norm_(x.slug) === q || norm_(x.name).indexOf(q) >= 0 || (x.shortLabel && norm_(x.shortLabel).indexOf(q) >= 0) || norm_(x.code) === q
  })[0]
}

/**
 * Returns the 2026/27 minimum hourly rate for an Australian modern award classification.
 * Indicative only - verify against the Fair Work pay guide before paying wages.
 *
 * @param {string} award  Award name, short name, slug or code (e.g. Hospitality or MA000009).
 * @param {string} level  Classification label (e.g. Level 2).
 * @param {string} type   casual or permanent (default: permanent).
 * @return The hourly rate.
 * @customfunction
 */
function ROSTERELF_RATE(award, level, type) {
  if (!award || !level) throw new Error('Provide an award and a classification level.')
  var data = fetchDataset_()
  var a = findAward_(data, award)
  if (!a) throw new Error('Award not found: ' + award)
  var q = norm_(level)
  var c = a.classifications.filter(function (x) {
    return norm_(x.label) === q || norm_(x.id) === q
  })[0]
  if (!c) {
    var labels = a.classifications
      .map(function (x) {
        return x.label
      })
      .join(', ')
    throw new Error('Classification not found: ' + level + '. Options: ' + labels)
  }
  var casual =
    String(type || '')
      .toLowerCase()
      .indexOf('cas') >= 0
  return casual ? c.hourlyCasual : c.hourlyPermanent
}

function onOpen() {
  SpreadsheetApp.getUi().createMenu('RosterElf').addItem('How to use', 'showAbout_').addToUi()
}

function showAbout_() {
  var lines = ['Insert a 2026/27 award rate with the custom function:', '', '=ROSTERELF_RATE(award, level, type)', '', 'Award = name, short name, slug or MA code. Type = casual or permanent.', '', 'Indicative minimums only - verify with Fair Work. Data: rosterelf.com/award-rates-dataset']
  SpreadsheetApp.getUi().alert('RosterElf award rates', lines.join('\n'), SpreadsheetApp.getUi().ButtonSet.OK)
}