How to make a form using Google Sheets script editor

Question

I need to create a Google sheet with 3 columns (column A: Name, column B: Gender and column C: E-mail)

Then I need to add a script to the sheet to make a form (For some reasons I can not use google forms) with the three related questions we just added in the sheet

I can do the form.html code but I am not so much familiar with JavaScript to connect the form to the sheet once submitted

I think it is something like this:

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('form.html');
}

function update spreadsheet {
  var sheet = "get active spreadsheet"
  ...

I am not able to complete the above code, can anybody can help me with this?

Welcome to Stack Overflow. See How to Ask, and be sure to show what you have tried and researched. This issue is well explained by Google's tutorial — sinaraheneba

Andres Duarte Andres Duarte · Accepted Answer · 2019-08-08T08:58:17

You can deploy Apps Script as a Web App [1]. You'll need to create a html file [2] in which you'll put the form you want. Here [3] is well explain how to execute Apps Script functions with JavaScript in the html. Finally, in an Apps Script function you can use the SpreadsheetApp class to insert the values you want [4].

This would be an example code found on the documentation:

Index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      // Prevent forms from submitting.
      function preventFormSubmit() {
        var forms = document.querySelectorAll('form');
        for (var i = 0; i < forms.length; i++) {
          forms[i].addEventListener('submit', function(event) {
            event.preventDefault();
          });
        }
      }
      window.addEventListener('load', preventFormSubmit);

      function handleFormSubmit(formObject) {
        google.script.run.withSuccessHandler(updateUrl).processForm(formObject);
      }
      function updateUrl(url) {
        var div = document.getElementById('output');
        div.innerHTML = '<a href="' + url + '">Got it!</a>';
      }
    </script>
  </head>
  <body>
    <form id="myForm" onsubmit="handleFormSubmit(this)">
      <input name="myFile" type="file" />
      <input type="submit" value="Submit" />
    </form>
    <div id="output"></div>
 </body>
</html>

code.gs

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index');
}

function processForm(formObject) {
  var formBlob = formObject.myFile;
  var driveFile = DriveApp.createFile(formBlob);
  return driveFile.getUrl();
}

[1] https://developers.google.com/apps-script/guides/web

[2] https://developers.google.com/apps-script/guides/html/

[3] https://developers.google.com/apps-script/guides/html/communication

[4] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

How to make a form using Google Sheets script editor

1 Answers