Connecting React App to Google Sheets using Google Apps Script

Koowoy
TIL

Reference:

For general structure - Youtube Channel Get __it Done!

For auto increment id on Google Sheets - Stack Overflow Question

Motivation

For my react app, I wanted to create a page where I could ask for the user’s email and name, maybe for future marketing purposes. Although storing these kind of data would conventionally require a backend server and a database connected to it. However, I wanted this project to be as lightweight as possible, preferably a Front End only project. Therefore, in search for a way to simply connect this app to a database API of sorts. The solution I found was Google Sheets and the Apps Script API.

Process

Google Sheets provides a automated access to the sheets using Apps Script, which works as a form of backend that could be used to read and write to rows in the sheet with data from outer source. When deployed, a url is created that can receive request and parse that information. Thus, when my react app sends a request of JSON to this url, Apps Script can parse this data and write it to Google sheets.

Setting Up Google Sheets and Apps Script

In my sheet I created three columns, where each would contain the primary key, name, and email. In the Apps Script of this sheet, I created a function that receives a JSON data, parses the body, and when there is valid name and email, this is written into the sheet database.

For the auto incrementing id number, I used the code found on stack overflow on this link.

function getNextId() {
  // Get properties
  const properties = PropertiesService.getScriptProperties()

  // Aquire the script lock. Prevents race condition
  const lock = LockService.getScriptLock()
  lock.waitLock(2000)

  // Get the saved ID and save the next value
  // Properties are string so it has to convert the type
  const id = parseInt(properties.getProperty('id_seq') || 0)
  properties.setProperty('id_seq', (id+1).toString())

  // Release the script lock (allows other executions to continue)
  lock.releaseLock()

  // Return the result
  console.log(id)
  return id
}

function doPost(e) {
  // Get next ID number  
  const id = getNextId();

  // Get the target google sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('test');

  // Parse JSON data from request
  const body = e.postData.contents;
  const bodyJson = JSON.parse(body);

  // If body data is valid, write row to database
  if(bodyJson.name && bodyJson.email) {
    ws.appendRow([id, bodyJson.name, bodyJson.email]);
  }
}

Deploy app on App Scripts

Using the graphical interface of Apps Script, deploy the “doPost” App, with options selected for web app, verifying with me, and access to everyone.

Write function on react app that fetches request

onSendButton = (event) => {
    // Prevents from sending get request (- not sure why)
    event.preventDefault()

    // Validate Email & Name Input
    if(this.validateInput()) {
        const url = 'https://script.google.com/macros/s/YOUR_APP_URL';
        fetch(url, {
            method: 'post',
            mode: 'no-cors',
            headers: {'Content-Type': 'application/json'},
            body: JSON.stringify({
                // Input data is stored as state on the react app
                name: this.state.name,
                email: this.state.email,
            })
            // After sending data, change route
        }).then(() => {this.props.onRouteChange('result')}) 
    }
}