1

I am currently comparing a date input thru a google form by the user, against today's date, and I would like to stop the form data from being inserted into the google response spreadsheet if the date has not yet occurred. I've googled the heck out of this, but cannot find something similar to the DOM which has e.preventDefault(). Here is what my code looks like in the google script editor:

//first, verify the date of the submission is not before the date of the game (has not happened yet)
var todayDate = new Date();
var gameDate = new Date(ogkGameDate);
if (gameDate < todayDate)
{
  //send an email to admin and user that submission was rejected because the game date is in the future.
var subject = "WFRA Online Game Keeping Submission";

var message = "Hello! Thank you for submitting to the online game keeping system. \n";
message += "\n Unfortunately, the submission was rejected because this game has not yet occurred:";

message +="\n Game Keeper: " + ogkGameKeeper;
message +="\n Game Date: " + gameDate;
message +="\n Submission Date: " + todayDate;

//message += "\n Administrators can view the submission sheet at " + ogkURL + " \n";
message += "\n Please reply to this email if you experience technical issues. \n";


// Send yourself an email with a link to the document.
GmailApp.sendEmail("wfringette@gmail.com", subject, message,{bcc: "thetroutlakemonster@gmail.com", replyTo: "webmaster@westferrisringette.ca"});

//reject the submission to the sheet
    //HERE IS WHERE I WOULD LIKE TO CANCEL THE EVENT TO AVOID DATA INPUT TO THE SPREADSHEET

//exit the code

return;  }

Thanks in advance for any advice!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
WFRA Website
  • 11
  • 1
  • How is this code triggered? – TheMaster Sep 09 '20 at 15:18
  • Do you want to prevent the users from submitting the form, or prevent the population of the destination spreadsheet / the triggering of a script on form submit? – ziganotschka Sep 09 '20 at 15:29
  • My code is triggered in the onFormSubmit of my google form, and my goal is to prevent the population of the spreadsheet. Thanks so much – WFRA Website Sep 09 '20 at 15:37
  • Unless there is a better way to validate the data and stopping the submission? I"m open! lol – WFRA Website Sep 09 '20 at 15:43
  • 1
    I discovered just now that I can clear the active range, and then nothing goes thru. Is this just bad programming tho? – WFRA Website Sep 09 '20 at 15:51
  • You cannot really selectively populate the spreadsheet, bu what you can do is to synchronize the destination spreadsheet with your own version which you populate only with the entries that fulfill your condition - or you bind an onFormSubmit trigger directly to the form. – ziganotschka Sep 09 '20 at 15:54
  • 1
    Is your coded bounded to a form or to an spreadsheet? I suggest you to create a [mcve] This will help to make the answers more specific. – Rubén Sep 09 '20 at 16:14
  • @WFRAWebsite No it's not.(I wanted to suggest the same). Now that you've figured it out, Consider adding it as a answer in the answerbox below. See [answer] – TheMaster Sep 10 '20 at 10:25

2 Answers2

0

From a OP's comment

My code is triggered in the onFormSubmit of my google form, and my goal is to prevent the population of the spreadsheet. Thanks so much

If you set your form to send the form response to an spreadsheet it's not possible to prevent the population of the spreadsheet but it's possible if the on form submit trigger is used to populate the spreadsheet.

The way do to this is to set the proper condition before populating the spreadsheet.

By the way, there is no way to add something like e.preventDefault() to a Google Form as it's not possible to edit the form HTML or to add custom JavaScript.

The code included in the question includes a condition comparing two Dates objects but them can't be compared directly.

Instead of

if (gameDate < todayDate)

use

if (gameDate.getTime() < todayDate.getTime())

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
0

Sample how to populate onFormSubmit your own Spreadsheet with desired data

Provided your script is bound to your form proceed as following:

  • Create your own destination spreadsheet
  • modify your if condition to compare dates correctly as explained by Ruben
  • Important: If you want to send the email and avoid form submission for game dates in the future, the correct query would be if (gameDate.getTime() > todayDate.getTime())
  • Add an else statement to implement that the formdata shall be submitted to your custom spreadsheet if the game date has already occured

Sample

function bindMeOnFormSubmitTrigger(e){
  //please define
  var ogkGameKeeper="";
  //sample date
  var ogkGameDate = "2020-09-12";
  var todayDate = new Date();
  var gameDate = new Date(ogkGameDate);
  if (gameDate.getTime() > todayDate.getTime())  {
    //send an email to admin and user that submission was rejected because the game date is in the future.
    var subject = "WFRA Online Game Keeping Submission";
    
    var message = "Hello! Thank you for submitting to the online game keeping system. \n";
    message += "\n Unfortunately, the submission was rejected because this game has not yet occurred:";
    
    message +="\n Game Keeper: " + ogkGameKeeper;
    message +="\n Game Date: " + gameDate;
    message +="\n Submission Date: " + todayDate;
    
    //message += "\n Administrators can view the submission sheet at " + ogkURL + " \n";
    message += "\n Please reply to this email if you experience technical issues. \n";
    
    Logger.log("send email");  
    // Send yourself an email with a link to the document.
    GmailApp.sendEmail("wfringette@gmail.com", subject, message,{bcc: "thetroutlakemonster@gmail.com", replyTo: "webmaster@westferrisringette.ca"});
  } else{
    //paste form data into your own spreadsheet
    var formResponse = e.response;
    var rowData = [];
    var timeStamp = formResponse.getTimestamp();
    rowData.push(timeStamp);
    var itemResponses = formResponse.getItemResponses();
    for (var j = 0; j < itemResponses.length; j++) {
      var itemResponse = itemResponses[j];
      rowData.push(itemResponse);
    }
    //append the form submit data to your own custom sheet
    var mySheet = SpreadsheetApp.openById("PASTE HERE THE ID OF YOUR CUSTOME SPREADSHEET").getSheetByName("PASTE HERE THE NAME OF THE SHEET").appendRow(rowData);
  }
}
ziganotschka
  • 25,866
  • 2
  • 16
  • 33