Managing HTML Tables in Squarespace 7

Squarespace 7 did not add the ability to easy manage tabular data. I recently set up a website for a client who has the need to update a small number of tables daily. I explored a number of solutions (detailed below). I finally settled on a shared Google Docs spreadsheet which the client would update via the Google Docs web interface and which would be integrated into the live website with a small bit of JavaScript.

Possible solutions:

  • Embedded Office Live Spreadsheet - Limited formatting options, not responsive
  • Embedded Google Docs Spreadsheet - Limited formatting options, not responsive
  • Built-in Grid Layout System in Squarespace - Built-in UI is difficult to manipulate, limited formatting options.

Now on to the solution I proposed the my client. First, create a spreadsheet in Google Docs. I've created a spreadsheet for Widgets for the sake of this post.

From the menu, select File and then select "Publish to the web..."

Under the "Published content & settings" heading (which you may need to expand) click the "Start publishing" button. Confirm when prompted (not shown).

Now we've opened up the spreadsheet, we need to save the spreadsheet ID. This ID can be taken from the sharing link that should now be showing.

What's not shown at this screen is a link to the JSONP formatted output of this spreadsheet. This link is in the following format:

  • https://spreadsheets.google.com/feeds/list/<SPREADSHEET_ID>/od6/public/values?alt=json-in-script&callback=<NAME>

Using the above format, and the ID shown in the image above, we can view the JSONP formatted output for the Widgets spreadsheet at the following URL:

https://spreadsheets.google.com/feeds/list/1p_icgWWaKTFvhKCEqie0jXc7V15_Qyd5b5JnCL1nh9A/od6/public/values?alt=json-in-script&callback=callback

One thing you'll notice is that the title row (four columns merged) does not appear in the JSONP formatted output. This is a small hitch that we'll code around by manually adding the table header row in the HTML in the codeblock. This actually provides a good placeholder when we're editing the page in Squarespace, but it's not ideal. However, it's also not such a big issue that I invested much time in researching a solution, just provided a note to the client that the header row won't change if they change it in Google Docs.

After the data side of the equation is worked out, we need a way to get that data on to the live website. To accomplish this task, we'll inject a bit of JavaScript into the Squarespace website to pull in the JSONP formatted table data and push that data into an HTML table. Within Squarespace there are a number of places the JavaScript below can be injected. For this project, I injected it at the page level. That is, by going to the settings for the page, going to the Advanced tab, and pasting the code in the "Page Header Code Injection" textbox.

JavaScript to Inject

<script type="text/javascript">

    var requests = [];
    var requestsReturned = 0;

    function loadTables(){
        var tables = document.getElementsByTagName('table');
        for(var i = 0; i < tables.length; i++){
            var table = tables.item(i);
           
            if(!table.attributes.getNamedItem("data-remote"))
                continue;
           
            if(table.attributes.getNamedItem("data-remote").value !== "true")
                continue;
           
            requests.push(createRequest(table));
        }
        for(var i = 0; i < requests.length; i++){
            requests[i].send();
        }
    }

    function createRequest(table){
        var url = table.attributes.getNamedItem("data-url").value + "&callback={callback}";
        
        return new Y.JSONPRequest(url, {
            on: {
                success: handleTableLoaded,
                failure: handleTableFailed,
                timeout: handleTableFailed
            },
            args: [table] 
        });
    }
    
    function handleTableLoaded(data, table){
        var tableElement = table;
             
        for (var rowNumber = 0; rowNumber < data.feed.entry.length; rowNumber++){
            var row = data.feed.entry[rowNumber];
            var rowElement = document.createElement("tr");           
           
            for(var key in row) {
                if (row.hasOwnProperty(key)) {
                    if(key.startsWith("gsx$")){
                        var cellElement = document.createElement("td");                       
                        cellElement.innerHTML = row[key].$t;
                        cellElement.style["text-align"] = "center";
                        if (rowNumber == 0) {
                            cellElement.style["font-weight"] = "bold";
                        }                       
                        rowElement.appendChild(cellElement);
                    }
                }
            }
           
            tableElement.appendChild(rowElement);
        }
        
        handleTableFinally(data, table);
    }
    
    function handleTableFailed(data, table){
        var headerElement = table.getElementsByTagName("th")[0];        
        var rowElement = document.createElement("tr");  
        var cellElement = document.createElement("td");                   
        cellElement.innerHTML = "An error occurred while loading this table.";
        cellElement.setAttribute("colspan", headerElement.getAttribute("colspan"));
        cellElement.style["text-align"] = "center";
        rowElement.appendChild(cellElement);
        table.getElementsByTagName("tbody")[0].appendChild(rowElement);
        handleTableFinally(null, table);
    }
    
    function handleTableFinally(data, table){
        requestsReturned++;
        
        if (!data)
            return;
        
        /* Do something with data from single table */
        
        if(requestsReturned < requests.length)
            return;
        
        /* Do something when all table requests have returned */
    }

    YUI().use('event-base', function (Y) {
        Y.on('domready', function () {
            loadTables();
        });
    });
    
</script>

The JavaScript above will populate every HTML table element containing the data-remote attribute and the data-url attribute. The data-remote attribute should equal "true" and the data-url attribute should contain a valid Google Doc spreadsheet JSONP URL as described above. One thing to note is the "callback" URL parameter is left off the data-url value (this parameter is filled in via the JavaScript above).

HTML for Codeblock

<table
       data-remote="true"
       data-url="https://spreadsheets.google.com/feeds/list/1p_icgWWaKTFvhKCEqie0jXc7V15_Qyd5b5JnCL1nh9A/od6/public/values?alt=json-in-script">
  <tr>
    <th colspan="4">Widgets</th>
  </tr>       
</table>

Inserting a codeblock with the HTML above will cause the page to load the Widget table and append the table rows into the existing HTML element. The final product is shown below.

Widgets
Geoffrey O'Donnell1 Comment