DEV Community

Submit a Form to a Google Spreadsheet

Omer Lahav on January 30, 2020

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 ...

Read full post

whnguyen • Jul 19 '20

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.

Omer Lahav • Jul 20 '20

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

mauroian • Jan 22 '21

Hi Omer. Your script works very well and I am grateful to you!

I now have an issue: on submitting data from my HTML form to the sheet, how can I get a Redirect, so the user is sent to a Success or Error page?
Many thanks!

Omer Lahav • Jan 22 '21

Hi there mauroian, glad it helped!
About the redirecting part: How do you decide whether the user is being sent to the success/error page? Is it based on whether the form was sent successfully to the spreadsheet, by one of the form's fields or by another criterion?

mauroian • Jan 22 '21 • Edited

All I need is that the form was sent successfully to the spreadsheet. In which case, I'd like the user to be redirected to a success page :)

Omer Lahav • Jan 22 '21 • Edited

Try to find this line on the current code:

.then(response => console.log('Success!', response))

And try to replace it with (You can pick either one of the options with comments, no need for both of them):

.then((response) => {
    console.log('Success!', response);
    // similar behavior as an HTTP redirect
    window.location.replace("https://dev.to/");

    // similar behavior as clicking on a link
    window.location.href = "https://dev.to/";
});

Same goes for the error part, it should do the trick.

mauroian • Jan 22 '21

Done that. Now nothing gets submitted to the spreadsheet (and no redirect or anything else happens).

mauroian • Jan 22 '21

By the way, I think this might be important: in the original code, which was working, when I successfully submitted data to the spreadsheet, my HTML page did not change at all. It continued to show the input fields and submit button.

Basically, the line:
.then(response => console.log('Success!', response))

did nothing, as no 'Success!' message was shown in my page.

So, the issue could be in how/what the google script sends as response, or in the original code in index.html itself.

I am stuck at the moment... would be so happy if this worked!

Omer Lahav • Jan 23 '21

It wasn't meant to do anything else besides sending to the spreadsheet.
In the following line:

form.addEventListener('submit', e => {
.............
}

Everything between the { .... } will happen after the form is being submitted and you can modify it as much as you want, anything that happens there is up to your limits and not really relevant to this article or this mechanism :)
This whole article was only about the "sending-to-spreadsheet" part and nothing else, this is why nothing is happening other than that.
It already feels more of a tech support session than an easy js fix so just feel free to ping me wherever you'd like through one of the contact channels in my profile. The redirecting part is pure JS and can be found online but I'll try to help you out.

Shubham Jha • Apr 14 '21

can you please tell me where have you added index.html file, on appscript or your on vs code???

Akinhwan • Apr 12 '20 • Edited

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

Nicolas Llambías • Apr 12 '20

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?

Omer Lahav • Apr 12 '20

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 :)

Meera Menon • May 26 '20

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

Omer Lahav • May 26 '20

Which URL/codepen are you referring to?

Meera Menon • May 26 '20

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

Omer Lahav • May 26 '20

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

Jon Snow • May 23 '20 • Edited

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:

Omer Lahav • May 26 '20

Thanks for the feedback!

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

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

gbizindia • Jul 9 '20

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

Shubham Jha • Apr 14 '21

I was suprised how no-one got this doGet() not found error.
I know on many Youtubers have provided the same code, they run script and it worked for them, don't know how and data was put in spreadsheet as well.
Can't i upload more than one image??

BobyMarley • Apr 10 '22 • Edited

Hi Omer. I get error TypeError: Cannot read property 'getId' of null
Please tell me what is causing this error. Thnks!
drive.google.com/file/d/1bIhcizg8C...

Phillip Cantu • May 17

I solved it with ChatGPT. Replace the beginning code to:
var sheetId = 'YOUR_SPREADSHEET_ID'; // Replace 'YOUR_SPREADSHEET_ID' with the actual ID of your Google Sheet
var sheetName = 'Sheet1'; // Replace 'Sheet1' with the name of your sheet if it's different

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

