I had a client who needed to display a list of churches taking part in an event. The list needed to be organised by area, include information about each church, and allow visitors to download a PDF they could print off.
The information was already being managed in Google Sheets, so rather than move everything into a database or build a CMS, I decided to use the spreadsheets directly.
Google Sheets has a handy feature which lets you query spreadsheet data and return it as JSON. Combined with Vue, this meant I could build a dynamic interface without needing a backend.
Getting JSON from Google Sheets
Google Sheets has a query endpoint which can return spreadsheet data in a format that JavaScript can work with.
The URL looks like this:
1function GoogleJSONEndpoint(sheetID, pageId) {2 return `https://docs.google.com/spreadsheets/d/${sheetID}/gviz/tq?tqx=out:json&tq=SELECT%20*&gid=${pageId}&headers=0`3}
You need two things: the spreadsheet ID, which you can find in the URL of your Google Sheet, and the ID of the individual sheet (tab) you want to query.
The tq=SELECT%20* parameter tells Google to return all the columns and rows. You can also use the Google Visualization API Query Language to filter the results.
One slightly annoying thing is that the response isn’t actually valid JSON. Google wraps the data in a JavaScript function call, so you can’t just pass the response straight to JSON.parse().
I used Axios to request the data and stripped away the wrapper before parsing it.
1function getJSONfromGoogle(response) {2 return JSON.parse(response.data.substr(47).slice(0, -2))3}4 5const response = await axios.get(6 GoogleJSONEndpoint('YOUR_SHEET_ID', '0')7)8 9const churches = getJSONfromGoogle(response)
It’s not particularly elegant, and it relies on Google’s response format remaining the same, but it worked for what I needed.
The spreadsheet also needs to be accessible to the application. As this is entirely client-side, any data returned by the endpoint is available to visitors, so you wouldn’t want to use this approach for private information.
Combining multiple spreadsheets
The project actually uses three Google Sheets.
One contains the churches that have registered for the event, another contains the full list of churches, and the third contains Google Maps links for each area.
The first two spreadsheets have slightly different column structures, so I needed to convert their rows into a consistent format.
Google returns each row as an array of cells, with values stored inside properties such as v and f. I mapped the column positions to meaningful names, such as Town/Village, Name of Church, and Post Code of Church.
Once I’d converted the data from both sheets into JavaScript objects, I combined them into one array and sorted it alphabetically by town or village.
Grouping the churches by area
The next challenge was displaying the churches in a way that made sense to visitors. A single long list wasn’t particularly useful, especially when people wanted to find churches in a specific area.
JavaScript’s reduce() method made grouping the results fairly simple.
1this.grouped = this.sheets.reduce(function (result, church) {2 result[church['Area']] = result[church['Area']] || []3 result[church['Area']].push(church)4 return result5}, Object.create(null))
This creates an object where each key is an area and its value is an array of churches within that area.
I then sorted the area names alphabetically and used Vue to render each area as a separate section of a table.
At the top of the page, I added a dropdown containing all the available areas. Selecting one scrolls the table to the relevant section, rather than hiding the other areas or loading a new page.
I also used the third spreadsheet to add a Google Maps link to each area, making it easier for visitors to plan which churches to visit.
Generating PDFs in the browser
The final requirement was allowing visitors to download the church lists.
I initially considered generating these on a server, but there wasn’t really any reason to. All the data was already available in the browser.
I found jsPDF
Here’s a simplified version of the code:
1const doc = new jsPDF('landscape') 2 3const table = area.map(church => [ 4 church['Town/Village'], 5 church['Name of Church'], 6 church['Road/Street'], 7 church['Post Code of Church'], 8 church['Church facilities'], 9 church['Additional Notes']10])11 12doc.autoTable({13 head: [[14 'Town/Village',15 'Church',16 'Road/Street',17 'Postcode',18 'Facilities',19 'Notes'20 ]],21 body: table,22 styles: {23 fontSize: 10,24 cellPadding: 125 },26 showHead: 'firstPage'27})28 29doc.save(`hihct-${name}.pdf`)
The application has two options. Visitors can download a PDF containing the churches in a particular area, or download every area in one document.
For the full document, I loop through the grouped areas and add a new table for each one.
I also set the PDF orientation to landscape and adjusted the column widths to leave more space for notes.
Keeping it simple
The whole application is built with Vue 2, Axios and jsPDF. There isn’t a build process or any server-side code. It’s just an HTML file and a JavaScript file.
The benefit of this approach is that the people managing the event can continue using Google Sheets. They don’t need to learn a new CMS, and changes to the spreadsheets are reflected when the application next loads.
There are a few limitations. The application depends on Google’s spreadsheet endpoint, the data has to be publicly accessible, and the JavaScript assumes the spreadsheet columns stay in the same order.
For a larger application, I’d probably introduce a proper API or at least an intermediate layer to validate and cache the data. But for a relatively small event website, that would have added complexity without much benefit.
It’s a good example of how you don’t always need a database or complicated backend to build something useful. Sometimes a spreadsheet and a bit of JavaScript are enough.