CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Mahmoud Bayoumi
Mahmoud Bayoumi

Reputation: 63

How to make a form using Google Sheets script editor

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?

Upvotes: 0

Views: 819

Answers (1)

Andres Duarte
Andres Duarte

Reputation: 3350

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

Upvotes: 1

Related Questions