try {
var doc = SpreadsheetApp.openById(sheetId); // Open the spreadsheet by ID
var sheet = doc.getSheetByName(sheetName);
THE REST OF THE CODE IS THE SAME
So the difference here is that you're putting the exact Google Sheet's ID

Phillip Cantu • May 17

Same! Did you solve it?

Shristi Sharma • Apr 20 '21 • Edited

Hi Omer! Thank you so much for this post. As a high schooler, your tutorial is much easier to follow than others.
I am sending data from a js file (without an HTML form) to my google sheet and I do get a "Success! {}" message in the console and the timestamp updates, but the rest of the columns on my sheet never update with data. I have checked to make sure my column headers and code match up and are case sensitive.
My code on Google Apps Script is the exact same as yours, and this is how I am sending data from my js file:

// Send Data to Google Sheets
  var formData = {
    'name': 'Bob Smith',
    'email': 'bob@example.com'
  };
  var options = {
    'method' : 'post',
    'payload' : formData
  };
  fetch(scriptURL, options)
      .then(response => console.log('Success!', response))
      .catch(error => console.error('Error!', error.message))

Could you please help me out?

Shristi Sharma • Apr 20 '21

Solved it! This link explains the solution: github.com/jamiewilson/form-to-goo...
But, one small change from the link above is not putting quotes in FormData() because it signifies a string, and the workaround should be to create a null HTML Form element.
So use: var sendingData = new FormData()
Instead of: var sendingData = new FormData('')

Solution code for js file:

saveToGoogleSheet () {
  const scriptURL = 'https://script.google.com/macros/s/.../exec'
  var sendingData = new FormData() // adjusted here
  sendingData.append('some_key', some_var_with_value)
  fetch(scriptURL, {method: 'POST', body: sendingData}) // adjusted here
    .then(response => console.log('Success!', response))
    .catch(error => console.error('Error!', error.message))
}

Drafty • May 6 '21

Hi Omer, I follow the steps I getting email message on this line --> form.addEventListener('submit', e => { saying that TypeError: Cannot read property 'addEventListener' of undefined I just copy what you told me.

PascalPointParty • Jun 12 '20

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 ?

Omer Lahav • Jun 12 '20 • Edited

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.

PascalPointParty • Jun 12 '20

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 !

lili mel • Jun 10 '20

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

Brett East • Mar 29 '20

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

Omer Lahav • Mar 30 '20 • Edited

It is, never claimed otherwise:

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

Brett East • Mar 30 '20

Nice, thanks for sharing

Omer Lahav • Mar 30 '22

It's true, FabForm looks like a great solution. (someone mentioned it as well last December: dev.to/fabform/comment/1kha4 )
The thing is I wrote this article two years ago, on January 2020. I looked for this kind of a solution for 7 (!!!) pages on google :)
Couldn't find any other solution than this one that was suggested by Jamie Wilson . If other, easier solutions came to the market since then - I'm truly happy because finding and implement this solution was a bit of a mess. [You can see it by the amount of tech support I had to give here in the comments section :) ]

Levi Nunnink • Sep 4 '20

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/

JonthueM • Mar 12 '23

What if I just want to push certain inputs not all?

Sagar • Apr 30 '21

What about security? anybody can be a fishing attack on spreadhseet. I can send tons of request by fetch method to overflow the spreadhseet. Is there any solution for this security threate?

joboswd • Aug 13 '20

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

Omer Lahav • Aug 16 '20

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.

abdelwahed • Jan 7 '21

I have this error saying
Google hasn't verified this app
The app is requesting access to sensitive info in your Google Account

nameless08 • Oct 24 '22

Hi Omer.
Great job.
I have a task to stack data on different sheets. Could you please tell me how to modify the script to pass the sheetName value from the site?

Omer Lahav • May 7 '22

You commented the same thing on Dec 17 '21 and on March 26.
How is this helpful? :)

nameless08 • Oct 24 '22 • Edited

Hi Omer
Nice work
I have the task of stacking data on different sheets. Could you please tell me how to modify the script to pass the "sheetName" value from the site?