Import data from a web API into Sheets

const FORECAST_DAYS = 7;
const FORECAST_LATITUDE = 52.52;
const FORECAST_LONGITUDE = 13.41;
const FORECAST_OWNER_MARKER = 'Owned by weather-import';

/**
 * A property-access view for validation, not a validated provider response.
 * Every data field remains unknown until the checks in forecastRows succeed.
 * @typedef {{daily?: {time?: unknown, temperature_2m_max?: unknown, temperature_2m_min?: unknown}|null, daily_units?: {temperature_2m_max?: unknown, temperature_2m_min?: unknown}|null}} ForecastCandidate
 */

function importForecastToSheet() {
  if (!Number.isInteger(FORECAST_DAYS) || FORECAST_DAYS < 1 || FORECAST_DAYS > 7) {
    throw new Error('Set FORECAST_DAYS to a whole number from 1 to 7.');
  }
  if (!Number.isFinite(FORECAST_LATITUDE) || Math.abs(FORECAST_LATITUDE) > 90 ||
      !Number.isFinite(FORECAST_LONGITUDE) || Math.abs(FORECAST_LONGITUDE) > 180) {
    throw new Error('Use numeric latitude from -90 to 90 and longitude from -180 to 180.');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  if (!spreadsheet) {
    throw new Error('Open this project through Extensions > Apps Script in the practice spreadsheet.');
  }
  const sheet = spreadsheet.getSheetByName('Forecast');
  if (!sheet) {
    throw new Error('Create a blank tab named Forecast before running this function.');
  }
  const target = sheet.getRange('A1:C12');
  checkForecastDestination(target, sheet);

  const query = [
    'latitude=' + encodeURIComponent(FORECAST_LATITUDE),
    'longitude=' + encodeURIComponent(FORECAST_LONGITUDE),
    'daily=temperature_2m_max,temperature_2m_min',
    'temperature_unit=celsius',
    'timezone=auto',
    'forecast_days=' + FORECAST_DAYS
  ].join('&');
  const response = UrlFetchApp.fetch('https://api.open-meteo.com/v1/forecast?' + query, {
    method: 'get',
    headers: { Accept: 'application/json' },
    muteHttpExceptions: true,
    followRedirects: false
  });
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) {
    throw new Error('The forecast service answered with status code ' + statusCode + '. No cells were written.');
  }
  const contentType = forecastHeader(response.getAllHeaders(), 'content-type');
  if (contentType.split(';')[0].trim().toLowerCase() !== 'application/json') {
    throw new Error('The forecast response was not application/json. No cells were written.');
  }
  const text = response.getContentText('UTF-8');
  if (text.length > 50000) {
    throw new Error('The forecast response exceeded the 50000-character parsing limit.');
  }
  /** @type {unknown} */
  let forecast;
  try {
    forecast = JSON.parse(text);
  } catch (error) {
    throw new Error('The forecast response was not valid JSON. No cells were written.');
  }

  const rows = forecastRows(forecast, FORECAST_DAYS);
  // Check again after the request in case the destination changed while it was pending.
  checkForecastDestination(target, sheet);
  target.setValues(rows);
  Logger.log('Wrote ' + FORECAST_DAYS + ' days of forecast.');
}

/**
 * @param {SpreadsheetApp.Range} target
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {void}
 */
function checkForecastDestination(target, sheet) {
  if (target.isPartOfMerge()) {
    throw new Error('Forecast!A1:C12 must not overlap merged cells.');
  }
  if (target.isBlank()) return;
  const markerCell = sheet.getRange('A12');
  if (markerCell.getValue() !== FORECAST_OWNER_MARKER || markerCell.getFormula() !== '') {
    throw new Error('Forecast!A1:C12 is not blank and has no valid ownership marker. Use a new practice sheet; do not clear existing data to bypass this check.');
  }
}

/**
 * @param {unknown} rawForecast
 * @param {number} expectedDays
 * @returns {Array<Array<string|number>>}
 */
function forecastRows(rawForecast, expectedDays) {
  // This view permits property probes; the original runtime checks still validate the value.
  const forecast = /** @type {ForecastCandidate|null|undefined} */ (rawForecast);
  if (!forecast || typeof forecast !== 'object' || Array.isArray(forecast) ||
      !forecast.daily || !forecast.daily_units) {
    throw new Error('The forecast response is missing daily data or units.');
  }
  const days = forecast.daily.time;
  const highs = forecast.daily.temperature_2m_max;
  const lows = forecast.daily.temperature_2m_min;
  if (!Array.isArray(days) || !Array.isArray(highs) || !Array.isArray(lows) ||
      days.length !== expectedDays || highs.length !== expectedDays || lows.length !== expectedDays) {
    throw new Error('The forecast arrays must each contain exactly ' + expectedDays + ' days.');
  }
  if (forecast.daily_units.temperature_2m_max !== '°C' ||
      forecast.daily_units.temperature_2m_min !== '°C') {
    throw new Error('The forecast temperature units must be Celsius.');
  }

  /** @type {Array<Array<string|number>>} */
  const rows = [['Date', 'High', 'Low']];
  let index = 0;
  /** @type {number|null} */
  let previousDay = null;
  while (index < expectedDays) {
    /** @type {unknown} */
    const day = days[index];
    /** @type {unknown} */
    const high = highs[index];
    /** @type {unknown} */
    const low = lows[index];
    if (typeof day !== 'string' || !/^\d{4}-\d{2}-\d{2}$/.test(day)) {
      throw new Error('Forecast day ' + (index + 1) + ' is not a YYYY-MM-DD date.');
    }
    const timestamp = Date.parse(day + 'T00:00:00Z');
    if (!Number.isFinite(timestamp) || new Date(timestamp).toISOString().slice(0, 10) !== day ||
        (previousDay !== null && timestamp - previousDay !== 86400000)) {
      throw new Error('Forecast dates must be valid consecutive calendar days.');
    }
    if (!Number.isFinite(high) || !Number.isFinite(low) ||
        /** @type {number} */ (high) < /** @type {number} */ (low)) {
      throw new Error('Forecast day ' + (index + 1) + ' needs numeric high and low temperatures with high >= low.');
    }
    rows.push([day, /** @type {number} */ (high), /** @type {number} */ (low)]);
    previousDay = timestamp;
    index = index + 1;
  }
  // The fixed output area has room for seven days, even when fewer are requested.
  while (rows.length < 8) {
    rows.push(['', '', '']);
  }
  rows.push(['Weather data by Open-Meteo', 'https://open-meteo.com/', '']);
  rows.push(['Licence: CC BY 4.0', 'https://creativecommons.org/licenses/by/4.0/', '']);
  rows.push(['Selected daily highs/lows; Celsius; local dates', '', '']);
  rows.push([FORECAST_OWNER_MARKER, '', '']);
  return rows;
}

/**
 * @param {Record<string, string|string[]>} headers
 * @param {string} wantedName
 * @returns {string}
 */
function forecastHeader(headers, wantedName) {
  for (const name of Object.keys(headers)) {
    if (name.toLowerCase() === wantedName) {
      const value = headers[name];
      if (Array.isArray(value)) return value.join(', ');
      return String(value);
    }
  }
  return '';
}