Scrape a Website to Google Sheets — IMPORTXML to API

There are three tiers of scraping a website into Google Sheets, and each one exists because the tier below it hit a wall. Tier one is the built-in import formulas — IMPORTHTML, IMPORTXML, IMPORTDATA — free, instant, and limited to static pages. Tier two is Apps Script with UrlFetchApp and a time-driven trigger, which buys you real scheduling and JSON handling. Tier three is a short Python script that calls a scraping API and writes rows with gspread, which is what you reach for when the site fights back. This guide goes deep on all three, with working formulas, complete scripts, and an honest account of where each one breaks. (Need the data in Excel instead? The sister guide, scrape a website to Excel, covers Power Query and the workbook side of the same pipeline.)

Tier 1: The built-in import formulas, properly

Most tutorials show one IMPORTXML example and move on. The built-ins deserve better — used within their limits, they are the fastest scraping tool that exists, because there is nothing to install and nothing to authenticate.

IMPORTHTML — tables and lists by position

IMPORTHTML(url, query, index) grabs the Nth table or list element on a page. For any page whose data already lives in an HTML table — Wikipedia, government statistics, sports standings — this is the whole job:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)", "table", 1)

If the first index returns the wrong table, do not guess — enumerate. Drop this helper in a scratch cell and it labels every table on the page so you can find the right index once:

=INDEX(IMPORTHTML("https://example.com/stats", "table", 2), 1, 1)

Swap the trailing 2 upward until the preview shows the header you want, then point your real formula at that index. Use "list" as the second argument for ul and ol elements the same way.

IMPORTXML — XPath for everything else

IMPORTXML(url, xpath_query) is the precision instrument: it fetches the page and runs an XPath query against the markup, so you can pull exactly one field instead of a whole table. Real queries you will actually use:

=IMPORTXML("https://example.com/product", "//h1")
=IMPORTXML("https://example.com/product", "//span[@class='price']")
=IMPORTXML("https://example.com/blog", "//a/@href")
=IMPORTXML("https://example.com/blog", "//article//h2[contains(@class, 'title')]")
=IMPORTXML("https://example.com", "//meta[@name='description']/@content")

The first pulls the page title, the second a price span by class, the third every link URL on the page, the fourth headings whose class merely contains a value (handy when sites use hashed class names with stable prefixes), and the fifth grabs metadata that never renders visibly at all. XPath attributes come back as plain text, and a query matching multiple nodes spills down the column automatically. An optional third argument sets the parsing locale if the source page's number formats differ from your sheet.

If a query returns nothing, test it in your browser first: open DevTools on the target page and run $x("//span[@class='price']") in the console. If DevTools finds the node but Sheets does not, the element is being created by JavaScript after page load — and you have found the built-ins' hard limit (more below). Writing XPath against real-world markup is the core skill here; our HTML parsing glossary entry covers why page structure is always a moving target.

IMPORTDATA — the underrated one

IMPORTDATA(url) ingests any URL that returns CSV or TSV — no XPath, no indexes, just structured data straight into cells:

=IMPORTDATA("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.zip?format=csv")
=IMPORTDATA("https://data.cityofnewyork.us/api/views/erm2-nwe9/rows.csv?$limit=500")

Government open-data portals, financial reference feeds, and many SaaS export endpoints publish plain CSV URLs, and IMPORTDATA treats them like a native data source. Before you write any XPath, check whether the site quietly offers a CSV endpoint — it is the difference between parsing a page and just reading the answer. (That distinction — parsing rendered pages versus consuming structured endpoints — is the whole theme of web scraping vs API.)

Where all three break

