HTML Form Direct to Google Sheet

This project is one (1) example showing how to set up a direct data transfer form that sends submitted data straight to Google Sheets, without the need for Google Forms.

Note: Updated for the Google Apps Script Editor 2022 version.

Requirements

A Google account
A text editor/website

Setup the Google Sheet

Go to Google Sheets and create a new sheet. This is where the submitted form data will be stored.

Set the following headers in the first row (You can update these headers later as well):

	A	B	C	...
1	Date	Name	Email	...

Create the Google Apps Script

In the window navigation menu of Google Sheet, find & click on Extensions > Apps Script. A new tab will open with Google Apps Script. Give the project a name (i.e. Mailing List or Subscriber List).

Replace the pre-set myFunction() {... section with the following code snippet:

const sheetName = 'SHEET_NAME' // Enter name of sheet tab from Google Sheet (i.e. Sheet1)
const scriptProp = PropertiesService.getScriptProperties()

function initialSetup () {
  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'))
    const sheet = doc.getSheetByName(sheetName)

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    const nextRow = sheet.getLastRow() + 1

    const newRow = headers.map(function(header) {
      // Returns date of input submission
      return header === 'Date' ? new Date() : e.parameter[header] // 
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

Remember to save the Apps Script project before progressing to the next steps.

Run the initialSetup Function

In the Apps Scripts window, find & click the Run option.

A pop-up will appear asking for permission(s). Click Review Permissions.

Because this script has not been reviewed by Google, it will generate a warning before it can continue. Click on Go to Mailing List (unsafe) for the script to obtain the correct permissions to update the form.

After allowing the script to acquire the correct permissions, you should see the following output in the script editor:

Now your script has the correct permissions to continue to the next step.

Add the Trigger to the Script

Select the project Triggers from the sidebar, then click the Add Trigger button.

In the window that appears, select the following options from the dropdown menus:

Choose which function to run: doPost
Choose which deployment to run: Head
Select the event source: From spreadsheet
Select the event type: On form submit

Then, select Save.

Publish the Apps Script

Now your script is ready to publish. Select the Deploy button, then choose the option New Deployment from the drop-down menu.

Click the Select Type icon and choose Web app from the options.

In the form that appears, select the following options:

Description: This can be anything you want. Just make it descriptive.
Web app → Execute As: Me
Web app → Who has access: Anyone

Then, select Deploy.

Important: Copy and save the web app URL before moving on to the next step.

Configure your HTML Form

Create a HTML form like the following, replacing YOUR_WEBAPP_URL with the URL you saved from the previous step.

<form id="YOUR_FORM_ID" method="POST" action="YOUR_WEBAPP_URL" name="input">
  <input name="Name" type="text" placeholder="Name" required>
  <input name="Email" type="email" placeholder="Email" required>
  <button type="submit">Submit</button>
</form>

Now when you submit this form from any location, the data will be saved in the Google Sheet.

Please Note: This process is case sensitive. This means the headers contained in the Google Sheet must EXACTLY match the casing of the name attribute(s) within the HTML form. However, the headers within the Google Sheet do not have to match the exact order of the HTML form.

Customized Redirect

If you want to intercept the submit event so the user isn't redirected to the webapp, but rather to a specific URL, you can do this by attaching a Javascript event listener to the form submission and creating the POST request yourself.

window.addEventListener("load", function() {
  const form = document.getElementById('YOUR_FORM_ID'); // Input the id of the form
  form.addEventListener("submit", function(e) {
    e.preventDefault();
    const data = new FormData(form);
    const action = e.target.action;
    fetch(action, {
      method: 'POST',
      body: data,
    })
    .then(() => {
      alert("Success! Your form has been submitted."); // Input a customized message to the user
      window.location.href = 'YOUR_REDIRECT_URL'; // Input desired URL to be redirected to after submission
    })
  });
});