Menu

A few months ago I got an task to create a simple participant registration system for a Wordpress website (personal project). People are registering to the running event via this system. This blog post contains information how this system was built. I'm interested to investigate new technologies so I decided to create mixed system which is using Google and MS Azure platform.

General requirements for the system

  • Simple responsive registration form + thanks page
  • Registered persons should be listed on the site
  • Wordpress hosted site
  • Admin users are already using Google ecosystem

Solution architecture

Architecture

  • Wordpress = Public web site of the running event
  • Google Forms = Registration form which is shown via Iframe in the Wordpress site
  • Google Drive / Google Spreadsheet = Registeration data
  • Azure PaaS Web App = Lists registered persons via Iframe in the Wordpress site

Registration form powered by Google Forms

Google Forms is a tool which makes possible to create forms using browser user interface. Microsoft has also similar application called Microsoft Forms which requires O365 subscription. Google Forms was an easy choice because admin users were already using Gmail email and Google Drive. More detailed information about how to use Google Forms can be found from here. In this project Google Forms is used to create registration form for end users. 

Let's start. First sign in with your Google account to the Forms site and design your form. Google Forms includes 12 field types: 9 question types, along with text, photo, and video fields. Registration form in this case has the following fields:

  • Name (Text field)
  • Distance (Radio button)
  • Birth date (Date)
  • Team (Text field)
  • Street Address (Text field)
  • Postal Number (Text field)
  • City (Text field)
  • Country (Text field)
  • Phone number (Text field)
  • Email (Text field)
  • Gender (Radio button)

Form settings

From settings view you can determine if email addresses collection is required. This settings creates Email Address field to the top of the form.

Email field

One requirement was to show thanks message. You can determine confirmation message which is shown after submit from the presentation tab.

Thanks message

Response data destination

Google Forms stores data of the form to the Google Spreadsheet file which is stored to the Google Drive. If you want to change name or destination of the spreadsheet where form data is stored open "Select response destination"

Response destination

Response destination

Now basic functionalities of the form are ready. Now Form can be embedded to the Wordpress site using Iframe.

Enable Google Spreadsheet API

Next we will configure Google APIs to allow external application to fetch data from Google Spreadsheet. This external application will be created later. Open Google Cloud console and sign in with your Google credentials. Give the name of the project and click Create.

Project

Next select APIs & Services and select Dashboard

Dashboard

Click Enable APIs and Services

Enable API

We are interested about Google Sheets API. Use Search to find Sheets API.

Sheets API

Enable Sheets API

Enable API

Next select Credentials. We will create a service account which is later used in the Azure Web App to fetch data from Google API.

Credentials

Select Service account key under Credentials Tab.

Service account

Give name for the service account and select Service Account User role. Select JSON key type which also recommended by Google. After creation you will get a JSON file which contains private key for your client.

Service account

Private key data

Example about private key JSON:

{
  "type": "service_account",
  "project_id": "",
  "private_key_id": "",
  "private_key": "-----BEGIN PRIVATE END PRIVATE KEY-----\n",
  "client_email": "",
  "client_id": "",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": ""
}

Google Drive permissions

Service account which was created on the previous section has not permissions to the any files in Google Drive. In this section I will describe how you determine permissions for service account. Open Google Drive and find Spreadsheet file were Google Forms stores the data. Right click top of the file and select Share.

Share

Find service account which was created on the previous phases. Give read permissions for the account.

Permission

Registered people list app (Azure hosted)

Registration data in Spreadsheet contains a lot of data about participants so we cannot use Spreadsheet file as it is. In this section I will describe shortly how small ASP.NET Core 2 + React.js App is created to show only few columns of data from Spreadsheet. Later this application is used in the Wordpress site via Iframe. Maybe later I will refactor this solution a little bit and create native PHP app for Wordpress site. Now I'm using MS technology because I'm more familiar with it. This app will be hosted in Azure as PaaS application. It's also possible to host ASP.NET Core application in the Google cloud as well. I will investigate Google cloud + .NET application possibilities later.

First create Visual Studio project using ASP.NET Core 2 React.js template.

Visual studio template

Next install Google.Apis.Sheets.v4 nuget package to your project.

Nuget-package

How to use Google Sheets API

Back-end

First you have to initialize GoogleCredential object. You can use FromJson method were you can pass private key JSON content as a string. In the application side you can store JSON information ex. to the Key Vault in Azure. 

string[] scopes = { SheetsService.Scope.SpreadsheetsReadonly };
GoogleCredential credential = GoogleCredential.FromJson(jsonData).CreateScoped(scopes);         
SheetsService service = new SheetsService(new BaseClientService.Initializer()
{
    HttpClientInitializer = credential,
    ApplicationName = applicationName,
});

Next create a Person object which describes the Registered person.

public class Person
{
    public string Name { get; set; }
    public string Team { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
    public string Distance { get; set; }
    public string Gender { get; set; }
}

SpreadsheetFieldsEnum contains index numbers of the each column in the Spreadsheet.

public enum SpreadsheetFieldsEnum
{
    Name = 1,
    Team = 4,
    City = 7,
    Country = 8,
    Distance = 2,
    Gender = 11
}

Before fetching data from the spreadsheet via API id and name of the spreatsheet should be checked. Open Google Drive and open Spreadsheet file. Spreadsheet Id is shown in the URL. Spreadsheet name is shown in the bottom of the screen when the spreadsheet file is open.

Spreadsheet Id

Fetching the data is very straightforward process. Make get request to the specified spreadsheet and call Execute method. Execute returns a response object where you can loop through data and create new Person objects.

SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, sheetName);

ValueRange response = request.Execute();

