APIs, properties and cache

// Seven-day forecast into a sheet: one fetch, one setValues.
function forecastToSheet() {
  const url = 'https://api.open-meteo.com/v1/forecast'
    + '?latitude=13.75&longitude=100.50&daily=temperature_2m_max,temperature_2m_min,precipitation_sum'
    + '&timezone=Asia%2FBangkok';
  const d = JSON.parse(UrlFetchApp.fetch(url).getContentText()).daily;

  const rows = d.time.map((day, i) => [day, d.temperature_2m_max[i], d.temperature_2m_min[i], d.precipitation_sum[i]]);
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Weather') || ss.insertSheet('Weather');
  sheet.clearContents();
  const table = [['Date', 'Max °C', 'Min °C', 'Rain mm'], ...rows];
  sheet.getRange(1, 1, table.length, 4).setValues(table);
}

function postExample() {
  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({ title: 'Hello', body: 'From Apps Script', userId: 1 }),
    // headers: { Authorization: 'Bearer ' + getKey_() },  // only for APIs that need a key
    muteHttpExceptions: true,                             // don't throw on 4xx/5xx; let me check
  };
  const res = UrlFetchApp.fetch('https://jsonplaceholder.typicode.com/posts', options);

  if (res.getResponseCode() >= 400) {
    console.error(`Failed (${res.getResponseCode()}): ${res.getContentText().slice(0, 200)}`);
    return;
  }
  console.log(JSON.parse(res.getContentText()));
}

{ title: 'Hello', body: 'From Apps Script', userId: 1, id: 101 }

// Networks fail. Try a few times, waiting longer each time.
function fetchWithRetry(url, options = {}, tries = 3) {
  for (let attempt = 1; attempt <= tries; attempt++) {
    try {
      const res = UrlFetchApp.fetch(url, { ...options, muteHttpExceptions: true });
      const code = res.getResponseCode();
      if (code < 500 && code !== 429) return res;          // success or a real client error
      console.warn(`Attempt ${attempt}: HTTP ${code}`);
    } catch (err) {
      console.warn(`Attempt ${attempt}: ${err.message}`);
    }
    Utilities.sleep(1000 * 2 ** attempt);                    // 2 s, 4 s, 8 s
  }
  throw new Error(`Gave up on ${url} after ${tries} tries`);
}

// Run ONCE by hand (then delete the key from this code) to store a secret.
function saveKey() {
  PropertiesService.getScriptProperties().setProperty('WEATHER_KEY', 'paste-your-key-here');
}

// The trailing _ makes this private: it can't be run from the menu or google.script.run.
function getKey_() {
  const key = PropertiesService.getScriptProperties().getProperty('WEATHER_KEY');
  if (!key) throw new Error('No API key. Set WEATHER_KEY in Project settings → Script properties.');
  return key;
}

// User properties: each person who runs the script gets their own copy.
function rememberRoom(room) {
  PropertiesService.getUserProperties().setProperty('myRoom', room);
}
function myRoom() {
  return PropertiesService.getUserProperties().getProperty('myRoom') || 'not set';
}

// Ask the API at most once every 30 minutes, however often this runs.
function getForecast() {
  const cache = CacheService.getScriptCache();
  const hit = cache.get('forecast');
  if (hit) {
    console.log('from cache');
    return JSON.parse(hit);
  }

  const url = 'https://api.open-meteo.com/v1/forecast?latitude=13.75&longitude=100.50&daily=temperature_2m_max&timezone=Asia%2FBangkok';
  const data = JSON.parse(UrlFetchApp.fetch(url).getContentText());
  cache.put('forecast', JSON.stringify(data), 30 * 60);   // seconds; max 6 hours
  console.log('from the API');
  return data;
}

from the API      ← first run
from cache        ← every run in the next 30 minutes