The following is a quick and dirty script to overlay static HTML pages with values pulled from Google sheets.
The sheet for this demo can be found: Content Sheet.
The script will pull the content of one or more sheets from Google and will replace the content with the referenced part of the spreadsheet.
getting it to work
- Create a Google sheet and publish it for all to see (being sure to keep the option to publish changes)
- Write the standard HTML document including JQuery and jquery.tps.scms.js
-
Wrap the sections of text that require replacement in an element (e.g. span) with the following attributes
- class: "scms" so the code knows it needs to do something with the span
- data-scms-sheetid: the unique ID that Google assigns the spreadsheet. This can be found between the d/ and the next / of the spreadsheet URL
- data-scms-sheetnumber: starting from 1 the sheet within the spreadsheet
- data-scms-name: a friendly name for the sheet (default will use id-number. If a sheet has the same name (of id and number), then it will override the previous sheet.
- data-scms-action: at present only swap is defined
- data-scms-range: the range of cells to apply, swap only takes a single cell reference e.g. "b1" (if on the same element where the sheet is linked), or the full path "mySheetName!b1" (if the sheet is linked elsewhere)
When the page has loaded the JavaScript will run and replace all required values on the page with the values updated from the sheet.
example
This is a very simple example of SheetCMS working non-obtrusively.
This is an example of referencing a sheet that has already been loaded elsewhere on the page.
under the hood
The script is split into two "init" and "render" which can be called once the document has loaded. The split enables inbuilt render functions to be ignored and the populated JSON to be used instead. Their functions breakdown:
-
The init function:
- parses the document looking for any linked Google sheets
- creates a sheet object with the id, number and name of the sheet for all it finds. No attempt to load sheets is made at this point.
- after the first parse the document is parsed again an any bound elements are given an scmsrender function that will carry out the appropriate action at render time.
- once the document is parsed twice each of the sheets are loaded asynchronously, once loaded they update their state to say so. The lightweight object gsReader handles the simple functions of mapping the URL and updating the return so that it is in parseable JSON.
-
The render function:
- checks to ensure all of the sheets have finished loading
- loops through all of the bound elements instructing them to render themselves