NOAA Alert Lookup

/**
 * Weather Alerts Script for Google Sheets
 * 
 * This script automatically fetches and updates weather alerts from the National Weather Service
 * for a list of ZIP codes in a Google Sheet.
 * 
 * Sheet Layout:
 * - Column F: ZIP codes (starting from row 3)
 * - Column G: Weather alerts (starting from row 3)
 * - Cell G2: Last update timestamp
 * 
 * Main Functions:
 * - updateWeatherAlerts(): Fetches and updates weather alerts for all ZIP codes
 * - createHourlyTrigger(): Sets up automatic hourly updates
 * - deleteAllTriggers(): Removes all automatic update triggers
 * 
 * Usage:
 * 1. Enter ZIP codes in column F, starting from row 3.
 * 2. Run updateWeatherAlerts() to populate alerts.
 * 3. (Optional) Run createHourlyTrigger() to set up automatic hourly updates.
 * 
 * Important Notes:
 * - Works only with U.S. ZIP codes
 * - Uses external APIs: zippopotam.us for ZIP code data, api.weather.gov for weather alerts
 * - Ensure compliance with your organization's data handling policies
 * 
 * Troubleshooting:
 * - "Error: Zip code not found" indicates an invalid ZIP code
 * - If alerts aren't updating, check script permissions
 * 
 * Modifying the Script:
 * - ZIP_COLUMN and ALERT_COLUMN variables at the top of the script define data locations
 * - Adjust createHourlyTrigger() for different update frequencies
 * 
 * Caution: Modifying this script requires JavaScript knowledge. Seek assistance if unsure.
 * 
 * Last Updated: 8 Oct 2024
 */


function ziploc(zip, country) {
  // default country is US
  country = country === undefined ? 'US' : country
  
  // to learn more about the zippopotam.us API, visit
  var response = UrlFetchApp.fetch("http://api.zippopotam.us/" + country + "/" + zip, {muteHttpExceptions: true});
  
  if (String(response.getResponseCode())[0] === '4'){
    return "Zip code not found"
  }
  
  var z = JSON.parse(response.getContentText());
  
  return [ [z["country"], z.places[0]["state"], z.places[0]["place name"], z.places[0].latitude, z.places[0].longitude] ]
}

function getWeatherAlerts(lat, lon) {
  // Step 1: Get zone information
  var pointUrl = "https://api.weather.gov/points/" + lat + "," + lon;
  var pointResponse = UrlFetchApp.fetch(pointUrl, {
    'muteHttpExceptions': true,
    'headers': {
      'User-Agent': 'Google Apps Script Weather Alert Lookup (your-email@example.com)'
    }
  });
  
  if (pointResponse.getResponseCode() !== 200) {
    throw new Error('Failed to get zone information. Status code: ' + pointResponse.getResponseCode());
  }
  
  var pointData = JSON.parse(pointResponse.getContentText());
  var zoneId = pointData.properties.forecastZone.split('/').pop();

  // Step 2: Get active alerts for the zone
  var alertsUrl = "https://api.weather.gov/alerts/active/zone/" + zoneId;
  var alertsResponse = UrlFetchApp.fetch(alertsUrl, {
    'muteHttpExceptions': true,
    'headers': {
      'User-Agent': 'Google Apps Script Weather Alert Lookup (your-email@example.com)'
    }
  });
  
  if (alertsResponse.getResponseCode() !== 200) {
    throw new Error('Failed to get alerts. Status code: ' + alertsResponse.getResponseCode());
  }
  
  var alertsData = JSON.parse(alertsResponse.getContentText());

  // Step 3: Extract relevant alert information
  var alerts = alertsData.features.map(function(feature) {
    return {
      event: feature.properties.event
      //,headline: feature.properties.headline
      ,severity: feature.properties.severity
      //,urgency: feature.properties.urgency
    };
  });

  return {
    //count: alerts.length,
    alerts: alerts
  };
}

