IMPORTXML in Google Sheets: Pull Website Data into Spreadsheets

The Google Sheets IMPORTXML allows you to pull data in from other websites. The function imports data from various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
The syntax of the formula requires a URL and xpath_query to run on the structured data.
As a note: if you're having issues, many websites have anti-scraping measures or are dynamically generated by javascript which block this function from working.
► Follow along with the full step-by-step tutorial on how to use IMPORTXML in Google Sheets: coefficient.io...
Video Timeline
0:00: Introduction to IMPORTXML
0:19: Why You Might Be Having IMPORTXML Issues
0:51: Pulling Data with the IMPORTXML Formula
4:00: Using Inspect to Copy XML Path
► Come see us: www.coefficien...
Any questions? Comment below; we have Google Sheets experts on the other side.
#googlesheets #googlesheetstutorial #xml #importxml #coefficient

[@jimd1617]

thank you 🤝

[@chrisder1814]

yes I tried the import XML function but it doesn't work, do I need to create an API key first?

[@coefficientio]

You don't need an IP key. But if the website you're trying to pull from has anti-scraping mechanisms or is dynamically generated, you won't be able to use the function.

[@mmaammn]

Is it possible to just scrape a specific word which is also a link on the website over to sheets? when i try it keeps saying imported data is empty

[@coefficientio]

Hi @mmaammmn, this is indeed possible, but it would depend on the website's structure and how links are embedded in the page.
If we are currently in coefficient.io/, and want to return the 'See all templates' text including its hyperlink at the bottom of the page, we need to use an additional function called HYPERLINK. Following the provided tutorial, we use the formula =IMPORTXML("coefficient.io","//*[@id='templates-slider-block_b70956d40a25cf3789739f376b8db14f']/div[2]/div[3]/a") to simply extract the 'See all templates' text. Now, to get its corresponding link, we use =IMPORTXML("coefficient.io","//a/@href[contains(.,'/templates/' )]",).
This will instead however display coefficient.io/templates/, and not the intended text. This is where HYPERLINK comes into play. By combining the two formulas inside the said function, we arrive with: =HYPERLINK(IMPORTXML("coefficient.io","//a/@href[contains(.,'/templates/' )]",),IMPORTXML("coefficient.io","//*[@id='templates-slider-block_b70956d40a25cf3789739f376b8db14f']/div[2]/div[3]/a")) formula that displays the 'See all templates' text with its embedded link.

[@dylanhuston1446]

what do you do if there is no option that lets you copy the full Xpath? Im on MacBook Air and it only allows for "copy Xpath"

[@coefficientio]

You can in fact make use of 'Copy Xpath' when building a formula with =IMPORTXML() function. For more details, you may refer here: coefficient.io/how-to-use-importxml-in-google-sheets

[@giangpham-wp5nk]

Hello ! i want to import tronscan to google sheet, i’m using importxml but cell recognised ,plz help me. thanks u

[@coefficientio]

Hi there, normally you could import from that website with a formula that looks like this: =IMPORTXML("tronscan.org/#/address/TWu5qJG2ZyQJ1Xxjv3UHLg7VgTS7p5fQ9Y","//div[@class='col-md-6 col-sm-12 col-xs-12']")
Unfortunately however, some websites like tronscan make their data inaccessible to spreadsheet functions such as IMPORTXML.

[@13mowe]

I am using importxml and pulling prices from the web but the cell is not recognised as a number when I sum it with another cell. 🤔 any work around or is it something to do with where it has come from ?

[@coefficientio]

Without seeing your data it's hard to tell, James. You could use GPTX_Format() within the Coefficient Google Sheets extension to convert the text into number format.

[@theDemolisher13]

Question how would I import price data from a storefront site while they have a size & quantity options that could effect the price?

[@coefficientio]

Hi there, this all depends on the website you are importing from and may be quite difficult or impossible on some websites. On my ecommerce websites, the URL will change every time you select a different size of product. If that's the case, you may be able to have multiple IMPORTXML functions to import the price for each size.