• No JavaScript rendering. The formulas read the raw HTML the server returns on first request. Sites that hydrate their content client-side — Amazon, Google results, most e-commerce and dashboards — hand Sheets an empty shell, so the formula returns #N/A, "Imported content is empty", or nothing.
• Refresh is shallow. Per Google's documentation, IMPORTDATA, IMPORTHTML, and IMPORTXML check for updates roughly every hour, and only while the document is open. A closed sheet never refreshes, and merely reopening it does not force a re-fetch.
• Traffic limits and the stuck "Loading..." cell. Google enforces usage limits on import functions calculated across all of a creator's open documents. The commonly cited practical ceiling is around 50 import formulas per spreadsheet before things degrade, and heavy use produces the infamous cell that says "Loading..." forever — Google's own guidance for the "Loading data may take a while because of the large number of requests" error is simply to use fewer import functions. There is no queue you can inspect and no retry button; the failure mode is silence. Import formulas also refuse to reference volatile functions like NOW() or RANDBETWEEN() (with TODAY() as the sole exception), which kills the classic cache-busting trick.
• Google's fetcher gets blocked. Every import formula fetches from Google's shared infrastructure — the same IP ranges for every Sheets user on earth. Sites with any rate limiting or anti-bot layer have seen those IPs a billion times and block them wholesale, which is why a page that loads fine in your browser can still return #N/A in Sheets even when it is fully static.

Verdict: unbeatable for static tables, CSV feeds, and metadata on cooperative sites. The moment you need a schedule, a JavaScript-rendered page, or a protected site, move up a tier.

Tier 2: Apps Script — UrlFetchApp plus a time-driven trigger

Apps Script is the escalation most spreadsheet users never take, and it fixes two of the four walls for free: real scheduling and real error handling. UrlFetchApp makes HTTP requests with custom headers, and a time-driven trigger re-runs your function on a clock — even when the sheet is closed, which no import formula can do.

