Image by Bruno Glätsch from Pixabay

TSDataTable — Simple, Elegant Data Tables for Google Sheets & Sites

I recently worked on a project for a G Suite client to display a table of Google Sheet data in one of the client’s internal Google Sites. Although this is easily accomplished by embedding the Google Sheet directly, the client needed a customized solution with data search and sort.

The challenge? Make this table powerfully configurable, simple to use, private to the client domain and employ existing G Suite tools.

After some dedicated design and development with Google Apps Script … 💥 TSDataTable!

TSDataTable Overview

Diagram 1 — Data Table Populated From Google Sheet

TSDataTable populates a web table with data from the first sheet of a Google Spreadsheet … using the first row of sheet data as table column headers in the table display (Diagram 1).

Cell alignments in this first row of sheet data are used as column alignments in the resulting table display (Diagram 2).

Diagram 2 — Data Table Column Alignments from First Row Cell Alignments

Diagram 3 — Hidden Sheet Data Not Displayed in Data Table

Hiding data rows and columns in the Google Sheet prevents those portions of data from being displayed in the resulting table (Diagram 3).

To add, delete or modify table data or reorder table columns add, delete or move columns/rows in the Google Sheet.

Table display is configured through a Google Sheet menu (Diagram 4).

Diagram 4 — TSDataTable Configuration Menu in host Google Spreadsheet

Table display configuration options exist for (Diagram 5):

• Title (display, text, color, alignment)
• Data search
• Column sort
• Data display (including URLs and Google Sheets checkboxes)

Diagram 5 — TSDataTable Configuration Options

Any saved configuration changes will immediately reflect in the table display.

TSDataTable caches data for up to 6 hours to speed table display but the cache can be manually cleared to pick up new data and table configuration changes.

Diagram 6 — Publish Data Table as a Web Application

Data tables can be published as web applications within a G Suite domain (Diagram 6).

Published web applications can be viewed as standalone sites or embedded in Google Sites (Diagram 7).

Diagram 7 — Published Data Table Embedded in a Google Site

Important Notes

• TSDataTable is intended for internal use within G Suite for Business or G Suite for Education domains.
• TSDataTable uses data caching to facilitate faster table display and is not meant to be used with large data sets. If you receive cache quota exceeded errors or the table takes too long to load, you may need a more robust solution like App Maker.
• TSDataTable may not display all sheet data types (e.g. cell images).
• TSDataTable works with the new version of Google Sites.

Need a similar data table for your Google Site? See the TSDataTable project, installation and getting started guide on Github.

techstreams/TSDataTable

Need help automating workflow inside your G Suite domain? Get in touch

Follow me on Twitter , Medium and Github.

Enjoy this post? Please buy me a coffee. Thank you!