[@theDemolisher13]

@@coefficientio Thank you for taking the time to reply to my question. From what you told me it sounds like this method isn't possible for what I am trying to do. As a few of the web stores I'm trying to pull from don't change their URL when selecting different options but seem to just pull the price info from another database from elsewhere. Or that's what my limited knowledge would tells me.

[@cgc2300]

hello I'm selling Amazon, do you think I could retrieve my sales history in a Google sheet?

[@coefficientio]

We don't currently have an Amazon connector, but we are in progress on working on a custom API connector within Coefficient which would then provide you access to this data.
If you click on the Custom API on this page and submit you're email, we'll let you know as soon as it's available!
www.notion.so/coefficient/Monthly-Planning-f8d788dbd8444d0f9b191c2c9d9cf136?pvs=4#68c78da0fb4c41f49d9f382527f2a955

[@chrisder1814]

hello I have two ideas, could this allow me to recover the data from my Amazon seller account and recover the titles of certain public UA-cam videos?
new:
Do you use no code software like make?
In fact I'm asking myself questions to try to understand the differences between custom GPT, chrome and WordPress extensions, automation software like browser automation studio and make
I had an idea of ​​creating an SEO and image editing but I don't know how to go about it I thought of several solutions either with make or by creating a Chrome or WordPress plugin or by using a existing plugin but I don't think it exists to do what I want or even with a custom GPT
seo:
OK and what do you think about rank math?
I understand that this extension allows me to scan my pages and find the keywords to replace, from my back end, have I understood correctly?
And I also understood that this does not allow me to ask it to scan my entire website?
I mean if I understood correctly it scans the pages but one by one and I have to do everything by hand?
This is why I wondered if I could not create an extension to which I would give the URL of my website and have it scan all the pages and tell me which keywords to replace
And I even wondered if I wouldn't be able to make a robot with custom GPT or even with no code software like make in the case where an extension wouldn't be able to allow me to do it, does this idea seem good to you?
do you also create chrome or WordPress extensions?
I'm asking you this question because I'm trying to understand what I could do with custom GPT, could I do more with a chrome WordPress extension? Does it exist or not and finally what more can I do with it? make compared to its other methods
Amazon, jungle scout
I would like to use the jungle scout api but at the moment I am looking into the subject of artificial intelligence and I realized that there are several ways to use APIs and so I would like to try first to understand what is the difference between using the jungle scout api in a dent custom gpt action and I realized that I can also use the JS api in a make scenario
Could you tell me what the difference is, what could I get, using either of these two methods?
And also with the Amazon API?
I understand that Amazon has several APIs?
Do you also sell on several marketplaces?
could you give me your opinion
could I send you an email

[@coefficientio]

you may be able to - here's a video that might help: ua-cam.com/video/BLa9HKGos8U/v-deo.html

[@chrisder1814]

@@coefficientio doesn't work

[@chrisder1814]

​@coefficientio yes before writing to you and I tried this function but it doesn't work
I understand that I have to create an API key if I want to import titles from public videos?

[@coefficientio]

@@chrisder1814 we are working on a scraper but it isn't live yet. We do have a UA-cam API connection but that's if you'd like to pull information from an owned accounts. For public UA-cam videos that you don't own, you'd need a scraper.

[@chrisder1814]

@coefficientio ok thank you very much for your answer but then could you explain to me why by creating an API I can only scrape the information (title no, number of views...) from my own videos
I mean I don't understand why I can't retrieve public information, I'm not doing anything illegal? since it's public
and also could you explain to me what is the difference between app script and Google sheet add on: API connector
can you also explain to me how the import xml function of Google sheet works?
I mean does it work with an API system? I don't think so since it doesn't require any API
and finally I wanted to ask you if you know the Amazon SP API?
I would like to retrieve information from my Amazon seller account using their API, could it work with the Google sheet API connector?