Open your sheet, go to Extensions → Apps Script, and paste this complete script. It calls a JSON API (here, Crawlora's Amazon search endpoint — any JSON endpoint works the same way), checks the status code, and rewrites a tab named results:

function refreshSheet() {
  const API_KEY = PropertiesService.getScriptProperties()
    .getProperty("CRAWLORA_API_KEY");
  const url =
    "https://api.crawlora.net/api/v1/amazon/search?k=" +
    encodeURIComponent("mechanical keyboard");

  const resp = UrlFetchApp.fetch(url, {
    headers: { "x-api-key": API_KEY },
    muteHttpExceptions: true, // return the response instead of throwing
  });
  if (resp.getResponseCode() !== 200) {
    console.error("Fetch failed: HTTP " + resp.getResponseCode());
    return;
  }

  const rows = JSON.parse(resp.getContentText()).data;
  const header = ["asin", "title", "price", "rating", "review_count", "link"];
  const values = rows.map((r) =>
    header.map((h) => (r[h] == null ? "" : r[h]))
  );

  const sheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("results");
  sheet.clearContents();
  sheet.getRange(1, 1, 1, header.length).setValues([header]);
  if (values.length) {
    sheet.getRange(2, 1, values.length, header.length).setValues(values);
  }
}

function installTrigger() {
  ScriptApp.newTrigger("refreshSheet").timeBased().everyHours(6).create();
}

Three setup notes. Store the API key under Project Settings → Script properties as CRAWLORA_API_KEY — never paste secrets into cells or code. Run installTrigger() once from the editor (or configure the same thing in the Triggers panel), and Google will call refreshSheet() about every six hours; time-driven triggers behave like cron, though Google randomizes the exact minute within the window to spread load. And keep muteHttpExceptions: true — it turns a failed fetch into a status code you can log and handle instead of a red exception at 3 a.m.

The limits, honestly: Apps Script requests time out after six minutes, URLs are capped at 2,082 characters, consumer accounts get bounded daily UrlFetch and trigger quotas, and — the big one — requests still originate from Google's IP ranges. Against a protected site, Apps Script gets blocked exactly like IMPORTXML did, because to the target it is the same crawler knocking. Apps Script solves scheduling and JSON, not access. Which is precisely why the script above calls an API instead of a raw page.

Tier 3: Python + scraping API + gspread

When the target site renders with JavaScript, rotates its markup, or actively blocks crawlers, stop fetching pages and start fetching answers. A scraping API does the rendering, proxy rotation, and retries on its side and returns the same normalized JSON even when the site redesigns — and gspread turns that JSON into sheet rows in three lines. This is the setup that survives contact with production. (Building the scraper yourself instead is a legitimate path — the web scraping with Python pillar covers the full DIY toolkit — but for a spreadsheet pipeline the API route deletes the maintenance.)

One-time Google setup, five minutes: in Google Cloud Console create a project, enable the Google Sheets API and Google Drive API, create a service account, download its JSON key, and share your spreadsheet with the service account's email (it ends in iam.gserviceaccount.com) exactly as you would share with a colleague. Then:

pip install requests gspread

import requests
import gspread
from datetime import datetime, timezone

API_KEY = "YOUR_API_KEY"  # free tier: 2,000 credits/month, no card

resp = requests.get(
    "https://api.crawlora.net/api/v1/amazon/search",
    params={"k": "mechanical keyboard"},
    headers={"x-api-key": API_KEY},
    timeout=60,
)
resp.raise_for_status()
items = resp.json()["data"]  # normalized result cards

gc = gspread.service_account(filename="service-account.json")
ws = gc.open("Keyboard price tracker").worksheet("log")

stamp = datetime.now(timezone.utc).isoformat(timespec="seconds")
rows = [
    [stamp, it["asin"], it["title"], it["price"], it["rating"], it["link"]]
    for it in items
]
ws.append_rows(rows, value_input_option="USER_ENTERED")
print(f"Appended {len(rows)} rows at {stamp}")

Note the design difference from the Apps Script version: this one appends timestamped rows instead of overwriting, so the sheet becomes a price history you can chart and pivot, not just a snapshot. Swap the k keyword, or swap the endpoint entirely — the platform catalog lists every supported site, the docs specify each endpoint's fields, and the playground lets you fire a test request in the browser before writing a line of code. Because pricing is pay-on-success — you are charged only for successful 2xx responses — a blocked or failed fetch costs a retry, not credits. Schedule it with cron or a GitHub Actions workflow; the Excel sister guide has a complete copy-paste Actions YAML that works unchanged here. And whatever the schedule, keep it polite — a daily pull is crawling done responsibly; a hammering loop is how IPs end up on blocklists.

Which tier should you use?

By persona:

• Analyst with a static table and a deadline: IMPORTHTML, done in sixty seconds. Check for a CSV endpoint first and use IMPORTDATA if one exists.
• Ops person who needs one page refreshed daily in a shared sheet: Apps Script with a time-driven trigger. It is the cheapest genuinely scheduled option, and the script above is the whole thing.
• Anyone scraping dynamic, protected, or many sites: Python + API + gspread. It is the only tier where the blocked-site problem is somebody else's job.
• Team already living in Excel: same pipeline, different last mile — the Excel guide covers Power Query and to_excel().

• Is the data in the initial server HTML? Try IMPORTHTML or IMPORTXML first — test your XPath in DevTools with $x().
• Is there a CSV endpoint? IMPORTDATA beats XPath every time one exists.
• Need refresh while the sheet is closed? Import formulas cannot do it — use an Apps Script time-driven trigger.
• Is the site JavaScript-rendered or blocking Google's fetcher? Route through a scraping API and write rows with gspread.
• Keep API keys in Script properties or environment secrets, never in cells, and keep the fetch cadence polite.

Turn any supported site into a living Google Sheet

One GET request returns normalized JSON — Apps Script or gspread does the rest. 2,000 free credits a month, no card, and pay-on-success: you are charged only for successful 2xx responses.

Try it in the PlaygroundSee the Web Scraping API

Related reading: the Excel sister guide for Power Query and the workbook side of this pipeline, the web scraping with Python pillar for the full DIY toolkit, and web scraping vs API for when each approach wins.