In an effort to promote change in manufacturing and policy, the Healthy Stuff Lab team of scientists test consumer products and natural resources such as air, water, and soil for toxic chemicals. They release their findings in reports and articles on their web site. These reports typically include tables of products or materials tested and information to identify those items such as brand, manufacturer, product numbers, location, color, images, etc. Those tables may also include results from the testing instruments and the interpretation of those results.
Our role in this project was to design and create an automated web page “table builder” that integrated with their established scientific workflow. They use Google Sheets to view and analyze their data, so the concept was to pull values and style information from designated Google Sheets and automatically create web page tables that could be placed into reports. The tables were to be:
- Interactive. A user visiting the site and viewing a report should be able to click on a table row to display more details, click on a column header to sort the table, and enter text in a search box to dynamically filter the data.
- Flexible. Different tables can have different numbers of rows and columns with varying formats in table cells. The “table builder” shouldn’t impose a fixed data structure but should be flexible enough to accommodate different table dimensions and data formats.
- Styled. The table cells should inherit styles from the Google Sheet. Supported styles should be font size, font weight, horizontal text alignment, text color, and background color.
- Downloadable. A user should be able to click to download the table data to a CSV file.
While Google Sheets can be embedded into web pages, the team wanted more control than embedding would provide – they didn’t want changes to the Google Sheet automatically published to the web page.
Google Sheets
Authoring in Google Sheets was business as usual with a couple exceptions:
- Permissions. All files used to build web page tables needed to be stored in a “special” folder. The authenticated web site could access and read these files since they all inherited the permissions of the parent folder.
- Styling. Only certain formatting styles were supported by the web page table. Unsupported styles would not transfer from the Google Sheet and be rendered.
Importing into Drupal
We created a custom Drupal module that used the Google Sheets API to authenticate and read spreadsheet data. The module creates a Drupal “table” entity to store the table data, an admin user interface to import spreadsheet data from Google, and an admin user interface to set the display options for the columns used in tables, details panels, and downloaded CSV files.
The module also creates a custom Drupal field with a field widget used in admin forms and a field formatter to render the table on a web page. The field formatter collects and transforms the table data, and makes that data available to a twig file. The twig file contains logic to render the table.
Image support is managed by mapping image file names in the Google Sheet to image files bulk-uploaded into Drupal.
Success
We created a custom Drupal module to automatically build web page tables from data in Google Sheets. The tables are interactive, flexible, styled, and downloadable – fulfilling all the original design objectives. We’ll continue to improve and enhance the module as we learn more about how the team uses the current feature set, what they like, and what they would change.
- Drupal
- Google Sheets
- Google Sheets API
- RESTful Services
- JSON