How To Use Google Sheets For Web Scraping With AI

Scraping knowledge from webpages is a comparatively superior activity that, till just lately, required a level of technical talent. The concept of diving into code or scripts for knowledge extraction appeared overwhelming for a lot of, myself included.

Knowledge scraping can energy many SEO tasks, equivalent to auditing, competitor evaluation, and analyzing web site and knowledge construction.

Google sheets presents simple solutions to assist.

A type of options is the IMPORTXML perform that permits customers to scrape webpage knowledge utilizing just some parameters. It makes knowledge extraction accessible to a wider viewers, particularly to those that weren’t well-versed in programming languages.

Whereas this perform is spectacular, the actual breakthrough got here with the adoption and integration of generative AI into the combination.

On this information, we’ll present you the best way to use Google Sheets and AI, notably ChatGPT, for net scraping with no need superior coding abilities.

The Instruments: AI And Chatbots

We are actually all accustomed to AI, ChatGPT, and similar chatbots.

In actual fact, many people use options like ChatGPT to put in writing our personal code, scripts, and applications with or without very restricted programming data.

It is so simple as offering detailed directions within the type of prompts and dealing with the chatbot to construct instruments that solely till just lately we believed have been approach above us.

However most significantly, these are instruments which are deeply altering the best way we strategy our day-to-day work.

For instance, if we ask ChatGPT the next query, “What’s the IMPORTXML perform and the way can I exploit it in Google Sheets to scrape the title of an HTML webpage? Present the required code to try this in Google Sheets,” the response is extraordinarily correct. In a matter of seconds, we’ve got our method prepared to make use of in Google Sheets.

However to be sincere, that was a really primary and easy activity that we may have simply accomplished with out ChatGPT.

The Process

So, how does this work if we need to extract knowledge that may be a bit much less customary in comparison with a web page title or description?

For instance, how does this work if we need to extract the next knowledge from the PPC entrance web page of Search Engine Journal?

Record all featured articles, their authors, the hyperlink URLs, and the article description for the columns listed on https://www.searchenginejournal.com/class/paid-media/pay-per-click/.

Can we do this instantly with ChatGPT?

Executing With ChatGPT

When creating prompts, it took a number of makes an attempt to supply directions that have been detailed sufficient for the chatbot to completely perceive the target of the duty and return good outcomes.

In lots of circumstances, it felt just like the AI was beneath stress to return fast outcomes regardless of their accuracy.

However let me clarify.

The duty was to research the web page and record all featured articles, their authors, the hyperlink URLs, and the outline for every of the 30 articles listed on the web page. Then compile the info right into a desk and at last export it right into a CSV file.

Easy proper?

At first, ChatGPT returned only a pattern of seven articles and solely their titles and URLs; after a reworked immediate, it managed to record and export all 30 articles and their hyperlinks.

Now, that was good. So, to finish the duty, we simply wanted so as to add the authors and the article descriptions.

However right here is the place the bot stumbled and was not in a position to present an correct description of every article regardless of us offering examples of the web page aspect it wanted to seek out and duplicate.

ChatGPT stored ignoring the directions and offering its personal article descriptions time and time once more.

ChatGPT even failed once we tried with a unique strategy and downloaded and uploaded a replica of the web page HTML.

This time, it was in a position to present correct knowledge for seven articles however couldn’t go previous that. The difficulty reported:

“…the construction and content material of the web page current vital challenges for complete knowledge extraction in a single session.

The web page is kind of intensive and complicated, and it’s not possible to extract all 30 articles within the present format of interplay.”

ChatGPT + Google Sheets

So, going again to IMPORTXML and Google Sheets.

This time, getting ChatGPT to supply the formulation for every discipline was like a breeze.

Listed below are a few of the formulation, as urged by the chatbot, that you would be able to simply attempt your self in Google Sheets to extract:

Title

=IMPORTXML("https://www.searchenginejournal.com/class/paid-media/pay-per-click/", "//*[@id='archives-wrapper']/article/div/div[2]/h2/a")

Writer Identify

=IMPORTXML("https://www.searchenginejournal.com/class/paid-media/pay-per-click/", "//*[@id='archives-wrapper']/article/div/div[2]/p[1]/a")

URL Hyperlink

=IMPORTXML("https://www.searchenginejournal.com/class/paid-media/pay-per-click/", "//*[@id='archives-wrapper']/article/div/div[2]/h2/a/@href")

Description

=IMPORTXML("https://www.searchenginejournal.com/class/paid-media/pay-per-click/", "//*[@id='archives-wrapper']/article/div/div[2]/p[2]")

Very quickly, we have been in a position to extract the info into the spreadsheet.

Moreover, through the use of merely constructed nested formulation, we will shortly pull the info from a number of pages on the similar time.

Within the instance under, I used to be in a position to extract the identical knowledge associated to every article (title, creator, URL hyperlink, and outline) for the primary 10 pages of the PPC part.

The result’s a complete of 300 articles scraped in lower than a minute!

Evaluating The Two

So, how do ChatGPT vs. ChatGPT + Google Sheets IMPORTXML evaluate?

In my expertise, I couldn’t discover a simple and fast approach to make use of ChatGPT to scrape the info I used to be in search of – thoughts you, that doesn’t imply that this isn’t doable, and there is perhaps a number of methods to do that, however I didn’t discover any.

What labored for me was a mix of the totally different instruments, and that served me very well for my meant objective.

ChatGPT was extraordinarily helpful for writing the IMPORTXML formulation I wanted to make use of in Google Sheets, and people formulation did the remainder.

An extra bonus of the ChatGPT + Google Sheets choice is that you would be able to simply use the free 3.5 model of ChatGPT and get the instrument to construct your IMPORTXML formulation, as a substitute of getting model 4 to scan the web page and extract the info.

Key Takeaway

This highlights a vital facet of how AI has reworked how we predict and work.

The very best instrument for the job isn’t merely utilizing AI, Google Sheets, or any particular software program alone however fairly a combination of tools and skills.

It’s on this built-in strategy that we develop workflows which are environment friendly and efficient, thus enhancing our general productiveness.

Extra assets: 

Featured Picture: Visible Technology/Shutterstock