loading...
Cover image for Submit a Form to a Google Spreadsheet

Submit a Form to a Google Spreadsheet

omerlahav profile image Omer Lahav Updated on ・4 min read

Lately, I created an HTML form where the results are sent to a Google spreadsheet. I used it for analytics purposes, but it's useful even just for keeping track of the form's results. After trying all kinds of solutions and even searching beyond the 2nd page on Google, I found something that worked for me and I thought worth sharing.

Prerequisites: HTML and basic JavaScript knowledge.

The credit for this solution goes to Jamie Wilson who created and uploaded it to Github.

First, Create the Spreadsheet

  1. Go to Google Sheets and Start a new spreadsheet with the Blank template. Disclaimer: It doesn't really matter how you name the file.
  2. On the first row, write timestamp on the first column and email on the second column.

Create a Google Apps Script

  1. Click on Tools > Script Editor… which should open a new tab.
  2. Name the script Submit Form to Google Sheets or however you want for you to remember what the script does.
  3. Delete the function myFunction() {} block withing the Code.gs tab.
  4. Paste the following script in its place and File > Save:
var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

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

    var newRow = headers.map(function(header) {
      return header === 'timestamp' ? 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()
  }
}

If you want to better understand what this script is doing, check out the form-script-commented.js file in the repo for a detailed explanation.

Run the setup function

  1. Now, go to Run > Run Function > initialSetup to run this function.
  2. In the Authorization Required dialog, click on Review Permissions.
  3. Sign in or pick the Google account associated with this project.
  4. You should see a dialog that says Hi {Your Name}, Submit Form to Google Sheets wants to...
  5. Then click Allow

Add a new project trigger

  1. Click on Edit > Current project’s triggers.
  2. In the dialog click No triggers set up. Click here to add one now.
  3. In the dropdowns select doPost
  4. Set the events fields to From spreadsheet and On form submit
  5. Then click Save

Publish the project as a web app

  1. Click on Publish > Deploy as web app….
  2. Set Project Version to New and put initial version in the input field below.
  3. Leave Execute the app as: set to Me(your@address.com).
  4. For Who has access to the app: select Anyone, even anonymous.
  5. Click Deploy.
  6. In the popup, copy the Current web app URL from the dialog.
  7. And click OK.

IMPORTANT! If you have a custom domain with Gmail, you might need to click OK, refresh the page, and then go to Publish > Deploy as web app… again to get the proper web app URL. It should look something like https://script.google.com/a/yourdomain.com/macros/s/XXXX….

Input your web app URL

Open the file named index.html. On line 7 replace <SCRIPT URL> with your script url:

<form name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <button type="submit">Send</button>
</form>

<script>
  const scriptURL = '<SCRIPT URL>'
  const form = document.forms['submit-to-google-sheet']

  form.addEventListener('submit', e => {
    e.preventDefault()
    fetch(scriptURL, { method: 'POST', body: new FormData(form)})
      .then(response => console.log('Success!', response))
      .catch(error => console.error('Error!', error.message))
  })
</script>

As you can see, this script uses the Fetch API, a fairly new promise-based mechanism for making web requests. It makes a "POST" request to your script URL and uses FormData to pass in our data as URL parameters.

Adding additional form data

To capture additional data, you'll just need to create new columns with titles matching exactly the name values from your form inputs. For example, if you want to add first and last name inputs, you'd give them name values like so:

<form name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <input name="firstName" type="text" placeholder="First Name">
  <input name="lastName" type="text" placeholder="Last Name">
  <button type="submit">Send</button>
</form>

Then, you can create new headers with the exact, case-sensitive name values. Just go to the spreadsheet and add the names to the first row, each name in a different column.

Related Polyfills

Some of this stuff is not yet fully supported by browsers or doesn't work on older ones. Here are some polyfill options to use for better support.

  1. Promise Polyfill
  2. Fetch Polyfill
  3. FormData Polyfill

Since the FormData polyfill is published as a Node package and needs to be compiled for browsers to work with, a good option for including these is using Browserify's CDN called wzrd.in. This service compiles, minifies and serves the latest version of these scripts for us.

You'll want to make sure these load before the main script handling the form submission. e.g.:

<script src="https://wzrd.in/standalone/formdata-polyfill"></script>
<script src="https://wzrd.in/standalone/promise-polyfill@latest"></script>
<script src="https://wzrd.in/standalone/whatwg-fetch@latest"></script>

<script>
  const scriptURL = '<SCRIPT URL>'
  const form = document.forms['submit-to-google-sheet']
  ...
</script>

And... Yep, this is it.
After trying several different methods to do this "simple" action, that's the only one that worked for me - so I hope it helps. Feel free to share it/save it for future use and feel free to comment in case you have ideas to improve it!

Discussion

pic
Editor guide
Collapse
whnguyen profile image
whnguyen

Hi Omer - Came across this post trying to get my html form to write to the Google Sheet using another method initially. With your instructions, I finally got it working, so thank you! I did have one question - were you able to turn on notifications for this sheet? I've gone to Tools > Notification rules and haven't had any luck.

Collapse
omerlahav profile image
Omer Lahav Author

