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

0
votes

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:

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

1
htmlgoogle-apps-scriptgoogle-sheets
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
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

1 Answers

2
votes

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.