onClick event for Google Forms Submit button

Question

This is driving me crazy, I have a simple Google Form that accepts subscriber code and their slot timings. My purpose is to get data via Google Forms and save it to Google Sheets without having any clashes in the slots timings.

To show you an example, I am attaching an image of my form and sheet. After the first entry was done, if another slot is to be given to same subscriber, we need to ensure that the slot timings do not clash with the saved timings in google sheet that is assigned to the same subscriber. If you see the second entry, it just accepts it and for this we need a validation check which is not available. My question is, is it even possible to have an onClick validation on the submit button? I have been searching for it on google like crazy but haven't got a solution. If this is not possible to do it in Google forms, what are my alternatives or is there any workaround available?

Eagerly awaiting responses for my query. Thank you.

Muhammet Yunus · Accepted Answer

As @Cooper pointed out, it seems best that you create your own web app.

After a few hours of work I've made a web app that will work for you using Google Apps Script. Here are the spreadsheet and script file.

Server Side Codes:

var ss = SpreadsheetApp.getActiveSheet()

function doGet() {
  return HtmlService.createTemplateFromFile('form').evaluate()
}

function submitData (data) {
  
  ss.appendRow([new Date(),data.ssCode, new Date(data.startDateTime), new Date(data.endDateTime)])
}

function isAvailible (start, end) {
  var data = ss.getDataRange().getValues()
  var isAvailible = true;
  var message = '';

  data.forEach(row => {

    if(start > row[2].valueOf() && start < row[3].valueOf() || end > row[2].valueOf() && end < row[3].valueOf()) {
      isAvailible = false;
      message = new Date(row[2].valueOf()) +'
 to ' + new Date(row[3].valueOf()) + '
 isn\'t availible.'
    }
  })

  var response = {status: isAvailible, message: message}

  return response;
  
}

Client Side Codes:

M.FormSelect.init(document.querySelectorAll('select'));

var dps = document.querySelectorAll('.datepicker');
var dpsInstances = M.Datepicker.init(dps, {
  showClearBtn: true,
  autoClose: true
});

dpsInstances[0].options.onSelect = function(day) {
  dpsInstances[1].setDate(day);
  endDate.value = dpsInstances[1].toString()
  M.updateTextFields();
}

var tps = document.querySelectorAll('.timepicker');
var tpsInstances = M.Timepicker.init(tps, {
  twelveHour: false,
  showClearBtn: true,
  autoClose: true
});

function formSubmit(data) {

  var startDate = new Date(data.startDate.value);
  var startTime = data.startTime.value.split(':');
  startDate.setHours(startTime[0]);
  startDate.setMinutes(startTime[1]);

  var endDate = new Date(data.endDate.value);
  var endTime = data.endTime.value.split(':')
  endDate.setHours(endTime[0]);
  endDate.setMinutes(endTime[1]);
  
  if (startDate.valueOf() >= endDate.valueOf()) {
    M.toast({html: 'End date should be greater then start date.'})
    return;
  }
  
  var dataToSubmit = {
    ssCode: data.ssCode.value,
    startDateTime: startDate.valueOf(),
    endDateTime: endDate.valueOf()
  }

  google.script.run.withSuccessHandler(function(response) {
    if (response.status) {
      google.script.run.withSuccessHandler(function() {
        myForm.reset()
        M.toast({
          html: "Successful"
        })
      }).submitData(dataToSubmit)
    } else {
      M.toast({
        html: response.message
      })
    }

  }).isAvailible(dataToSubmit.startDateTime, dataToSubmit.endDateTime)


}

body {
  background: rgb(244, 235, 234)
}

.outer-field {
  border-radius: 15px;
  background: white;
  height: 150px;
  margin: 10px;
  padding: 20px;
}

.title {
  padding-left: 2%;
  font-weight: bold;
}


  
    
    
    

  
  
  
    
      
        
          Slot Timings
        
        
          
            Subscriber Code
            
              
            
          
          
            From
            
              
                
                Date
              
            
            
              
                
                Time
              
            
          
          
            To
            
              
                
                Date
              
            
            
              
                
                Time

onClick event for Google Forms Submit button

Answers (2)

Sheet Script:

Sample Data:

Trigger:

Emails confirm:

Emails conflict:

Output:

Note:

Related Questions