function getWeatherAlertsByZip(zip, country) {
  var locationInfo = ziploc(zip, country);
  
  if (locationInfo === "Zip code not found") {
    return "Error: " + locationInfo;
  }
  
  var lat = locationInfo[0][3];
  var lon = locationInfo[0][4];
  
  try {
    var alertsInfo = getWeatherAlerts(lat, lon);
    return {
      /*location: {
        country: locationInfo[0][0],
        state: locationInfo[0][1],
        city: locationInfo[0][2],
        latitude: lat,
        longitude: lon
      },*/
      alerts: alertsInfo
    };
  } catch (error) {
    return "Error: " + error.message;
  }
}

//********Start Scheduling */
function updateWeatherAlerts() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheets()[0];  // Get the first sheet

  if (!sheet) {
    Logger.log("No sheets found in the spreadsheet");
    return;
  }

  // Get ZIP codes from the specified column, starting from row 3
  var zipRange = sheet.getRange(ZIP_COLUMN + "3:" + ZIP_COLUMN);
  var zipCodes = zipRange.getValues().filter(String);
  
  // Prepare an array to hold all alert data
  var alertData = [];

  zipCodes.forEach(function(zip, index) {
    var alerts = getWeatherAlertsByZip(zip[0]);
    alertData.push([formatAlerts(alerts)]);
  });

  // Write all alerts at once
  if (alertData.length > 0) {
    sheet.getRange(ALERT_COLUMN + "3:" + ALERT_COLUMN + (alertData.length + 2)).setValues(alertData);
  }
  
  // Update last refresh time
  sheet.getRange(ALERT_COLUMN + "2").setValue("NOAA Alerts per ZIP Last Updated: " + new Date().toLocaleString());
}

function formatAlerts(result) {
  if (typeof result === 'string' && result.startsWith('Error:')) {
    return result;  // Return error message as is
  }

  var alertsArray = result.alerts.alerts;
  
  if (alertsArray.length === 0) {
    return "No active weather alerts for this location.";
  }

  // Define severity order
  var severityOrder = {
    "Extreme": 1,
    "Severe": 2,
    "Moderate": 3,
    "Minor": 4,
    "Unknown": 5
  };

  // Sort alerts by severity
  alertsArray.sort(function(a, b) {
    return severityOrder[a.severity] - severityOrder[b.severity];
  });

  // Format alerts into a readable string with line breaks and commas
  var formattedAlerts = alertsArray.map(function(alert, index) {
    var alertString = alert.event + " (Severity: " + alert.severity + ")";
    // Add a comma to all but the last item
    if (index < alertsArray.length - 1) {
      alertString += ",";
    }
    return alertString;
  }).join("\n");  // Join with newline character

  return formattedAlerts;
}

// Function to create a trigger that runs every hour
function createHourlyTrigger() {
  ScriptApp.newTrigger('updateWeatherAlerts')
      .timeBased()
      .everyHours(1)
      .create();
}

// Function to remove all triggers (useful for debugging or resetting)
function deleteAllTriggers() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
}
//*****End Scheduling */

// Example usage
function WEATHER_ALERTS_BY_ZIP(zip, country) {
  var result = getWeatherAlertsByZip(zip, country);
  
  if (typeof result === 'string' && result.startsWith('Error:')) {
    return result;  // Return error message as is
  }

  var alertsArray = result.alerts.alerts;
  
  if (alertsArray.length === 0) {
    return "No active weather alerts for this location.";
  }

  // Define severity order
  var severityOrder = {
    "Extreme": 1,
    "Severe": 2,
    "Moderate": 3,
    "Minor": 4,
    "Unknown": 5
  };

  // Sort alerts by severity
  alertsArray.sort(function(a, b) {
    return severityOrder[a.severity] - severityOrder[b.severity];
  });

  // Format alerts into a readable string with line breaks and commas
  var formattedAlerts = alertsArray.map(function(alert, index) {
    var alertString = alert.event + " (Severity: " + alert.severity + ")";
    // Add a comma to all but the last item
    if (index < alertsArray.length - 1) {
      alertString += ",";
    }
    return alertString;
  }).join("\n");  // Join with newline character

  return formattedAlerts;
}

// Helper function to test the output in Google Apps Script
function testWeatherAlertsByZipFormatted() {
  var zip = "10001"; // New York City ZIP code
  var country = "US"; // Optional, defaults to US if not provided
  
  var result = WEATHER_ALERTS_BY_ZIP(zip, country);
  Logger.log(result);
}