Display live data from Google Spreadsheet in an HTML Table
This article shows how to generate a self-updating HTML Table that displays data from Google Sheets
We in SheetDB.io constantly work on new integrations and features of our app in order to make it useful for as many people and businesses as possible. What SheetDB does is turning a Google Spreadsheet into a JSON API which then can work with different services, tools and programming languages.
Lately I have described how to generate an HTML Form snippet with the use of SheetDB and, by doing this, be able to collect data from users and post them directly to Google Sheets. This time I’d like to show you yet another feature which we implemented to our application, and that is the Table generated out of a spreadsheet, also in the form of a snippet. After embedding it into the code, the Table automatically displays any changes made to the spreadsheet’s data.
Creating an API from Google Sheets data
In order to be able to present the Table feature, I’ve decided to put the details of a few well-known movies into my sample spreadsheet, as shown below. To create an API from your dataset, you’ll need the spreadsheet’s URL address. Having it in the clipboard, go to sheetdb.io and log into your account to create an API.
Click the CREATE NEW button on the right and paste your URL into the given input. After one more confirmation click, the API is ready to be used. Click either on its name (same as the spreadsheet’s name) or the green arrow on the right to see the API view. There you can see your endpoint’s URL, or generate a Form snippet mentioned above, or a Table snippet - the one in question.
After you pick the latter, SheetDB website will take you to the table’s snippet generator. If you have all the permissions granted for this API, you’ll see a reminder at the top of the page, saying that it will be exposed to the outside world. In order not to allow others to mess with your spreadsheet, go to the API settings and leave only the READ (GET) permission marked.
Customizing your HTML Table
In the creator you’ll see all of the columns from your spreadsheet, listed one above the other. You can choose which of them are to be displayed in the table itself. You as well may decide whether each of them should be sortable or searchable. The columns’ names that are duplicated from the spreadsheet can also be modified at will.
Below, you can apply some style customization. Choose the row’s height, the font size and if you want your table to appear as a whole or rather as big as a screen, and thus be scrollable. The last setting is what to put as a placeholder in the table’s search box.
Underneath you’ll find the live preview, showing every change in real time, so you can see your ultimate version displayed there immediately. You can check straight away if the sorting and searching features work correctly.
Generating an embeddable snippet
If you’re happy with the effect, simply click the GENERATE CODE button under the preview. What you’ll get is a single line of code - your snippet that can be copied and used within your code or in any of the low-code platforms such as Wix or WordPress.
In order to conclusively test whether the Table actually works, I’ve taken my snippet to Codepen - a smart tool which shows the coding results in real time. This implies that the snippet can be also successfully embedded elsewhere.
Thank You!
I hope you’ll find our new feature useful. You can also check the video I made on this, titled: Create an HTML Table from your Spreadsheet’s data. I also recommend you to visit SheetDB’s YouTube channel because we post new tutorials on the app’s features regularly.
If you have any questions or feedback, email us at support@sheetdb.io and feel free to reach out on Twitter @SheetDB_io. We’d love to hear your opinion on our work and/or ideas as to what else you’d like us to do with SheetDB. We might just take them seriously!
