[\/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<\/span>)<\/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 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.<\/span><\/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 Automate data transfers between spreadsheets Find 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 [\/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.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IMPORTXML-1.png” alt=”IMPORTXML 1″ title_text=”IMPORTXML 1″ _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<\/span><\/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.<\/span><\/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.<\/span><\/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 2″ title_text=”2. IMPORTXML loading data 500×300″ 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.<\/span><\/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.<\/span><\/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 3″ title_text=”3. IMPORTXML loading data error 500×300″ 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 4″ title_text=”IMPORTXML 4″ _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.<\/span><\/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 5″ title_text=”IMPORTXML 5″ _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,\u201d\/\/h2\u2033)<\/b><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/10\/IMPORTXML-6.png” alt=”IMPORTXML 6″ title_text=”IMPORTXML 6″ _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.<\/span><\/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 <\/span>XPath queries<\/span><\/a>.<\/span><\/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 Naturally, this error message in Google Sheets usually means that there was a problem fetching the data.<\/span><\/p>\n This error can occur due to various reasons, such as:<\/span><\/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 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 <\/span>IMPORTHTML Google Sheets function <\/span><\/a>or <\/span>How to use the IMPORTFEED formula.<\/span><\/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.Syntax=IMPORTXML(url, xpath_query, locale) url \u2013 the address of the […]<\/p>\n","protected":false},"author":40,"featured_media":33478,"comment_status":"open","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\n
\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\"){kind=icon}
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
<\/span><\/p>\nIMPORTXML example<\/h3>\n
Importxml could not fetch url<\/span><\/h2>\n
\n
A wrap-up on IMPORTXML<\/span><\/h2>\n
Syntax<\/h3>\nIMPORTXML(url, xpath_query)<\/span><\/strong><\/span>\n
\n \t
\n \t
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":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2660","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=2660"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/2660\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/33478"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=2660"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=2660"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=2660"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}