If you enjoy my Google Workspace Apps Script work, please consider buying me a cup of coffee!
TSDataTable is a simple, elegant data table publishing tool for Google Sheets & Sites.
See the blog post.
TSDataTable employs Google Apps Script inside a Google Spreadsheet to retrieve data from the first sheet, filter the data based on hidden rows and columns, cache the data to speed subsequent load times and display data in a nicely formatted, paginated and searchable data table. This data table can be published as a web application.
TSDataTable display options are configurable and the published web application can be viewed standalone or embedded in Google Sites.
TSDataTable is intended for data table publishing within a G Suite for Business or G Suite for Education domain.
-
Login to Google Drive
-
Access TSDataTable
-
Click the Use Template button. This will copy the TSDataTable host Google Spreadsheet to your Google Drive.
TSDataTable retrieves data from the first sheet of the Google Spreadsheet and uses the first row of data as the table column headings in the resulting table display.
To add, delete or change the order of the table data columns or rows ... add, delete or move columns/rows in the Google Sheet.
If you would like to display another sheet of data in the data table, move the desired sheet to the first position in the Google Spreadsheet and clear the table data cache using the table configuration options (see instructions below).
To align data table columns, align the corresponding column header in the first row of the Google Sheet.
To remove data columns or rows in the data table, hide the corresponding columns or rows in the Google Sheet and clear the table data cache using the table configuration options (see instructions below).
TSDataTable configuration is accessed through the host Google Spreadsheet menu TSDataTable → Configure Data Table.
IMPORTANT:
-
The first time the TSDataTable menu is accessed, the script will prompt for authorization. Complete the authorization flow by following the Google authorization prompts.
-
The first time the configuration dialog is opened (or the table data is retrieved after the cache has expired) there will be a delay before the dialog displays.
-
If you encounter a problem displaying the configuration dialog, close the dialog by clicking the
Xin the top right and then re-open.
-
Click the options icon to open the table configuration.
-
Click the save icon to save configuration changes. NOTE: Any saved configuration changes will update in the published data table web application on the next display.
-
Click the reload icon to invalidate/clear the data table cache and reload the data. NOTE: Any data changes will update in the published data table web application on the next dipslay.
Configure the data table title display under the Title Controls tab (options defined below).
❗ Don't forget to save configuration changes!
| # | OPTION | DESCRIPTION |
|---|---|---|
| 1 | Show Table Title | Enable/Disable table title bar display. |
| 2 | Table Title | Edit table title. NOTE: The initial table title defaults to the name of the Google Spreadsheet. |
| 3 | Table Title Color | Set table title bar background color. |
| 4 | Table Title Alignment | Align table title within title bar (left, center, right). |
| 5 | View Changes | Close configuration dialog to view configuration changes. |
Configure the data table display under the Table Controls tab (options defined below).
❗ Don't forget to save configuration changes!
| # | OPTION | DESCRIPTION |
|---|---|---|
| 1 | Table Height (px) | Set the height of the data table display (height in pixels). |
| 2 | Dark Theme | Apply a dark theme to the table display. |
| 3 | Dense Table | Decrease the height of the table data rows. |
| 4 | Show Search | Enable/Disable table search. TIP! TRUE/FALSE values and text in URLs are also searchable. |
| 5 | Fixed Headers | Fix table column headers to top of table when scrolling. NOTE: This does not work in the IE11 browser. |
| 6 | Allow Sort | Enable/Disable data table column sort. NOTE: Data sorting is alphanumeric. |
| 7 | Show True/False Values as Check Marks | Enable/Disable displaying TRUE/FALSE values from the Google Sheet as check marks in the data table. TIP! This is especially useful when using Google Sheet checkboxes. If the data value is TRUE a check mark will show otherwise the field will be blank. |
| 8 | Check Mark Type | Determine the type of check mark icon to display when the data value is TRUE. NOTE: Option 7 must be enabled to set the check mark type. |
| 9 | Show Link as URL | Enable/Disable http or https links displaying as active URLs. |
| 10 | Show URL as Icon | Turn http or https URLs into active icons vs. the full URL. NOTE: Option 9 must be enabled to set the URL to an icon. |
| 11 | View Changes | Close configuration dialog to view configuration changes. |
To publish a data table as a web application:
-
Open the Script Editor in the host TSDataTable Google Spreadsheet via the Tools → <> Script Editor menu.
-
Follow the steps in Deploying a script as a web app with the following changes:
- Under Execute the app as: → select your account
- Under Who has access to the app: → select Anyone within "your domain" ... where "your domain" is the name of your G Suite for Business or G Suite for Education domain.
To access the published data table web application, use the URL from the Current web app URL section of the web application publishing dialog (URL ends in /exec). Also use this URL to embed the published data table in a Google Site (see next section).
Once the data table has been published as a web application it can be embedded in the new version of Google Sites. Follow the instructions for adding content from another website on the Google Sites documentation.
TIP! By controlling the size of the Google Site embed area, you can further control display of the data table.
-
TSDataTable is intended for internal use within a G Suite for Business or G Suite for Education domain.
-
TSDataTable caches data for up to 6 hours to speed table display. When the cache expires the table data will be retrieved once again and the cache repopulated. The cache can be manually cleared prior to the 6 hours expiration to pick up new data and table configuration changes.
-
TSDataTable is not meant to be used with large data sets. If you receive errors for exceeding the cache quota or the table takes to 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.
TSDataTable License
© Laura Taylor (github.com/techstreams). Licensed under an MIT license.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.