Dynamic Resources - UTACM/TexasACM.org GitHub Wiki

Instead of hard-coding many pieces of data that can often change, we’ve implemented a system of dynamic resources. JavaScript files on our website can read from a Google Sheet and display that data on our website. This makes editing data easier, and any ACM officer can add, modify, or delete information shown on the website. These Sheets are located in the ACM Drive > Website Administration > Dynamic Resources

In order to set one up, you first have to create a Google Sheet. You will need to Freeze the first row. That row’s content will be how you will access the other rows’ data, so you should also add edit protections to it so others don’t accidentally edit it and break the website.

Now Click File > Publish to the Web... Ensure it says “Link”, “Entire Document”, “Web page”, “Automatically republish when changes are made” then click Publish.

Next, you are ready to write the JavaScript file. Create one or copy an existing file. The JavaScript file will follow the following format:

     var public_spreadsheet_url = "url of the google sheet";
    window.addEventListener("DOMContentLoaded", init)

    function init() {
        Tabletop.init( { key:public_spreadsheet_url,
                         callback: showInfo,
                         simpleSheet: true } );
    }

    // list the column names here
    var eventNameCol = "Event"
    var locationCol = "Location"
    var facebookLinkCol = "Facebook"
    . . .

    function showInfo(data) {
        // You can now access the data as a 2D array, data[rowNum][columnName]
        console.log(data[0]["Facebook"]); //equals “Link”
        // It is recommended that you don’t refer directly to column names in code.
        // Declare at top and use internal names so that col name updates are easy 
        console.log(data[0][facebookLinkCol]); //equals “Link”
        // Instead of a while loop with an index variable, use a foreach loop instead
        data.forEach(event => {
            //this will list every event’s name. In this case, just “Name”
            console.log(event[eventNameCol]);
        });
    }

What happens is that the addEventListener line will run init() when the webpage has fully loaded. When init() runs, it will start Tabletop, give it the spreadsheet URL, and when Tabletop has retrieved the data, it will call showInfo() with data. showInfo() will be called and provided with the dynamic resources data when it is ready.

Each row of the spreadsheet is addressed with data[rowNum]. A specific column is specified with the column name. Note though, that rowNum is 0 indexed after the first column, so row 2 of the spreadsheet is actually row number 0. In the above example, if I want to get the cell that holds “Name”, I would use data[0][eventNameCol]. You should use a foreach loop to easily and cleanly loop through each row of the data.

After you get the data from the sheet, you can make an HTML string and easily inject it into the HTML document with jQuery selectors, or

    Document.getElementById("elementId").innerHTML = "whatever"

Be sure to include the JavaScript file you wrote AND Tabletop.js into your HTML file. Otherwise it won’t work.