DEV Community

Loomwise
Loomwise

Posted on

No-Code Lead List: Pull New Contractor Businesses into Google Sheets with One Formula (State Open Data)

#nocode #opendata #googlesheets #leadgen

You don't need Python to use government open data. Most state and city data portals run on Socrata, and every Socrata dataset can return results as CSV from a plain URL. Google Sheets can load any CSV URL with IMPORTDATA. Put the two together and you get a lead list that refreshes itself, with no code, no add-ons and no API key.

This post builds two live sheets:

  • New contractor businesses licensed in Washington State in the last 7 days. On Oct 22, 2026, the query returned 190 businesses.
  • New contractor businesses registered in New York State in the last 7 days. On Oct 22, 2026, it returned 185 businesses.

Step 1: Washington new contractors

Open a blank Google Sheet, click cell A1, and paste this formula:

=IMPORTDATA("https://data.wa.gov/resource/m8qx-ubtq.csv?$query="&ENCODEURL("SELECT businessname, specialtycode1desc, city, zip, licenseeffectivedate WHERE licenseeffectivedate >= '"&TEXT(TODAY()-7,"yyyy-mm-dd")&"' AND businesstypecodedesc != 'Individual' ORDER BY licenseeffectivedate DESC LIMIT 2000"))
Enter fullscreen mode Exit fullscreen mode

What it does:

  • m8qx-ubtq is the WA L&I contractor license dataset.
  • $query= takes a SQL-like query (SoQL). ENCODEURL makes it safe to put in a URL.
  • TODAY()-7 makes the window roll forward on its own, so the list is always "the last 7 days".
  • businesstypecodedesc != 'Individual' keeps it to companies (LLCs, corporations, partnerships) and leaves out sole proprietors.

Step 2: New York new contractors

In a second tab, paste this formula into A1:

=IMPORTDATA("https://data.ny.gov/resource/i4jv-zkey.csv?$query="&ENCODEURL("SELECT business_name, business_type, city, zip_code, issued_date, business_is_mwbe_owned WHERE issued_date >= '"&TEXT(TODAY()-7,"yyyy-mm-dd")&"' AND business_type != 'Sole Proprietorship' ORDER BY issued_date DESC LIMIT 2000"))
Enter fullscreen mode Exit fullscreen mode

This is the NYS Contractor Registry. The business_is_mwbe_owned column tells you which firms are minority- or women-owned.

Step 3: Filter it to your territory

Add a third tab and use FILTER on the imported data. For example, Washington HVAC businesses only:

=FILTER(WA!A:E, REGEXMATCH(WA!B:B, "HVAC"))
Enter fullscreen mode Exit fullscreen mode

Or New York firms in Brooklyn ZIP codes (112xx):

=FILTER(NY!A:F, LEFT(NY!D:D, 3) = "112")
Enter fullscreen mode Exit fullscreen mode

Three things that will trip you up

  1. IMPORTDATA refreshes about once an hour while the sheet is open, not in real time. That's fine here, since the state data updates daily.
  2. Exact column names matter. Every Socrata dataset lists its column API names: open the dataset page, click "Data dictionary" or "API", and copy the field names exactly.
  3. Keep the LIMIT. Socrata returns 1,000 rows by default if you don't set one. 2,000 is plenty for a 7-day window.

Where to go next

  • Swap in any Socrata dataset: building permits, business licenses, restaurant inspections. Only the domain, dataset ID and column names change.
  • Want a weekly snapshot instead of a live view? Copy the tab and choose Paste special → Values only each Monday.
  • Need Excel instead? Use Data → From Web with the same URL (build the encoded URL once in Sheets and copy it).

Optional: if you'd rather get a scheduled export with more states (Oregon, Connecticut, New York, Washington) and extra filters, I run these as hosted searches, for example New WA Contractors This Week (ready-made search) (paid per result). Disclosure: it's my tool. The formulas above do the same job for free.

Top comments (0)