HTML Form Data to Google spreadsheet

Question

I looked at various prior posted entries about submitting HTML form data to that of a Google Spreadsheet using JavaScript. Google Spreadsheet has however changed quite a bit since the prior posts and the way that <input> entries work, so I'm not sure how to get my form to write to the Google Spreadsheet. I essentially followed the solution from this URL. My code is as the following below. Does anyone have suggestions on how to resolve? The issue is with the missing entry fields. My form only has an id=firstname and id=lastname, so pretty straight forward to start out simple. Thanks in advance for you looking.

<html>
<head>
<script>
    $('#emailForm').one('submit',function(){
    var inputField = encodeURIComponent($('#emailForm').val());
    var baseURL = 'https://docs.google.com/a/google.com/forms/d/1RZYnGjYAMjl6LMXLdsWZgb9S3NqXZSO000tD2eObhLc/formResponse';
    var submitRef = '&submit=Submit';
    var submitURL = (baseURL + emailAddress + submitRef);
    $(this)[0].action=submitURL;
    $('#email').addClass('active').val('Thank You!');
setTimeout(function(){
  $('#form-container').hide();
  $('#update-form').animate({'width': '0px'},300,function(){
        $('#get-updates-link').hide();
          $('#email-submit').addClass('active').val('Thank You!');
      });
    },1000);
</script>
</head>
<body>

<form id="emailForm" action="https://docs.google.com/a/google.com/forms/d/1RZYnGjYAMjl6LMXLdsWZgb9S3NqXZSO000tD2eObhLc/formResponse">

  <input id="firstname" type="text" placeholder="enter firstname" name="firstname">
  <input id="lastname" type="text" placeholder="enter lastname" name="lastname">
  <button id="email-submit" type="submit">Submit</button>
</form>


</body>
</html>

I was tried to do this as an HTML page, calling via JavaScript the Google Form Spreadsheet. — user2690151, Jan 07 '16 at 01:02

score 1 · Answer 1 · edited May 23 '17 at 11:52

The way you are doing this is interesting, but there's a better way. For your current strategy to work, you need to create a Google Form that matches your data. You don't need to do that. It's better to create a Stand Alone Apps Script Web App, with a doPost(e) function in it. That doPost(e) function can capture the HTTP Request payload, and run the doPost(e) function when the Apps Script Web App url is called. The e parameter will capture the payload data from the POST request.

You can look at stackoverflow questions like this one:

Call a custom Apps Script function from external url

score 0 · Answer 2 · answered Jan 05 '16 at 02:17

Here's how I create simple forms in Google Apps Script.

In code.gs

 function doGet(e) {
     return HtmlService
     .createTemplateFromFile('Index')
     .evaluate()
     .setSandboxMode(HtmlService.SandboxMode.NATIVE)
}

function writeForm(form) {

    var customerName = form.myName;
    var paidPrice = form.myMoney;

    var ss = SpreadsheetApp.openById('1XPhhCko*******9mHvgNzFZ-mG8bWyFiBjn3B_B1bc');
    var sheet = ss.getSheetByName("customers");
    var newRow = sheet.getLastRow()+1;   
    var range = sheet.getRange(newRow, 1);

    range.setValue(customerName);
    range = sheet.getRange(newRow, 2);
    range.setValue(paidPrice);
}

Note that the HTML renders in NATIVE mode. Otherwise the submit button wouldn't work.

Now here's the Index.html form:

<body>
<h1> This is a customer form </h1>

<form id="customerForm">
Customer name: 
<input name="myName" type="text" placeholder="Example: Bob"> 

<br>

 Money spent:
 <input name="myMoney" type="number" placeholder="0.00">

 <br>

 <input type="submit" onclick="google.script.run.writeForm(this.parentNode)">

HTML is just an example, excuse the poor design and br tag. What this does, when submitted, is saving the customer name on A1 and the price paid on A2 (unless that row has data, and so it will write on a new row)

HTML Form Data to Google spreadsheet

2 Answers2