Happy the article helped!
Unfortunately, I haven't tried to turn the notifications on, but it seems YouTube is full of tutorials covering it :)

Collapse
romieleimor profile image
Jon Snow

Hi Omer,

I follow your tutorial and it works really great. Thank you. Unfortunately, when i tested it on IE 11. It is returning an error on the ES6 code even if i added the polyfill cdn.

Can you please tell me where did it go wrong?

Here's my JS code:

Collapse
omerlahav profile image
Omer Lahav Author

Thanks for the feedback!

Regarding the ES6 errors, try to check the code on:

  1. jshint.com/
  2. jslint.com/
  3. babeljs.io/repl/

It should tell you where are the errors and how to fix them.

Collapse
akinhwan profile image
Akinhwan

Not sure why I'm getting a 400 error? Just for context I'm using vue-form-wizard within a vue.js web app, the following code is in a method i can onComplete of the multi-step form.

submitToGoogleSheet() {
      const scriptURL =
        "https://script.google.com/a/talkaboutdepression.org/macros/s/...../exec";
      const formData = new FormData();
      formData.append("timestamp", new Date());
      formData.append("age", this.age);
      formData.append("gender", this.gender);
      formData.append("marital", this.marital);
      formData.append("ethnicity", this.ethnicity);
      formData.append("totalScore", this.totalScore);
      formData.append("geolocation", this.location);

      fetch(scriptURL, {
        method: "POST",
        mode: "no-cors",
        body: formData,
        headers: {
          "content-type": "multipart/form-data",
        },
      })
        .then((response) => console.log(response))
        .catch((error) => console.error(error.message));
    },

UPDATE 4/14/2020 I realized I had to go back and re run the function
"Now, go to Run > Run Function > initialSetup to run this function."

Thank you! hope anyone else who runs into this tries this first

Collapse
nicollambias profile image
Nicolas Llambías

Hello Omer! I'm migrating another script with this same use scenario, from JQuery.ajax to Fetch. After searching in the web, found this page and tried it.

I keep getting Success!, 200 response, but no data is actually inserted in me G-Sheet. Been through the issues in the original GitHub and many are saying the same. Did you actually make this work?

Collapse
omerlahav profile image
Omer Lahav Author

Hi Nicolas!
It was actually the only way that worked for me successfully.
Haven’t tried it with jQuery, but since you’ve managed to get a “success” message - did you check the table itself? The columns’ headlines should be identical (they’re case-sensitive) to the name attributes of the form’s inputs.

Anyway, feel free to contact me and send a message via the social networks (or the form in my website) that are in my profile and I’ll try to troubleshoot it with you :)

Collapse
myfashionbuys profile image
Meera

the url can be my form file in the codepen file as well?

Thread Thread
omerlahav profile image
Omer Lahav Author

Which URL/codepen are you referring to?

Thread Thread
myfashionbuys profile image
Meera

codepen.io/meeramenon07/pen/KKdomOb
I have this above form file is it possible to deploy this form to google online>?

Thread Thread
omerlahav profile image
Omer Lahav Author

I think it might work, but I haven't tried it myself so you better try and let us all know :)

Collapse
gbizindia profile image
gbizindia

hi Omer,
recently i was trying the script you mention above when i tried to add tigger its asking Select event source i have only three option 1) From Spreadsheet 2) Time Driven and 3) from calender what to choose 1 tried From spreadsheet but its not working i have used you code in armorn.com you can check index.html Source code please help its really urgent
Thanks in advance

Collapse
lilimel profile image
lili mel

Merci beaucoup , cette solution a marchee pour moi . Je l ai juste suivie a la lettre

Collapse
bretteast profile image
Brett East

This looks like a straight rip-off of github.com/jamiewilson/form-to-goo...? Almost verbatim.

Collapse
omerlahav profile image
Omer Lahav Author

It is, never claimed otherwise:

Credit

Just found that solution and decided to share it since it wasn’t the first thing popped while looking for it.

Collapse
bretteast profile image
Brett East

Nice, thanks for sharing

Collapse
pascal_levesque profile image
PascalPointParty

It is possible to change error message if email is not enter correctly ? I need to translate in french !

It is possible to have a "Thank You" message after submiting ?

Collapse
omerlahav profile image
Omer Lahav Author

Both are possible with JavaScript and have nothing to do with this mechanism.
Just create a hidden p/div tag and whenever the user click on the relevant button, make that tag appear with the relevant text.

Collapse
pascal_levesque profile image
PascalPointParty

Oh !! I think my knowledge is not high enough to understand !! I will try !
If you can give some more information, I learn very fast !!! Thank you !

Collapse
levinunnink profile image
Levi Nunnink

Hey Omer, this is a cool way of doing this. I actually just built a free service that does this without any need for Javascript code.

I'd love to hear what you think: sheetmonkey.io/

Collapse
joboswd profile image
joboswd

Bro can you run 2 scripts at the same time? 2nd script will be used for Sheet2

Collapse
omerlahav profile image
Omer Lahav Author

I haven't tried it, but it might work. Have you tried to copy the "doPost" function and change the sheet name? Just remember to do everything on the same spreadsheet.