从Wunderground History API提取数据至谷歌表格的技术求助

Hey there! As a JavaScript newbie, let’s break this down into simple, actionable steps to pull yesterday’s temp and precipitation data from the Wunderground History API, get the time-specific observations you want, and dump everything into Google Sheets. We’ll use Google Apps Script (it’s just JavaScript tailored for Google tools) since it plays nicely with Sheets and avoids any cross-origin issues when fetching API data.

First, open your Google Sheet, click Extensions > Apps Script to open the script editor. This is where we’ll write all our code.

First, we need to grab the API data for yesterday. You’ll need your Wunderground API key (sign up for their service to get one) and your target location (like CA/San_Francisco or a zip code). Here’s the code to fetch and parse the data:

function fetchAndImportWeatherData() {
  // Replace these with your details
  const apiKey = "YOUR_WUNDERGROUND_API_KEY";
  const location = "CA/San_Francisco"; // e.g., "NY/New_York" or "90210"
  
  // Get yesterday's date in YYYYMMDD format (required by the API)
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  const dateString = Utilities.formatDate(yesterday, "GMT", "yyyyMMdd");
  
  // Build the API URL
  const apiUrl = `http://api.wunderground.com/api/${apiKey}/history/${dateString}/q/${location}.json`;
  
  // Fetch and parse the response
  try {
    const response = UrlFetchApp.fetch(apiUrl);
    const data = JSON.parse(response.getContentText());
    const observations = data.history.observations;
    
    if (!observations || observations.length === 0) {
      Logger.log("No weather observations found for yesterday.");
      return;
    }
    
    // We'll process the data here next...
    processWeatherData(observations);
    
  } catch (error) {
    Logger.log(`Error fetching data: ${error.message}`);
  }
}

Let’s create a helper function to extract the data we care about. We’ll grab the timestamp, temperature (you can pick Fahrenheit or Celsius), and hourly precipitation (or cumulative if you prefer):

function processWeatherData(observations) {
  // Extract all observations with temp and precip
  const allData = observations.map(obs => [
    obs.date.pretty, // Human-readable date/time
    obs.temp_f, // Use obs.temp_c for Celsius
    obs.precip_hr_in // Hourly precip (inches); use obs.precip_today_in for cumulative
  ]);
  
  // Now filter for the 6am-7pm window and sample observations
  const filteredData = filterTimeRange(observations);
  const sampledData = sampleObservations(filteredData);
  
  // Finally, write everything to Google Sheets
  writeToSheet(allData, sampledData);
}

Next, let’s filter out any observations outside your desired time window. The API gives us the hour as a 0-23 number, so we’ll keep entries where the hour is between 6 (6am) and 19 (7pm):

function filterTimeRange(observations) {
  return observations.filter(obs => {
    const hour = parseInt(obs.date.hour);
    return hour >= 6 && hour <= 19;
  });
}

You have two options here—pick whichever fits your needs better:

Option A: Take Every 4th Observation #

If you just want to grab every 4th entry from the filtered list:

function sampleObservations(filteredObservations) {
  // Option A: Take every 4th observation
  return filteredObservations.filter((_, index) => index % 4 === 0).map(obs => [
    obs.date.pretty,
    obs.temp_f,
    obs.precip_hr_in
  ]);
}

Option B: Get Exactly 6 Evenly Spaced Observations #

If you need exactly 6 entries spread out across the day:

function sampleObservations(filteredObservations) {
  // Option B: Get exactly 6 evenly spaced observations
  const numSamples = 6;
  const step = Math.floor(filteredObservations.length / numSamples);
  const sampled = [];
  
  for (let i = 0; i < numSamples; i++) {
    const index = i * step;
    if (index < filteredObservations.length) {
      const obs = filteredObservations[index];
      sampled.push([obs.date.pretty, obs.temp_f, obs.precip_hr_in]);
    }
  }
  
  // Add the last observation to cover the end of the window if needed
  if (sampled.length < numSamples && filteredObservations.length > 0) {
    const lastObs = filteredObservations[filteredObservations.length - 1];
    sampled.push([lastObs.date.pretty, lastObs.temp_f, lastObs.precip_hr_in]);
  }
  
  return sampled;
}

Finally, let’s write the full dataset and your sampled data to separate sheets in your Google Sheet:

function writeToSheet(allData, sampledData) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // Write all data to "Full Data" sheet (create if it doesn't exist)
  let fullSheet = spreadsheet.getSheetByName("Full Data");
  if (!fullSheet) fullSheet = spreadsheet.insertSheet("Full Data");
  
  fullSheet.clearContents();
  const headers = ["Date/Time", "Temperature (°F)", "Hourly Precipitation (in)"];
  fullSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  fullSheet.getRange(2, 1, allData.length, allData[0].length).setValues(allData);
  
  // Write sampled data to "Sampled Data" sheet (create if it doesn't exist)
  let sampledSheet = spreadsheet.getSheetByName("Sampled Data");
  if (!sampledSheet) sampledSheet = spreadsheet.insertSheet("Sampled Data");
  
  sampledSheet.clearContents();
  sampledSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sampledSheet.getRange(2, 1, sampledData.length, sampledData[0].length).setValues(sampledData);
  
  Logger.log("Data imported successfully! Check your sheets.");
}

• Replace placeholders: Don’t forget to swap YOUR_WUNDERGROUND_API_KEY and location with your actual details.
• Units adjustment: If you prefer Celsius, replace obs.temp_f with obs.temp_c and update the header text to match.
• Test first: Run the script and check the logs (View > Logs) to make sure data is being fetched correctly before it writes to your sheet.
• Rate limits: Wunderground has API rate limits—don’t run the script more often than allowed (check their docs for specifics).

• Invalid API key: Double-check your key is correct and active.
• No data returned: Verify your location format (e.g., State/City or zip code) and that yesterday’s date is valid.
• Sheet not updating: Make sure you have edit permissions for the Google Sheet.

内容的提问来源于stack exchange，提问作者p0werenner