Recently, I have embarked on creating an online occlusion test for basketball and instead of creating a database somewhere and going through all of that for what would be one table to store test results, I decided to set up 2 AWS Lambda functions that call Google Sheets API

The Web App

The web app is a simple form that has hidden fields for test results and then gets information about the user and sends an ajax request to AWS

Here we send a simple JSON post request with the rows to append to the spreadsheet taking this format

// each index will be a cell in the appended row

[ 200, 'NCAA D1', 5, 'Male', [Object object]]

This function calls AWS to append the above row to our Sheet

let sendTestResults = () =>
{
  $.ajax({
    type: 'POST',
    url: 'https://someid.execute-api.us-east-x.amazonaws.com/prod/addOcclusionResult',
    dataType: 'json',
    // stringify the post. notice the [] around row
    // we could post multiple rows at once doing something like [row_1, row_2]
    data: JSON.stringify([row]),
    success: (data) => {
      if(data.statusCode === 200){
        getLevelsAveragesFromAWS()
      }
      else {
        alert('Something went wrong')
      }
    }
  })
}

A cool thing about using Google Sheets as your DB is that you can have other tabs on the Sheet that run analytics on the data we just appended. Below we fetch cells from a separate tab that is doing some very basic averaging of our rows to show back to the user

let getLevelsAveragesFromAWS = () =>
{
  $.ajax({
    type: 'GET',
    url: 'https://someid.execute-api.us-east-x.amazonaws.com/prod/getLevelsResults',
    dataType: 'json',
    success: (data) => {

      PostFrames.style.display = 'none'

      PostForm.style.display = ''

      if(data.statusCode === 200){
        let rows = ''
        for(let level of data.body.values){
          rows += `<tr><td>${level[0]}</td><td>${level[1]}</td></tr>`
        }

        LevelsTable.innerHTML = rows
      }
      else {
        LevelsTable.innerHTML = `<tr><td>Error fetching results</td></tr>`
      }
    }
  })
}

Google API

The only thing required from the Google side is to

1. Create the Google Spreadsheet
2. Create a Google Service Worker and download credentials
3. Share the Spreadsheet with your Service Workers email address

AWS Lambda functions

AWS Lambda is a stateless server so it is much simpler and cheaper to keep running than a normal stateful server that needs to keep up with sessions and cookies and what not and needs its own instance on an AWS or Linode server or something like that

Below is the handler for the AWS Lambda function to add a row to our Sheet. It requires uploading a .zip file because it uses googleapis npm library so you need to include that for AWS Lambda

const google = require('googleapis')
const sheets = google.sheets('v4')
const RANGE = 'A1'
const SPREADSHEET_ID = 'GOOGLE SPREADSHEET ID'
const KEY = {JWT CREDENTIALS FROM GOOGLE API SERVICE WORKER ACCOUNT}

let jwtClient = new google.auth.JWT(
  KEY.client_email,
  null,
  KEY.private_key,
  [
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/drive.file',
    'https://www.googleapis.com/auth/spreadsheets'
  ],
  null
)

exports.handler = (event, context, callback) => {
  jwtClient.authorize((err, tokens) => {
    if (err) {
      console.log(err)
      return callback(err)
    }

    const body = {
      values: event
    }

    sheets.spreadsheets.values.append({
      auth: jwtClient,
      spreadsheetId: SPREADSHEET_ID,
      range: RANGE,
      valueInputOption: 'USER_ENTERED',
      resource: body
    }, (err, result) => {
      if(err) {
        console.log(err)
        return callback(err)
      } else {
        console.log('%d cells appended.', result.updates.updatedCells);
        callback(null, {"statusCode": 200, "body": result})
      }
    });
  });
};

To use an npm library in your Lambda function you need something like this

mkdir addRow
cd addRow
touch index.js   // the above goes in here
npm install googleapis
zip -r ../addRow.zip *  // this zips up files with the appropriate paths

AWS API Gateway

When setting up your Lambda function you will see a tab with triggers. This is where you can define what causes your function to run

For this example you would add an API Gateway that we can hit with our ajax request

A couple callouts for setting up your Gateway

1. Enable CORS - this allows us to call the endpoint from a different domain

   

2. Add the POST method using Create Method from the Actions dropdown

3. Deploy API - I almost lost a mouse before realizing that just because my tests from within the Amazon services were working didn't mean it was available publicly

That's It!

Now you can post data to your Google Spreadsheet at a minimal cost because you are only paying for the time your AWS Lambda function runs which is free for 1M requests I think, so it is great for simple hobby projects or POCs