[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
IMPORTXML reads a page that is live on the web, through its URL. It isn’t the way to load an .xml file saved on your computer.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h2>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IMPORTXML(url, xpath_query, locale)<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
- \n
- url<\/b> \u2013 the address of the structured web page from which you want to import data. There are two ways to include this:\n
- \n
- As a fully qualified location in the form of text between quotations (for example, \u201chttps:\/\/en.wikipedia.org\/wiki\/Tiger_Woods\u201d)<\/li>\n
- As a reference to a cell that holds the url.<\/li>\n<\/ul>\n<\/li>\n
- xpath_query<\/b> \u2013 this is the XPath query to run on the structured data. Simply put, this specifies the information we are trying to fetch. For instance, using \u201c\/\/h1\/@id\u201d gives all the id attributes within the anchor <h1> tags on the page. Check out XPath syntax guidelines<\/a> for detailed instructions.<\/li>\n<\/ul>\n
If this is new to you, you may be a bit confused when looking at the xpath_query<\/b> parameter. But it is just as powerful as it is confusing, so we encourage you to look into it.<\/p>\n
[\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”255443″ theme_builder_area=”post_content”]<\/p>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\")
<\/p>\nAutomate data transfers between spreadsheets
<\/span><\/p>\nFind out how<\/a><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
IMPORTXML example<\/h3>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IMPORTXML(url, “all the hyperlink titles in the page”)<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In the example below, I import the data into cell B3, so that\u2019s the destination to include in the formula.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IMPORTXML-1.png” alt=”IMPORTXML Google Sheets \u2014 formula in B3 pulling \/\/a\/@title from the Tiger Woods Wikipedia page, with the syntax help box open” title_text=”Formula in B3 pulling \/\/a\/@title from the Tiger Woods Wikipedia page, with the syntax help box open” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” custom_padding=”||0px|||” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
The formula I used included the url in quotations as previously mentioned. It uses the Xpath query \u201c\/\/a\/@title\u201d to fetch all the hyperlink titles within the article<\/strong>.\u00a0<\/p>\n
Give the formula a try! You will be warned that the spreadsheet is trying to send and receive data, just allow it to get all the information.<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IMPORTXML(<\/b>“<\/b>https:\/\/en.wikipedia.org\/wiki\/Tiger_Woods<\/b>“<\/b>, <\/b>“\/\/a\/@title”<\/b>)<\/b><\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Below is what you\u2019ll see as Google Sheets tries to fetch the data. The higher the number of nodes in the structured data file, the longer it will take to import the data.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/04\/2.-IMPORTXML-loading-data-500×300-1.png” alt=”IMPORTXML Google Sheets \u2014 cell B3 showing Loading… while the data imports” title_text=”Cell B3 showing Loading… while the data imports” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
While the data import is in progress, the cell in which you typed the formula looks like it generated an error.<\/p>\n
However, it isn\u2019t an error, but just a temporary state. This means that the data is loading. Hover your 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\/2021\/04\/3.-IMPORTXML-loading-data-error-500×300-1.png” alt=”IMPORTXML Google Sheets \u2014 Loading data… message in the error box while the import runs” title_text=”Loading data… message in the error box while the import runs” align=”center” _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″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As soon as Google Sheets loads the data, the error disappears. You\u2019ll notice the data extends from the formula cell (B3) to the right and further down.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IMPORTXML-4.png” alt=”IMPORTXML Google Sheets \u2014 hyperlink titles from the Wikipedia page filling column B from B3 down” title_text=”Hyperlink titles from the Wikipedia page filling column B from B3 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.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
This is an auto expanding function<\/strong>, meaning all the values fetched will be included in the cells below it.<\/p>\n
It\u2019s important that you keep the expected area of the result clear<\/strong> of any values. Otherwise, IMPORTXML formula will result in a #REF! error.<\/p>\n
For example, the following image demonstrates what happens if you have any values within that range.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IMPORTXML-5.png” alt=”IMPORTXML Google Sheets \u2014 #REF! error because the result would overwrite data in B9″ title_text=”#REF! error because the result would overwrite data in B9″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=IMPORTXML(url cell reference, \u201call the h2 headers in the page\u201d)[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In this example, in the cell B1, I have stored the url<\/b> to the page from which I am trying to import the header information.<\/p>\n
I\u2019ll use that cell as a reference in the IMPORTXML formula:\u00a0<\/p>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
=IMPORTXML(B1,”\/\/h2″)<\/b><\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IMPORTXML-6.png” alt=”IMPORTXML Google Sheets \u2014 formula with the URL in B1 and \/\/h2 listing the page’s H2 headings in column B” title_text=”Formula with the URL in B1 and \/\/h2 listing the page’s H2 headings in column B” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
As you can see, the data being pulled here is different from the previous example, even though the URL is the same.<\/p>\n
This time, I have a list of the headers in the article. This could be useful for a variety of reasons. If there\u2019s other information that you want to import from the URL, I encourage you to read more about XPath queries<\/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
More XPath examples for IMPORTXML<\/h2>\n
The xpath_query decides what IMPORTXML pulls from the page. These queries cover the most common jobs. Replace the URL with the page you want to read, or point to a cell that holds it.<\/p>\n
Scrape every link on a page<\/h3>\n
=IMPORTXML(\"https:\/\/en.wikipedia.org\/wiki\/Tiger_Woods\", \"\/\/a\/@href\")<\/code><\/p>\nThis returns the address of every link on the page, one per row. Links to other pages on the same site usually come back as relative paths, such as \/wiki\/PGA_Tour. If you use “\/\/@href” instead, you also get the href of other tags, such as stylesheets and icons.<\/p>\n
Scrape only external links<\/h3>\n
=IMPORTXML(\"https:\/\/www.example.com\/\", \"\/\/a[starts-with(@href, 'http') and not(contains(@href, 'example.com'))]\/@href\")<\/code><\/p>\nThe query keeps links that start with http, which leaves out relative paths, and then removes any link that points back to the same domain. Replace example.com with the domain of the page you’re reading.<\/p>\n
Get the canonical URL of a page<\/h3>\n
=IMPORTXML(\"https:\/\/www.example.com\/page\/\", \"\/\/link[@rel='canonical']\/@href\")<\/code><\/p>\nThe canonical link is the version of a page that its owner marks as the preferred one, to avoid duplicate content. This query returns that single URL.<\/p>\n
Other useful queries<\/h3>\n
- \n
\/\/title<\/code> returns the page title.<\/li>\n\/\/meta[@name='description']\/@content<\/code> returns the meta description.<\/li>\n\/\/*[@id='main']<\/code> returns the element whose id is main.<\/li>\n\/\/div[contains(@class, 'price')]<\/code> returns every div with price among its classes.<\/li>\n<\/ul>\nFor data that sits in a table or a list, the IMPORTHTML formula<\/a> is usually simpler.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Importxml could not fetch url<\/h2>\n
Naturally, this error message in Google Sheets usually means that there was a problem fetching the data.<\/p>\n
This error can occur due to various reasons, such as:<\/p>\n
- \n
- Invalid URL<\/b>: Double-check that the URL provided in the IMPORTXML function is correct, including http or https. The page may also have moved to another address.<\/li>\n
- Network issues<\/b>: Sometimes, this error may be the result of temporary network issues. Just wait a bit and try again.<\/li>\n
- Access restrictions<\/b>: The website you’re trying to access may have restrictions in place. If this is the case, there is not much to do unless you can request access.<\/li>\n
- Page structure changes<\/b>: If the function worked before but structure of the page changes, the XPath query may no longer work correctly. Update the XPath query to match the new structure of the webpage.<\/li>\n
- Imported content is empty<\/b>: the formula reached the page but found nothing that matches the query. Check the XPath query, then open the page source in your browser to confirm the data is in the HTML. Pages that load their content with JavaScript show it in the browser, but IMPORTXML only sees the HTML the server sends.<\/li>\n
- The site blocks Google Sheets<\/b>: some sites, including several finance data sites, refuse requests from Google’s fetcher. The page opens in your browser, but the formula still can’t fetch it. No change to the formula fixes this, so use another source or the site’s own API.<\/li>\n
- Too many formulas<\/b>: each IMPORTXML formula sends its own request, and Google Sheets refreshes them about every hour. A file with hundreds of them loads slowly and can hit a site’s limits. A few formulas that each return a whole list work better.<\/li>\n<\/ol>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
A wrap-up on IMPORTXML<\/h2>\n
You can also check out the following blog post to learn how to pull data into Google Sheets from a table or a list on an HTML page with the IMPORTHTML Google Sheets function <\/a>or How to use the IMPORTFEED formula.<\/a> To pull a CSV or TSV file from a URL, use the IMPORTDATA formula<\/a>, and to bring in data from another spreadsheet, use IMPORTRANGE<\/a>.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n
[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
The IMPORTXML Google Sheets function lets you import data from structured data types like HTML and XML. It also supports data import from CSV, TSV, RSS and ATOM XML feeds. This makes it a great tool to import data from websites for web scraping or data mining.IMPORTXML reads a page that is live on the […]<\/p>\n","protected":false},"author":40,"featured_media":33478,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The IMPORTXML<\/a> formula in Google Sheets lets us import data from the structured data types like HTML and XML. It also supports data import from CSV, TSV, and RSS and ATOM XML feeds.\n
Syntax<\/h3>\nIMPORTXML(url, xpath_query)<\/span><\/strong><\/span>\n
- \n \t
- url<\/strong><\/span> - this is the address (uniform resource locator) to the structured page on the web, from which we are trying to import data from.\u00a0This can take two forms.\n
- \n \t
- A valid and fully qualified location in the form of text (enclosed in double quotes). For example: \u201chttps:\/\/en.wikipedia.org\/wiki\/Tiger_Woods\u201d<\/strong><\/li>\n \t
- Or it can be a reference to a cell (like B1<\/strong>) within Google Sheets, where the url is stored.<\/li>\n<\/ul>\n<\/li>\n \t
- xpath_query<\/strong><\/span> - this is the XPath query to run on the structured data. Simply put, this specifies what kind of information we are trying to fetch. For instance, using \"\/\/h1\/@id\"<\/span><\/strong> as xpath_query<\/strong><\/span>, gives us to all the id attributes within the anchor
tags available on the page. For XPath syntax guidelines, check out this link<\/a>.<\/li>\n<\/ul>\n
Usage: IMPORTXML Formula<\/h3>\n
Case # 1: IMPORTXML(url,\"all the hyper link titles in the page\")<\/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 IMPORTXML formula.\n\n
![\"IMPORTXML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importxml1.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 data. Higher the number of nodes in the structured data file, the longer it might take to finish the data import.\n\n![\"IMPORTXML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importxml2.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![\"IMPORTXML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importxml3.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![\"IMPORTXML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importxml4.png\")
\n\nSo, it is of utmost importance that we keep the expected real estate of the result,\u00a0clear of any preoccupied values. Otherwise, IMPORTXML formula might cough up a #REF! error. For example, following image explains what could happen, if we had any values, for instance, in cell B9.\n\n![\"IMPORTXML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importxml5.png\")
\nCase # 2: IMPORTXML(url cell reference,\"all the h2 headers in the page\")<\/h5>\nHere, in the cell B1, we have stored the url<\/strong> to the page from which we are trying to import the header information. So, we\u2019ll use that cell to refer in the IMPORTXML formula.\n\n
![\"IMPORTXML](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/importxml6.png\")
\n\n\u00a0","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-2660","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\/pt\/wp-json\/wp\/v2\/posts\/2660","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=2660"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/2660\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/33478"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=2660"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=2660"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=2660"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} - Or it can be a reference to a cell (like B1<\/strong>) within Google Sheets, where the url is stored.<\/li>\n<\/ul>\n<\/li>\n \t
- A valid and fully qualified location in the form of text (enclosed in double quotes). For example: \u201chttps:\/\/en.wikipedia.org\/wiki\/Tiger_Woods\u201d<\/strong><\/li>\n \t
- Network issues<\/b>: Sometimes, this error may be the result of temporary network issues. Just wait a bit and try again.<\/li>\n