How to Send Data From an HTML Form to Google Sheets

Updated for Google Script Editor 2022 Version.

The following will teach you how to send data from an HTML form to a Google Sheet using only HTML and JavaScript.

This example shows how to set up a mailing list/newsletter form that sends data from an HTML form to Google Sheets, but you can use it for any sort of data.

1. Set up a Google Sheet

1. Go to Google Sheets and create a new sheet. This is where we'll store the form data.
2. Set the headers in the first row to whatever data you are collecting, they must match the input name you are using in your HTML form:

Example:

2. Create a Google Apps Script

Click on Extensions -> Apps Script. This will open new Google Script. Rename it to something like "Newsletter".

Replace function myFunction() { ... with the following code:

// Original code from https://github.com/jamiewilson/form-to-google-sheets
// Updated for 2021 and ES6 standards

const sheetName = '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) {
      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()
  }
}

Save the project ctrl+s before moving on to the next step.

3. Run the initialSetup function

You should see a modal/popup asking for permissions. Click Review permissions and continue to the next screen.

Because this script has not been reviewed by Google, it will generate a warning before you can continue. You must click the "Go to Newsletter (Unsafe)" for the script to have the correct permissions to update your form.

Once you click the "Go to Newsletter (Unsafe)" click "Allow".

After giving the script the correct permissions, run the script again, you should see the following output in the script editor console:

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

4. Add a trigger for the script

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

In the window that appears, select the following options:

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

Then select "Save". It will ask you to review permissions again, click "Allow".

5. Publish the project

Now your project is ready to publish. Select the Deploy button and New Deployment from the drop-down.

Click the "Select type" icon and select Web App.

In the form that appears, select the following options:

• Description: Newsletter blah blah blah (This can be anything that you want. Just make it descriptive.)
• Web app → Execute As: Me
• Web app → Who has access: Anyone

Then click Deploy.

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

6. Configure your HTML form

Configure your HTML form like the following, replacing WEBAPP_URL with the web app URL you saved from the previous step.

Make sure you update the the code with the proper information.

<form id="FORM_ID" method="POST" action="WEBAPP_URL">
  <input name="Email" type="email" placeholder="Email" required>
  <input name="Name" type="text" placeholder="Name" required>
  <button type="submit">Send</button>
</form>

OPTIONAL: To create a custom thank you page copy the javascript below. Replace FORM_ID in the HTML and JavaScript with the proper ID and https://www.YOUR_WEBSITE.com/thanks with the proper url for your thank you or home page.

<script type = "text/javascript" >
    window.addEventListener("DOMContentLoaded", function() {
        const yourForm = document.getElementById('FORM_ID');
        yourForm.addEventListener("submit", function(e) {
            e.preventDefault();
            const data = new FormData(yourForm);
            const action = e.target.action;
            fetch(action, {
                method: 'POST',
                body: data,
            }).then(() => {
                window.location.replace('https://www.YOUR_WEBSITE.com/thanks')
            })
        })
    }); 
</script>

Without this javascript code you will be redirected to a page that looks similar to the following.

Note: You will need to create a thank you page on your website. However a thank you page is not required, you could always redirect someone to your website home page, or previous page.

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

7. Please Note!

The input names on the google sheet are case sensitive. They MUST match the same casing as the form script.

For example:

If your input name is "firstANDlast",

<input name="firstANDlast">

A1 on your form would look like the following:

THIS WILL NOT WORK:

Additionally, to have the date included on your form you must use "Date", this is also case sensitive. A form with the date included would look similar to the following:

Thanks

Thanks to the following articles, projects, and people that inspired this guide;

• https://medium.com/@dmccoy/how-to-submit-an-html-form-to-google-sheets-without-google-forms-b833952cc175
• https://github.com/jamiewilson/form-to-google-sheets
• https://github.com/levinunnink/html-form-to-google-sheet