How to Insert Table Data into Google Spreadsheet using Website URL

Image Source: Automatio

Hello Friends,

In this article, we are going to learn about — “How to insert table data in the Google Spreadsheet using website URL”.

Sometimes we need to get some information from the website and keep checking the updated data, it could be career opportunities, time table, course details of some institution, any other information in table form. So from now you can just take URL and insert table data as per your required columns into the Google Spreadsheet.

Let’s see how we can perform this task and automate the same.

1. Create Google Spreadsheet in Google Drive
2. Get the Website URL from which data should be fetched
3. Apply formula in Google Spreadsheet

Step 1: Create Google Spreadsheet

You just need to go in Google Drive and create blank spreadsheet as show below:

Step 2: Get Website URL

Visit the website from which you would like to fetch table data and copy the URL. For example, we have following web URL — http://en.wikipedia.org/wiki/Demographics_of_India

Step 3: Apply formula in Google Spreadsheet

Now, you just need to apply formula to insert the table. In Google Spreadsheet we have IMPORTHTML function to perform this task. Let’s see the syntax and use of the same.

Syntax:

IMPORTHTML(url, query, index)

Explanation:

• url - The URL of the page to examine, including protocol (e.g. http://).
• The value for url must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
• query - Either "list" or "table" depending on what type of structure contains the desired data.
• index - The index, starting at 1, which identifies which table or list as defined in the HTML source should be returned.
• The indices for lists and tables are maintained separately, so there may be both a list and a table with index 1 if both types of elements exist on the HTML page.

How to Apply the formula in sheet: Now suppose we have multiple tables on the website and would like to access specific one, so we have to do as follows

For example, we want 4th table data from this URL, therefore

=IMPORTHTML(“http://en.wikipedia.org/wiki/Demographics_of_India","table",4)

Now apply this formula with URL to the cell of Google Spreadsheet. Please see the screenshot for the same.

Once it is applied, the table data will be visible as it is one the website.

In our next article, we will see how to create trigger using Google Apps Script to automate this to get fresh (updated) data from the website URL.

I hope you find this article helpful in fetching table data from the website URL to Google Spreadsheet. For more such articles please follow, upvote and share this with friends.

If you are interested to learn Google Apps Script and automate your Google Workspace ? must try this e-Book on “Google Apps Script: A Beginners Guide”

Happy Learning … 😁✌️

I’m available for freelance opportunities and you may reach out via email at dilipkashyap.sd@gmail.com for further collaboration.