Post Form to Google Spreadsheet

Here's how to post an html form to a google spreadsheet.

Why

Why might you want to do this? Because Google has a many servers, and you won't have to have one. Your form is light traffic. You can view this data in a spreadsheet instead of Postgres database. Cheap. Fun? I don't know.

Along with this article, here's a Google dev guide. Now it's fun.

Let's create an interest form, where someone can sign up in a contact form on your website.

Create spreadsheet

Go to sheets.new, and create a new Google Sheet (for the millionth time). Name the doc something like "my-project-forms" (arbitrary, but codey, like me). And name the first/only sheet "interest" (this name is actually important and used later).

Now give column names on Row 1, in lowercase, as they will eventually match the fields in your form. For me, start in Column A, and go right, specifying date, name, email, phone, message. Yours may vary.

In the menus at the top of the screen, on the far right, there's an Extension menu. Open that, and select App Scripts.

Create the App Script

Now you'll create a script that talks to your spreadsheet (sometimes called a "container" in App Scripts land).

Click Add file, then select Script. I will call mine

The app should have a script. Add file > script. Call mine interest.gs

As you may or may not have read in the delicious dev guide from Google, this .gs file is like .js, and it needs to have a doPost function in it to receive the request from your web form.

Here's my full implementation with some explainer comments:

const sheetName = 'interest'
const scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  // This is the spreadsheet that you just set up
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  const lock = LockService.getScriptLock()
  lock.tryLock(10000)
 
  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    // Open a specific sheet -- "interest", remember?!
    const sheet = doc.getSheetByName(sheetName)
 
    // Read that first row of column names -- name, email, phone, message
    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]

    // Get the first empty row after the last.
    const nextRow = sheet.getLastRow() + 1
 
    // For each header value, get the request param by the same name
    const newRow = headers.map(function(header) {
      return header === 'date' ? new Date() : e.parameter[header]
    })
 
    // Set that empty row with those values from the request
    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
 
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }
 
  catch (e) {
    console.error("catch", e)
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }
 
  finally {
    lock.releaseLock()
  }
}

This script defines how to put data into the spreadsheet upon request.

Deploy the script

Now we deploy the script. This creates a URL to host the script. POST requests will go there.

In the top right of the Apps Script UI, there's a "Deploy" button. Select New Deployment.

Now configure it. Make it a web app.

Execute as "Google account". This will allow writing to the spreadsheet.

Access as "Anyone". Without this open web access to the App Script from the web will not work.

Submit, and you will be given a URL for your script. Something like:

https://script.google.com/macros/s/abc123/exec

Create Your Web Form

Now create your website, preferrably using Frontpage 98.

In it, you'll have ye olde form:

<form action="#" class="interestForm" id="interest-form">
  <label style="display: none;">
    <input class="input" name="name-extra" placeholder="Name" type="text">
  </label>
  <label>
    <input class="input" name="name" placeholder="Name" required="" type="text">
  </label>
  <label>
    <input class="input" name="email" placeholder="Email" required="" type="email"> 
  </label>
  <label>
    <input class="input" name="phone" placeholder="Phone" required="" type="tel">
  </label>
  <label class="interestForm__message">
    <textarea class="input" name="message" placeholder="Message"></textarea>
  </label>
  <button class="btn -lg" id="interest-submit">Send</button>
</form>

No action. This will be handled in le Javascript.

name-extra is the poor man's honeypot, but is not required. Surely, the bots these days will see right through this. They see right through me.

Most importantly, the input name attributes match your spreadsheet header names exactly.

Submit Your Form

Now, write some sweet sweet scripts to submit your form.

;(function initInterestForm() {
  const form = document.querySelector("#interest-form");
  const submit = document.querySelector("#interest-submit");
  if (!form) return;

  form.addEventListener("submit", function (evt) {
    evt.preventDefault();
    const submission = new FormData(evt.target);
    if (submission.get("name-extra")) return;

    submit.disabled = true;
    fetch(
      "https://script.google.com/macros/s/abc123/exec"
      { method: "POST", body: submission }
    )
      .then(function (_res) {
        window.location.replace("/#interestSuccess");
        submit.disabled = false;
      })
      .catch(function (_err) {
        window.location.replace("/#interestError");
        submit.disabled = false;
      });
  });
})()

Note that it is posting to your App Script deployment.

On response, throw in that window.location has for a css-only toast, and your golden. Brown.