Backstory

I posted recently about building online fundraising pages for a Color Run event hosted by my daughter’s Elementary School PTA. Originally I was using a CSV file as the data source for the student’s donation pages. I found a way to keep the data in Google Sheets and pull from it directly on site build in Netlify, so that they can easily be updated by admin staff in the PTA without going to text files or messing with the Git repository for each change. This post highlights this new capability for the site.

Source Data, from CSV to Google Sheets

This update to the system started with a simple comma separated values (CSV) data file. From that data I created an extended Google Sheet with the same column headings and each page record as a row. There is another sheet that I am pulling from too, this second sheet has calculated totals from donation records. A combination of this data provides the information used in the donation pages.

Python reading Google Sheets

Some modifications to the older script were necessary to pull from sheets and to write that data into the new donation page files. Note: the ‘JSON_DATA’ variable in the python code is a Netlify build environment variable that I set with JSON format Google API credential information to keep my secret stuff out of the script. There is a Google API Python Quickstart that I used for reference.

For a deeper technical dive, please see this excellent resource The Comprehensive Guide To Google Sheets With Python.

# Takes in a file CSV file and outputs each row as a Markdown file with YAML front matter named after first column.
# Data in the first row of the CSV is assumed to be the column heading.
# Original work borrowed from: https://github.com/EvanLovely/csv_to_jekyll

# Import the python libraries.
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pathlib import Path
import os
import json

# Delete old page files. Leave the _index.md file there.
[ os.remove("site/content/colorrun/" + f) for f in os.listdir("site/content/colorrun/") if not f.startswith("_") and f.endswith(".md") ]

# Get JSON_DATA from the build environment.
jsondict = json.loads(os.environ['JSON_DATA'])

# Use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_dict(jsondict, scope)
client = gspread.authorize(creds)

# Open the Google Sheet by ID.
sheet1 = client.open_by_key("1KqNkSL6F8AVadHoTSgdyyuLiZ-DQ03XG2L2LRZ2sg2o").sheet1
sheet2 = client.open_by_key("1XONZtMTfgtj8XsE38Ij7GTllXNXct_rA-6v0z-OfBxk").get_worksheet(1)

# Extract all of the records for each row.
sheetdata1 = sheet1.get_all_records()
sheetdata2 = sheet2.get_all_records()

# Set location to write new files to.
outputpath = Path("site/content/colorrun/")

# Loop through each row...
for row_index, row in enumerate(sheetdata1):
  if row.get("approved") == "x":
    # Open a new file with filename based on the first column
    filename = row.get("sfname").lower().replace(" ", "-") + row.get("slinitial").lower().replace(" ", "-") + str(row.get("grade")).lower() + '.md'
    outputfile = outputpath / filename
    new_yaml = open(outputfile, 'w')

    # Empty string that we will fill with YAML formatted text based on data extracted from our CSV.
    yaml_text = ""
    yaml_text += "---\n"
    yaml_text += "type: colorrun\n"
    yaml_text += "image: /colorrun/eagle_paint.jpg\n"
    
    # Set the Page title value.
    yaml_text += "title: Donation page for " + row.get("sfname") + " " + row.get("slinitial") + ".\n"
    # Loop through each cell in this row...
    for key,val in row.items():
      # Compile a line of YAML text from our spreadsheet keys and the value of the current value, followed by a linebreak.
      if key == "sfname" or key == "slinitial" or key == "teacher" or key == "grade":
        cell_heading = str(key)
        cell_text = cell_heading + ': "' + str(val) + '"\n'
        # Add this line of text to the current YAML string.
        yaml_text += cell_text
        
    for student in sheetdata2:
      if student["First Name"] == row.get("sfname") and student["Last"] == row.get("slinitial"):
        yaml_text += 'online: "' + student["Online"] + '"\n'
        yaml_text += 'envelope: "' + student["Envelope"] + '"\n'
        yaml_text += 'total: "' + student["TOTAL"] + '"\n'
      
    # Write our YAML string to the new text file and close it.
    new_yaml.write(yaml_text + "---\n")
    new_yaml.close()

This script generates a set of temporary files in the colorrun subdirectory of the content directory. These files are not in the repository, but only exist long enough to generate the necessary donation pages for the site. The script runs first during the build, so changes to the data files can be incorporated easily from the spreadsheets using a Netlify webhook.

The build command that I use in Netlify is python src/scripts/gspread2md.py && hugo

Google Sheet Script

I wanted to provide an easy way for someone editing the data in the sheets, to trigger the site to be rebuilt with new data. Netlify has a nice feature to send a POST request to a unique URL that will start a deployment task for the project. I added a little bit of script to the Google Sheets that adds a new ‘Webmaster’ menu and a menu item to ‘Trigger Site Build’. When the menu item is clicked, Netlify will run the build command and deploy the updated Hugo site.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Webmaster')
      .addItem('Trigger Site Build', 'buildSite')
      .addToUi();
}

function buildSite() {
    var options = {
      'method' : 'post',
      'payload' : ''
    };
    UrlFetchApp.fetch('https://api.netlify.com/build_hooks/[unique-code-here]', options);
}

So far the system is working really well and the PTA is raising a decent amount of money through online donations.