How to copy a table from a website into Google Sheets

Sheets can take a table from a web page by paste or by formula. Which one fits depends on whether you need the data once or kept up to date.

Paste it once

  1. Select the table on the page and copy it.
  2. Click cell A1 in Sheets and paste.
  3. If everything landed in one column, select it and use Data, then Split text to columns.

Keep it up to date

Use =IMPORTHTML("page address", "table", 1). The number picks the table, counting from 1. Sheets refreshes it about once an hour. It cannot read pages that need you to sign in, or tables that a script draws after the page loads. Those show up as an error or an empty result.

When the table is not a real table

Many sites draw grids with div elements. Copy and IMPORTHTML both miss those. A table copier extension such as Table to CSV & Excel – Table Copier reads grids built from rows and columns of div elements as well, and gives you tab-separated text that pastes into Sheets cleanly.

Common problems

Dates turn into numbers or swap day and month

Set the destination columns to Plain text before pasting, then convert them on purpose with Format, then Number, then Date.

Merged cells leave gaps

Fill the blanks down with a formula such as =IF(A3="",A2,A3) in a helper column.

Extra spaces around values

Use =TRIM(A2) in a helper column and paste the values back.

Related: How to export an HTML table to a CSV file.