Copy-pasting tabular data from the internet into a spreadsheet can generate formatting issues. For this reason, IMPORTHTML is an easier way to import data from sites like Wikipedia directly into your spreadsheet while retaining the correct layout. In addition, this formula ensures your data is always up-to-date<\/strong>. Google Sheets refreshes the formula on its own, so when the website changes, your sheet catches up within about an hour. Here’s how to use the formula.<\/p>\n IMPORTHTML(url, query, index)<\/strong><\/p>\n IMPORTHTML returns the table or list starting in the formula cell and fills the cells to the right and below, so leave that area empty. For example, =IMPORTHTML(B1, “table”, 2) imports the second table on the page whose URL is in cell B1.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Take a look at the screenshot below. I want to import data into cell B3, so this is where I enter my IMPORTHTML formula.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/importhtml-google-sheets-1.png” alt=”IMPORTHTML Google Sheets \u2014 IMPORTHTML formula entered in cell B3″ title_text=”IMPORTHTML formula entered in cell B3″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Make sure that the\u00a0url\u00a0is valid, i.e. enclose the url\u00a0with\u00a0double quotes<\/strong>\u00a0and\u00a0hit\u00a0Enter<\/strong>.<\/p>\n While Google Sheets fetches the table data, it displays a Loading<\/strong> notification. Wait a few moments. The bigger the table, the longer it might take to finish the data import.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importhtml2.png” alt=”Importhtml Google Sheets: Loading Data” title_text=”importhtml-data-loading” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n While the data import is in progress, cell B3 looks like it has returned an error. See the little red speck on the top right hand corner of the cell. However, it isn\u2019t an error, just a\u00a0transient state<\/strong>. Hover the mouse over the cell to see the following description:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importhtml3.png” alt=”Importhtml Google Sheets: Snapshot of Error” title_text=”error-message” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As soon as Google Sheets has finished loading the data, the red speck disappears. You\u2019ll notice the data extends from the formula cell (B3) to the right and further down the spreadsheet.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/importhtml-google-sheets-4.png” alt=”IMPORTHTML Google Sheets \u2014 imported table filling the cells from B3 to the right and down” title_text=”Imported table filling the cells from B3 to the right and down” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Make sure you leave enough room in the spreadsheet for the entire table of data. If you don’t, IMPORTHTML formula might cough up a #REF! error. Take a look at the following image to see what happens when I have data in cell\u00a0C9.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/importhtml-google-sheets-5.png” alt=”IMPORTHTML Google Sheets \u2014 #REF! error because cell C9 already holds data” title_text=”#REF! error because cell C9 already holds data” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this example, I have stored the url in cell B1. This is the\u00a0url<\/strong>\u00a0to the HTML page containing the list we want to import. I\u2019ll refer to that cell in the IMPORTHTML Google Sheets formula, which I enter into cell B3. The result is the same except this time I want a list instead of a table.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/importhtml-google-sheets-6.png” alt=”IMPORTHTML Google Sheets \u2014 list imported with the URL stored in cell B1″ title_text=”List imported with the URL stored in cell B1″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The index counts tables and lists in the order they appear in the page’s HTML. Tables and lists are counted separately, and the count starts at 1. On a short page, try 1, 2, 3 and so on until the right data appears.<\/p>\n On a page with many tables or lists, use your browser’s developer console to see the numbers:<\/p>\n The Console shows the page after its scripts have run. If a table appears there but IMPORTHTML can’t find it, the page probably builds it with JavaScript. See the next section.<\/p>\n [\/et_pb_text][et_pb_text module_id=”google-sheets-importhtml-not-working” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When IMPORTHTML fails, the cell shows an error. Click the cell and hover over the red triangle in its corner to read the message. These are the usual causes.<\/p>\n Google’s servers fetch the page, not your browser. If the page needs a login, sits on a company network, or blocks automated visitors, the fetch fails. Check the address too. It must start with http:\/\/ or https:\/\/, and the page may have moved since you wrote the formula. To test, open the URL in a private browser window. If you have to sign in there, IMPORTHTML can’t read the page either.<\/p>\n IMPORTHTML reads the HTML that the server sends. It doesn’t run the page’s scripts. Many sites build their tables with JavaScript after the page loads, so the table you see in your browser isn’t in that HTML, and IMPORTHTML returns an error saying the imported content is empty. To check, right-click the page, choose View page source, and search for a value from the table. If it isn’t there, look for a CSV download on the site and import that file with IMPORTDATA<\/a> instead.<\/p>\n If the formula returns a different table than you expected, the index is off. Sites change their layout, and a new table near the top of a page shifts every number after it. Find the index again with the steps above. An index higher than the number of tables or lists on the page returns an error.<\/p>\n IMPORTHTML needs empty cells for the whole result. If any cell in that range already holds a value, you get #REF!, as shown in Option 1 above. Clear those cells or move the formula.<\/p>\n Google Sheets recalculates IMPORTHTML on its own about every hour, so the cell can lag behind the website. To force a refresh, edit the formula and press Enter.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n IMPORTHTML only reads tables and lists. Google Sheets has other import functions for other sources:<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Looking for more timesaving formulas and tricks in Google Sheets? If you regularly need to import data from\u00a0.csv or .tsv files from the web, see how to use the IMPORTDATA formula in Google Sheets<\/a>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To combine data from many spreadsheets, Excel files, and other sources and keep it updated on a schedule, Sheetgo<\/a>, an AI-powered data automation platform, connects them without formulas.<\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" If you want to\u00a0pull online data into Google Sheets from a website,\u00a0IMPORTHTML\u00a0Google Sheets formula is a great tool to have up your sleeve. It imports data from a table or list on any webpage without you having to copy-paste or adjust the data using other formulas. Copy-pasting tabular data from the internet into a spreadsheet […]<\/p>\n","protected":false},"author":40,"featured_media":4230,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"If we need to pull data into Google Sheets from a table or a list on an HTML page, IMPORTHTML<\/a> formula is the one to go with.\nIMPORTHTML syntax<\/h2>\n
\n
\n
How to import data from a webpage into Google Sheets<\/h2>\n
Option 1: IMPORTHTML(url, \u201ctable\u201d, index)<\/h3>\n
Option 2: IMPORTHTML(url cell reference, \u201clist\u201d, index)<\/h3>\n
How to find the index of a table or list<\/h2>\n
\n
var i = 1; document.querySelectorAll(\"table\").forEach(function (el) { console.log(i++, el); });<\/code><\/li>\nGoogle Sheets IMPORTHTML not working<\/h2>\n
#N\/A error: could not fetch the URL<\/h3>\n
The table loads with JavaScript<\/h3>\n
Wrong data: the index has changed<\/h3>\n
#REF! error: the result would overwrite data<\/h3>\n
The data doesn’t refresh<\/h3>\n
Other ways to import data into Google Sheets<\/h2>\n
\n
Syntax<\/h3>\nIMPORTHTML(url, query, index)<\/strong><\/span>\n
\n \t
\n \t
Usage: IMPORTHTML Formula<\/h3>\n
Case # 1: IMPORTHTML(url, \"table\", index)<\/h5>\nIn the\u00a0snapshot below, we are trying to import the data into the cell B3, which therefore becomes the destination to key in the IMPORTHTML formula.\n\n
![\"IMPORTHTML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importhtml1.png\")
\n\nWe ensure the url<\/strong><\/span>\u00a0is valid, i.e. enclose url<\/strong><\/span> within double quotes and\u00a0hit the Enter key. Below\u00a0is what we see as Google Sheets tries to fetch the table data. Bigger the table, the longer it might take to finish the data import.\n\n![\"IMPORTHTML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importhtml2.png\")
\n\nWhile the data import is in progress, the cell in which we keyed in the formula, looks like it\u00a0returned an error. Please note a little red speck on the top right corner of the cell. But, it isn\u2019t an error, just a transient state. Hover the mouse on the cell, to see the following description.\n\n![\"IMPORTHTML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importhtml3.png\")
\n\nAs soon as Google Sheets loads the data, the red speck disappears. You\u2019ll notice the data extends from the formula cell (B3) to right and also further down.\n\n![\"IMPORTHTML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importhtml4.png\")
\n\nSo, it is of utmost importance that we keep the expected real estate of the result,\u00a0clear of any preoccupied values. Otherwise, IMPORTHTML formula might cough up a #REF! error. For example, following image explains what could happen, if we had any values, for instance, in cell\u00a0C9.\n\n![\"IMPORTHTML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importhtml5.png\")
\nCase # 2: IMPORTDATA(url cell reference, \"list\", index)<\/h5>\nHere, in the cell B1, we have stored the url<\/strong> to the HTML page from which we are trying to import a list. So, we\u2019ll use that cell to refer in the IMPORTHTML formula. And, the result is same as that of\u00a0the first case. Except, now the result is a list instead of a table.\n\n
![\"IMPORTHTML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importhtml6.png\")
","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-2649","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2649","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=2649"}],"version-history":[{"count":1,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2649\/revisions"}],"predecessor-version":[{"id":266560,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2649\/revisions\/266560"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/4230"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=2649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=2649"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=2649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}