How to check a value in a form using Google Apps Script

Question

I am working on a "deploy as a web app - Google Apps Script". The script uses the 'html service'. My sample script does write data to a Google Sheet but I hope to do more with your help.

1. I want to check to make sure the email value contains a valid email pattern 2. I want to only save data to the spreadsheet if it is a valid email pattern.

My failed attempt to accomplish this is listed below... the spreadsheet key is not valid.

Code.gs below

var submissioSSKey = '1xjsrUJaPxxxxlpxxtf_hoSYw6tkr4WzbEIHTrB6ysx4';
function doGet() {
   return HtmlService
      .createTemplateFromFile('index')
      .evaluate()
     .setSandboxMode(HtmlService.SandboxMode.IFRAME);

}

function processForm(myForm) {
  var email = myForm.email;
  var ss = SpreadsheetApp.openById(submissioSSKey); 
  var sheet = ss.getSheetByName('Data');

  sheet.getRange(sheet.getLastRow()+1, 1, 1, 2).setValues([[Date(), email]]);  
  }

index.html below

    <script>
function DataSaved(){    
  var emailPattern = /^[a-zA-Z0-9._]+[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z]{2,4}$/;
  if (emailPattern.myForm(email) == false) {
         document.getElementById('submitMessage1').innerHTML = "* a valid email is required";
}

  // document.getElementById("myForm").reset();  // Reset

    return false; // Prevent page refresh

};
 </script>

<style>
body {
    background-color: #000;
}

.container {
width:800px;
    margin:0px auto;
    text-align:left;
    padding:15px;
    border:1px dashed #333;
    background-color:#ccc;
}

p {
  color: red;
}

#title {
  font-size: 1.3em;
  line-height: 50%;
  color: #fff;
  text-align: left;
  font-weight: bold;
  margin: 0px;
  margin-bottom: 10px;
}

label {
    color: #fff;
}
</style>

<div class="container">
<br /><div id="title">Simple Form</div><br />
<form id="myForm">
<label>Email</label> <br />
<input type="text"   tabindex="1"  id="email" name="email" size="25" required/>
<div id="submitMessage1"></div><br /><br />
<br /><br />
<input type="button" tabindex="2"  id="Submit" value="Submit"  
  onClick="google.script.run.withSuccessHandler(DataSaved).processForm(this.form)"/>
</form>
</div>

Regards,

Chris

Answer 1

Right now, the code for the onclick event is inside the input element:

<input type="button" tabindex="2"  id="Submit" value="Submit" onClick="google.script.run.withSuccessHandler(DataSaved).processForm(this.form)"/>

And the check for the correct email format isn't done until form data is sent to the server. I'd change the flow of the code. Call a function in the HTML script tag first, then if the email format passes, use the google.script.run API to send the form data to the server.

<input type="button" tabindex="2"  id="Submit" value="Submit" onClick="validateEmailFormat(this.form)"/>

Script Tag

<script>
  function validateEmailFormat(argFormObject){
    var email = myForm.email;
    var emailPattern = /^[a-zA-Z0-9._]+[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z]{2,4}$/;
    if (emailPattern.myForm(email) === false) {
      document.getElementById('submitMessage1').innerHTML = "* a valid email is required";
    } else {
      google.script.run
        .withSuccessHandler(DataSaved)
        .processForm(email)

    };
  };

  function DataSaved() {
    document.getElementById('submitMessage1').innerHTML = "The email was saved"; 
  };
</script>

Code.gs

function processForm(argEmail) {
  var ss = SpreadsheetApp.openById(submissioSSKey); 
  var sheet = ss.getSheetByName('Data');

  sheet.getRange(sheet.getLastRow()+1, 1, 1, 2).setValues([[Date(), argEmail]]);  
}

This code gets the email out of the form object in the script tag before sending it to the server. No point in sending the entire form object to the server if all you need is the email value.

Answer 2

I have the main concepts of validating Google Apps Script form data using Google's 'html service'and writing to a Google Spreadsheet in working code thanks to Sandy. My working code might be useful to others so I am posting the code here. You will need to replace the 'submissioSSKey' value with the value in your spreadsheet url to make this work. You will also need to run doget and accept permissions.

code.gs should look like this

var submissioSSKey = '1xjsrUJaPxxxxlpxxtf_hoSYw6tkr4WzbEIHTrB6ysx4';
function doGet() {
   return HtmlService
      .createTemplateFromFile('index')
      .evaluate()
     .setSandboxMode(HtmlService.SandboxMode.IFRAME);

}

function processForm(myForm) {
  var email = myForm.email;
  var ss = SpreadsheetApp.openById(submissioSSKey); 
  var sheet = ss.getSheetByName('Data');

  sheet.getRange(sheet.getLastRow()+1, 1, 1, 2).setValues([[Date(), email]]);  
  }

index.html should look like the following

<script>

function validateEmailFormat(argFormObject){
    console.log('start')
    var emailEntry = document.forms["myForm"]["email"].value;
  if (emailEntry.length == 0) {
      document.getElementById('submitMessage1').innerHTML = "* a valid email is required";
    } else {      
      google.script.run
        .withSuccessHandler(DataSaved)
       .processForm(myForm)
    }
  };

  function DataSaved() {
    document.getElementById('submitMessage1').innerHTML = "The email was saved"; 
  };

 </script>

<style>
body {
    background-color: #000;
}

.container {
width:800px;
    margin:0px auto;
    text-align:left;
    padding:15px;
    border:1px dashed #333;
    background-color:#ccc;
}

p {
  color: red;
}

#title {
  font-size: 1.3em;
  line-height: 50%;
  color: #fff;
  text-align: left;
  font-weight: bold;
  margin: 0px;
  margin-bottom: 10px;
}

label {
    color: #fff;
}
</style>

<div class="container">
<br /><div id="title">Simple Form</div><br />
<form id="myForm">
<label>Email</label> <br />
<input type="text"   tabindex="1"  id="email" name="email" size="25" required/>
<div id="submitMessage1"></div><br /><br />
<br /><br /> 
  <input type="button" tabindex="2"  id="Submit" value="Submit" onClick="validateEmailFormat(this.form)"/>
</form>
</div>