Daily Worldwide Earthquake Log in Google Sheets (USGS)

PromptCreate

Build me a deterministic code workflow that maintains a running spreadsheet log of every magnitude 4.5 or greater earthquake in the world, pulled from the U.S. Geological Survey (USGS) public catalog. There is no reasoning needed. The steps are fixed and the inputs are structured GeoJSON.

Trigger: cron, every day at 07:00 UTC.

Step 1. Pull the last 24 hours of earthquakes from USGS. Call the USGS Earthquake "Query Earthquakes" operation with: starttime = (now minus 24 hours) formatted as ISO 8601, endtime = now formatted as ISO 8601, minmagnitude = 4.5, format = geojson, orderby = time. No location filter. The 24 hour, M4.5+ window keeps the response well under the 20,000 feature FDSN cap (a typical day worldwide is under 100 events).

Step 2. For each GeoJSON feature in the response, call Google Sheets "Append Values" once against a spreadsheet ID and sheet/tab name I will configure. Use valueInputOption = USER_ENTERED so timestamps and numbers parse cleanly in the sheet. Append in this fixed column order:

1. event_time_utc — feature.properties.time is Unix milliseconds; convert to an ISO 8601 UTC string before writing.

2. magnitude — feature.properties.mag

3. magnitude_type — feature.properties.magType

4. place — feature.properties.place

5. latitude — feature.geometry.coordinates[1]

6. longitude — feature.geometry.coordinates[0]

7. depth_km — feature.geometry.coordinates[2]

8. pager_alert — feature.properties.alert (may be null; write empty string)

9. mmi — feature.properties.mmi (may be null)

10. cdi — feature.properties.cdi (may be null)

11. tsunami_flag — feature.properties.tsunami (0 or 1 from USGS)

12. event_id — feature.id

13. usgs_event_url — feature.properties.url

The spreadsheet has a fixed header row matching those column names; the workflow appends rows after that header. If the response contains zero features, the workflow ends cleanly with no rows written. Do not call Slack or send any email. The only output is one appended row per event into the configured Google Sheet tab.

Important details so the agent does not get this wrong:

- USGS GeoJSON coordinates are ordered [longitude, latitude, depth_km], not [latitude, longitude]. Do not flip them.

- USGS returns time and updated as Unix milliseconds. Convert milliseconds to ISO 8601 UTC before writing event_time_utc.

- USGS Earthquake is a no-auth public API. The only credential the workflow needs is Google Sheets.

- This is the deterministic counterpart to the agent-based earthquake alerting prompt. Use a code workflow with discrete nodes, not an agent.

What does this prompt do?

Pulls every magnitude 4.5 or greater earthquake from around the world over the last 24 hours, straight from the official USGS catalog.
Appends one tidy row per event to your spreadsheet with time, magnitude, place, latitude, longitude, depth, and impact fields already split into columns.
Runs every morning at 7am UTC, so analysts and researchers wake up to a clean, growing log instead of scraping the USGS feed by hand.
Builds a permanent history you can pivot, chart, filter, and join with other datasets over months and years.

What do I need to use this?

A Google account with edit access to the spreadsheet you want to log into.
A Google Sheet with a dedicated tab and a header row matching the column order in the prompt.
No USGS account is required. The earthquake catalog is a free, public data source.

How can I customize it?

Raise or lower the magnitude floor. Drop to 2.5 for a small region, or push to 5.5 to only catch the bigger events.
Change the schedule. Run it hourly, every six hours, or once a week instead of daily.
Filter to a region. Add a city center with a radius in kilometers, or a latitude and longitude bounding box, so only nearby quakes get logged.
Swap the column layout. Add or remove fields like felt reports, tsunami flag, or alert color to match how your analysts want to slice the data.

FAQs

Where does the earthquake data come from?

The U.S. Geological Survey's public earthquake catalog. It is the same source professional seismologists, USGS dashboards, and major news outlets pull from.

How many rows will this add per day?

Usually well under a hundred. Worldwide, magnitude 4.5 and above averages roughly 30 to 50 events per day, so the spreadsheet grows steadily without ever blowing up.

Can I track only quakes near a specific city or region?

Yes. Tell the workflow to filter by a center point with a radius in kilometers, or by a latitude and longitude bounding box, and only matching events get appended.

What happens if the workflow misses a day?

The 24 hour window picks up whatever happened in the last day. After an outage you can re-run the workflow with a wider window to backfill, and old events that already landed can be deduplicated by event ID inside the sheet.

Do I need to pay for the USGS earthquake data?

No. The USGS catalog is free and open. The only account you need to connect is your Google account for the spreadsheet.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.