How to correctly write into Google Spreadsheet with Google Apps Script?

Question

I'm creating a webpage with a form and I have to send data to a Google Spreadsheet. I found a good code searching this site but I don't know how to edit it to store my data in Google Spreadsheet.

My form is made this way

<form name="reqForm" id="reqForm" method="get" action="SCRIPT" accept-charset="UTF-8">
            <input type="hidden" name="reqID" id="reqID" />
            <label for="name">Name:</label>
               <input type="text" name="Name" id="Name"/>
            <label for="surname">Surname:</label>
               <input type="text" name="surname" id="surname"/>
            <label for="SERIAL">Serial:</label>
               <input type="text" name="serial" id="serial"/>
            <label for="email">E-mail:</label>
               <input type="text" name="email" id="mail"/>
            <label for="text">Request:</label>
               <textarea id="text"></textarea>
               <input type="submit" value="Send" />
</form>

The script is the same, from the example I linked (I just changed the function name because of an error)

function doPost(e){

   var id = "";
   var name = e.parameter['name'];
   var surname = e.parameter['surname'];
   var serial = e.parameter['serial'];
   var eMail = e.parameter['email'];
   var text = e.parameter['text'];
   var date = new Date();
   var ans = ""
   var flag = "WIP";

   var vals = [id, date, name, surname, serial, eMail, text, ans, flag];

  var sheetObj = SpreadsheetApp.openById("myKey").getSheetByName('Requests').appendRow(vals);
  // return ContentService.createTextOutput(e);

}

If I fill the form in this way:

Name: John
Surname: Doe
Serial: 123456
Email: [email protected]
Request: Hello

in my Spreadsheet I see data in this order: [DATE] [Serial] [Mail] [Name] [Surname]

How can modify the function to put my data into the spreadsheet in some kind of order that I can decide?

[UPDATE]: I updated the code: the result of this is a line correctly ordered but filled with "undefined"...

The method in the form tag is GET: method="get" The function name in your Apps Script file is doPost() That won't work. You either need to make a POST request, or use doGet() So, . . . GET for doGet() and POST for doPost() They need to match. — Alan Wells
– Alan Wells, Commented Jun 5, 2017 at 2:19
Yes I figured it out but when I try with method="post" I got an error, it says it cannot find function doPost. That's why I wrote "I had to change because of an error" — Simone
– Simone, Commented Jun 5, 2017 at 4:18

Parag Jadhav · Accepted Answer · 2017-06-04 21:34:03Z

3

After submitting the form, you can get form parameters using e.parameter[<parameter_name>].

For example to get the surname,

var surname = e.parameter['surname'];

To insert form data in Google Spreadsheet,

var sheetObj = SpreadsheetApp.openById("myKey").getSheetByName("sheetname");
//Here you can shuffle the order of values anyway you want.
sheetObj.appendRow([DATE, Serial, Mail, Name, Surname]);
//or you can do it like this
sheetObj.appendRow([DATE, Mail, Name, Surname, Serial]);

Hope this helps.

answered Jun 4, 2017 at 21:34

Parag Jadhav

1,8992 gold badges26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Simone Over a year ago

Hi! Thanks for your reply! I tried this way but it seems the function cannot identify the parameters. I followed your advice but now it only gives me the date because I generate it in my code, the other values are disappeared 😕

Parag Jadhav Over a year ago

Did you deploy the script as a web app with a newer version? If you haven't the script will execute the older version of the code

Simone Over a year ago

Yes but it seems not working at all (I changed the output to check and it's still the same...) I'll try creating a brand new script...

Parag Jadhav Over a year ago

If possible share a dummy script with us

Simone Over a year ago

Ok I created a new project and the filling is working except for the fact I now see my line full of "undefined" 🤦‍♂️

|

Collectives™ on Stack Overflow

How to correctly write into Google Spreadsheet with Google Apps Script?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related