This article will show us how to get the data from a Google Sheet, format it into a beautiful looking table and embed it into your website as a Table Widget.
Google Sheets has it’s own in-built function where you can embed a sheet into a website, but it is pretty ugly and is embedded using an iframe.
This solution takes the raw data from the Google Sheets cells and converts it into a nice looking table that is sortable, can be paginated and can be searchable. This is a much nicer solution for embedding Google Sheets into a website.
Once the table is embedded on your website, it will stay updated with fresh data if you change it within Google Sheets.
Let’s look at the steps required for embedding Google Sheets into your website.
The final table will look like this and this is how the table would display on a website:
Set up our Google Sheet
For this article, we are just adding some simple data from the 2024 Paris Olympics that shows the medal tally for each of the first 5 nations. We’ve also included the URL of the country flags.
We don’t need to add headers, we just want the raw data as the headers will be setup within API Widgets.
Make your Google Sheet public
The first step is to make your Google Sheet public by clicking on the Share button at the top of the page within Google Sheets as shown below:
After clicking the Share button, you will see a popup like the one below:
You need to change the settings so that the General Access is set to Anyone with the link, and then set the other option to Viewer.
This will now allow us to be able to connect to the Sheet.
Connect API Widgets with your Google Sheet
Now we need to click on the Get Started button at the top of this page to set up our new Table Widget. After clicking on the button, we will see the Builder page.
The first section on this page is the Data Source section. Here we want to click on Google Sheets as our data source.
There are 2 fields that we need to fill in.
Sheet ID field
In this field, we need the ID of your Google sheet. This ID is found in the URL of your sheet. It is the jumbled mix of letters and numbers found between the two slashes in the URL.
If this is your sheets URL: https://docs.google.com/spreadsheets/d/1olZyjQ-786O0NYU6txXm1pLTQA48ZV4uGiXsP3F8zcA/edit?gid=0#gid=0
Then this will be the Sheet ID: 1olZyjQ-786O0NYU6txXm1pLTQA48ZV4uGiXsP3F8zcA
Range field
In the Range field, you can add either:
- the name of the sheet, such as Sheet1
- the name of the sheet and a range, such as Sheet1!A1:G50
- a range, such as A1:G50 – this will use the first sheet
Convert to Table
The next option is to select Table as the format to convert your Google Sheet into. This will set the Widget type to Table.
Test Connection to Google Sheets
We can now go down to the Test Connection section and click on the button to test that we can connect to our Google Sheet.
Our successful test is shown below. After a successful connection test, we can then click the blue button to start configuring our table Widget.
Configure our Google Sheets table
On the next page, now that we have created our Table, it will look something like what is shown below:
We can click on the Columns tab to format how the columns are displayed and also modify some of the settings to get it looking how we want it to.
This is how ours has ended up, ready to be embedded into our website.
Embedding Google Sheets table into website
After you are happy with how the table looks, you can now click the Embed button at the top of the page. The embed popup will appear and looks like the below:
You can follow the instructions on how to embed a Widget and then you are done. You now have a beautiful looking table embedded onto your website using data from Google Sheets.
Updating data within Google Sheets
If you need to update the data in the table, you can do this within Google Sheets. Simply go to your spreadsheet, update whatever data you need and then the table Widget will update with the new data based on the Refresh Time you have set in the Embed popup.
So if you have a Refresh Time of 1 Hour, this means the table would run an update every hour and it will pick up your edits in the next update.