Google form edit response submission, automatically modify data in sheets, as well as calendar event

Question

So I managed to combine Google form, google calendar, as well as google sheets. When people submit the form (with a start date and end date), it will automatically appear in the google sheets as well as google calendar.

I modified the script to find conflict (to prevent double-booking), however, I just realized that even when the same person is trying to edit starting and ending date (via edit response), it will still show CONFLICT.

For example, someone books from date April 15th to April 17th, and he decided to change to April 16th to April 18th, because he previously booked 15-17, his new submission is having conflict with his own previous submission.

How can I add a function that will detect the same email to edit and submit data? (within empty day slot. Thanks in advance!

This is the function to create an object from sheet data

//Calendars to Output appointments
var cal = CalendarApp.getCalendarById('ID');
// Create an object from user submission
function Submission(){
  var row = lastRow;
  this.timestamp = sheet.getRange(row, 1).getValue();
  this.email = sheet.getRange(row, 2).getValue();
  this.name = sheet.getRange(row, 3).getValue();
  this.date = sheet.getRange(row, 4).getValue();
  this.dateout = sheet.getRange(row, 5).getValue();
  this.room = sheet.getRange(row, 6).getValue();
  this.day = sheet.getRange(row,8).setFormula("=(R[0]C[-3]-R[0]C[-4])+1").getValue();
  var fillDownRange = sheet.getRange(2, 8, lastRow-1);
  sheet.getRange(row,8).copyTo(fillDownRange);
  
   // Info not from spreadsheet
  this.status;
  this.dateString = (this.date.getMonth() + 1) + '/' + this.date.getDate() + '/' + this.date.getYear();
  this.dateOutString = (this.dateout.getMonth() + 1) + '/' + this.dateout.getDate() + '/' + this.dateout.getYear();
  this.calendar = eval('cal' + String(this.room));
  return this;
}

This is the function to detect conflict

function getEndTime(request){
  request.endTime = new Date(request.dateout.getTime() + 24 * 60 * 60 * 1000);
}
// Check for appointment conflicts
function getConflicts(request){
  var conflicts = request.calendar.getEvents(request.date, request.endTime);
  if (conflicts.length < 1) {
    request.status = "Approve";
  } else {
    request.status = "Conflict";
  }
}

This is the function to edit calendar

//update calendar (add)
function updateCalendar(request){
  var event = request.calendar.createEvent(
    "Booked",
    request.date,
    request.endTime
    )
}

ziganotschka ziganotschka · Accepted Answer · 2021-04-13T08:03:38

Instead of always retrieving the last row, you should retrieve the actual row of the latest submission

Mind that if people update their Google Form response, the submission row in the spreadsheet will not change - only the content.

You can retrieve the latest submitted / modified form response row with the event object event.range (provided your function is bound to a Google Sheets form submit trigger)
You can compare the modified row to the last row in the sheet
If the form response row is equal to the last row - a new response has been submitted

Sample:

function bindMeOnFormSubmitTrigger(event){
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var row = event.range.getRow();
  if (row == lastRow){
    // insert a new event as done before
    ...
  }
  else {
    // the submitted response was an edit of a previously submitted one
    // identify the already existing event (e.g. if you specified the respondent email address in the event description) and modify the event data to the newly submitted values
    var newFormResponseValues = event.values;
    this.timestamp = newFormResponseValues[0];
    ...
    
  }
}

If the form response row is differernt (smaller) then the last row in the sheet - it means that an existing form response has been edited (and consequently the event data might need to be modified in the calendar).
If you want to acccess the email of the respondent, you have to previously activate this option in the Google Forms UI settings:

Google form edit response submission, automatically modify data in sheets, as well as calendar event

2 Answers

Instead of always retrieving the last row, you should retrieve the actual row of the latest submission