//skip first row because it contains header title information
return response.Values.Skip(1).Select(row => new Person()
{
    Name = ProcessRow(row, (int)SpreadsheetFieldsEnum.Name),
    Team = ProcessRow(row, (int)SpreadsheetFieldsEnum.Team),
    City = ProcessRow(row, (int)SpreadsheetFieldsEnum.City),
    Country = ProcessRow(row, (int)SpreadsheetFieldsEnum.Country),
    Distance = ProcessRow(row, (int)SpreadsheetFieldsEnum.Distance),
    Gender = ProcessRow(row, (int)SpreadsheetFieldsEnum.Gender)
}).ToList();

API Controller has GetRegisteredPerson action which returns list of Person object to the front-end.

[HttpGet("[action]")]
public List<Person> GetRegisteredPersons()
{
    List<Person> allRegistrations = new List<Person>();
    string[] scopes = { SheetsService.Scope.SpreadsheetsReadonly };

    var applicationName = _configuration[Constants.Constants.AppSettingKeys.APPLICATION_NAME];
    var spreadSheedIdFi = _configuration[Constants.Constants.AppSettingKeys.REGISTRATION_FORM_FI_SPREADSHEETID];              
    var spreadSheedNameFi = _configuration[Constants.Constants.AppSettingKeys.REGISTRATION_FORM_FI_SPREADSHEET_NAME];

    if (string.IsNullOrEmpty(applicationName)){ return allRegistrations; }
    if (string.IsNullOrEmpty(spreadSheedIdFi)) { return allRegistrations; }           
    if (string.IsNullOrEmpty(spreadSheedNameFi)) { return allRegistrations; }               

    allRegistrations = FetchSpreadsheetData(spreadSheedIdFi, spreadSheedNameFi);                
    
    return allRegistrations;
}

Front-end

I created a small React.js component which fetch data from the back-end controller and renders data to the table.

import * as React from 'react';
import { RouteComponentProps } from 'react-router';
import 'isomorphic-fetch';
import LocalizedStrings from 'react-localization';
import 'babel-polyfill';


let strings = new LocalizedStrings({
    en: {
        name: "Nimi/Namn",
        team: "Seura/Klubb",
        city: "Kaupunki/Stad",
        country: "Maa/Land",
        loading: "Ladataan/...",
        noRegistrations: "Ei ilmoittautuneita/Ej registrerad",
        women22km: "Naiset/Damer 22 km",
        men22km: "Miehet/Män 22 km",
        women10km: "Naiset/Damer 10 km",
        men10km: "Miehet/Män 10 km"      
    }
});

const commonConstants = {
    gender: {
        female: 'Nainen / Kvinna',
        male: 'Mies / Man'
    }    
}

interface RegisteredState {
    registeredPersons: Person[];
    loading: boolean;
}

interface Person {
    name: string;
    team: string;
    city: string;
    country: string;
    distance: string;
    gender: string;
}

export class Registered extends React.Component<RouteComponentProps<{}>, RegisteredState> {
    constructor() {
        super();
        this.state = { registeredPersons: [], loading: true };

        fetch('api/Registration/GetRegisteredPersons')
            .then(response => response.json() as Promise<Person[]>)
            .then(data => {
                this.setState({ registeredPersons: data, loading: false });
            });
    }

    public render() {   
        let contents = this.state.loading ? <p><em>{strings.loading}</em></p> : Registered.renderRegisteredPersonsTable(this.state.registeredPersons);
        return <div>         
            {contents}        
        </div>;
    }   

    private static renderRegisteredPersonsTable(registeredPersons: Person[]) {

        var firstColumn = {
            width: '150px'
        };

        var responsiveTable = {
            "overflow-x":"auto"
        };

        return <div style={responsiveTable}><table className='table'>
            <thead>
                <tr>
                    <th style={firstColumn}></th>
                    <th>{strings.name}</th>
                    <th>{strings.team}</th>
                    <th>{strings.city}</th>
                    <th>{strings.country}</th>
                </tr>
            </thead>
            <tbody>
                <tr className='active'><td colSpan={6}><strong>{strings.women22km}</strong></td></tr>
                {Registered.renderRegisteredPersonRows(registeredPersons, "22 km", commonConstants.gender.female)}
                <tr className='active'><td colSpan={6}><strong>{strings.men22km}</strong></td></tr>
                {Registered.renderRegisteredPersonRows(registeredPersons, "22 km", commonConstants.gender.male)}
                <tr className='active'><td colSpan={6}><strong>{strings.women10km}</strong></td></tr>
                {Registered.renderRegisteredPersonRows(registeredPersons, "10 km", commonConstants.gender.female)}
                <tr className='active'><td colSpan={6}><strong>{strings.men10km}</strong></td></tr>
                {Registered.renderRegisteredPersonRows(registeredPersons, "10 km", commonConstants.gender.male)}
            </tbody>
        </table></div>;
    }

    private static renderRegisteredPersonRows(registeredPersons: Person[], distance: string, gender: string) {

        let filteredData;

        filteredData = registeredPersons.filter((person) => (person.distance == distance && person.gender == gender));   

        if (filteredData.length === 0) {
            return (<tr>
                <td colSpan={6}>{strings.noRegistrations}</td>
            </tr>)
        }
        return filteredData.map(function (registeredPerson, index) {
            return (<tr>
                <td></td>
                <td>{registeredPerson.name}</td>
                <td>{registeredPerson.team}</td>
                <td>{registeredPerson.city}</td>
                <td>{registeredPerson.country}</td>
            </tr>);
        })
    }
}

Now solutions is ready and this Azure App can be embedded to Wordpress site via Iframe.