How to parse NBM text data in Google Sheets

Writing simple code, for those of us who know nothing of actually writing code from scratch, is now a much more doable task thanks to generative AI. Instead of clawing my way through Reddit and Stack Overflow and hoping I can cobble something together, I can just ask ChatGPT for assistance. And that’s what I did when trying to parse NBM text data.

I use this site to view the text data, but it seems to use scripting that loads the data on-demand and therefore isn’t able to be parsed. So, I had to go to the bulk text files… which have a URL that changes with the date, and are large enough that Sheets kinda chokes on them.

Before the code and instructions, here are two notes:

1. This code came from ChatGPT. I won’t take credit for it, I can’t tell you how it assembled its answers, and I’m sure there are other (and possibly more efficient) ways of writing the code to accomplish this task.
2. When asking ChatGPT (or any other generative AI) for assistance with complicated tasks, it is much more useful to make your requests step by step, rather than asking for everything all at once. Here are some prompts I used:
   • It works, but I need help with data cleanup and formatting so it’s delimited for parsing.
   • Great! We’re getting there. In the raw data, the source uses the | character to separate values. Here’s an example of the whole chunk of data in its raw form:
     KCMH NBM V4.2 NBE GUIDANCE 10/29/2024 1800 UTC
     WED 30 |THU 31 |FRI 01 |SAT 02 |SUN 03 |MON 04 |TUE 05 |WED 06 CLIMO
     Can we remove extraneous spaces and | characters?
   • It looks like the data displayed in both sheets of my spreadsheet, rather than just the one I want it to. Can I force it to only put the data in one sheet?

Without further ado, here’s the code I’m using. In Google Sheets, go to Extensions > Apps Script. You’ll see a fairly empty slate with a little starter code; remove that and paste in the code that’s below. I’ve bolded three spots you will need to change.

function fetchData() {
const url = “https://nomads.ncep.noaa.gov/pub/data/nccf/com/blend/prod/blend.” +
Utilities.formatDate(new Date(), Session.getScriptTimeZone(), “yyyyMMdd”) +
“/18/text/blend_nbetx.t18z”;
const response = UrlFetchApp.fetch(url);
const data = response.getContentText();

// Log the URL and fetched data to see its structure
Logger.log(url);
Logger.log(data);

// Split the data into lines
const lines = data.split(‘\n’);
const results = [];
let capture = false;

// Loop through the lines to find and capture the desired chunk
for (const line of lines) {
if (line.startsWith(” KCMH“)) {
capture = true; // Start capturing
}
if (capture) {
// Replace “|” with whitespace, trim and split line by whitespace to get each value
const cleanedData = line.replace(/\|/g, ‘ ‘).trim().split(/\s+/);

// Convert all values to strings and format the third row to replace 0 with 00z and 12 with 12z
if (results.length === 2) {
for (let i = 0; i < cleanedData.length; i++) {
let value = String(cleanedData[i]); // Ensure each value is a string
if (value === ’00’) {
cleanedData[i] = ’00Z’;
} else if (value === ’12’) {
cleanedData[i] = ’12Z’;
}
}
}

results.push(cleanedData);
}
if (results.length === 30) { // Stop after capturing 30 lines
break;
}
}

// Ensure all rows have the same length
const maxColumns = Math.max(…results.map(row => row.length));
const paddedResults = results.map(row => {
while (row.length < maxColumns) {
row.push(“”); // Add empty strings to pad rows to maxColumns length
}
return row;
});

// Check if we have results before writing to the sheet
if (paddedResults.length > 0) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“RawImport“);
sheet.getRange(1, 1, paddedResults.length, maxColumns).setValues(paddedResults);
} else {
Logger.log(“No data found starting with ‘KCMH’.”);
}
}

1. Toward the beginning, I’ve highlighted 18. It’s the 18z run I’m pulling. If you want a different one, change that.
2. Near the middle, I’ve highlighted KCMH. That’s my city’s code. You’ll need to change it to yours. Note the leading space – leave that there!
3. At the end, I’ve highlighted RawImport. That’s the name of my tab/sheet. Make sure you use a name that matches whatever you’ve named the tab/sheet where the data will go.

When you’ve made these adjustments, hit the “Run” button. You should see data populate in your sheet and look something like this:

Since you probably don’t want to manually run this, you can set up a timer. Go back to Extensions > Apps Script and, on the left side, click Triggers (stopwatch icon). Hit the Add Trigger button and make sure you’re running the right function. You’ll need to change the Event Source to “Time-Driven,” and if you want it to run once a day, pick Day timer and then select the hour that you want it to go.

Feel free to make another sheet that references the cells in your raw imported data so you can organize it in a way that makes sense for you.

And if you’re feeling really fancy, you can use ChatGPT to create code that can pull in NDFD data… and do something like this:

This might be helpful if you have several cities that you want to create a customized forecast for, but don’t have a lot of time to get into nitty gritty details.