0

OBJECTIVE

  • Create booking system using Google form, sheets and calendar;
  • There is one calendar for each room; the code and the sample data is based on room#41.
  • When there is a new submission, calendar event is automatically created.
  • Respondent can also edit their submission (Edit form URL).
  • When edited, old event will be deleted, and then new event will be created.

PROBLEM

When a form submission is received, a specific row should be edited. But updateCalendar() function loops through the whole data range; deleting event from the first row. How can I make sure that the function works only for the row that is being edited?

// This is the function to generate the edit URL (which works perfectly).

function getEditUrl(request) {
    var formRes = FormApp.openById('XXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    var sheetRes = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YYYYYYYYYY');
    var data = sheetRes.getDataRange().getValues();
    var urlCol = 10;
    var responses = formRes.getResponses();
    var timestamps = [],
        urls = [],
        resultUrls = [];
    for (var i = 0; i < responses.length; i++) {
        timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
        urls.push(responses[i].getEditResponseUrl());
    }
    for (var j = 1; j < data.length; j++) {
        resultUrls.push([data[j][0] ? urls[timestamps.indexOf(data[j][0].setMilliseconds(0))] : '']);
    }
    sheetRes.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);
};
// This is the function to update the calendar event. 

function updateCalendar(request) {
    var sheet = SpreadsheetApp.getActiveSheet();
    var lastRow = sheet.getLastRow();
    var range = sheet.getRange(2, 1, lastRow, 13);
    var values = range.getDisplayValues();
    var calendar = CalendarApp.getCalendarById('XXXXXXXXXXXXXXXXXXXXXXXX@group.calendar.google.com');
    for (var i = 0; i < responses.length; i++) {
        getConflicts(request);
        if (request.eventConflict == "conflict") {
            sheet.getRange(lastRow, 11).setValue("conflict");
            break;
        } else if (request.eventConflict == "approve") {
            var newEvent = calendar.createEvent("booked", request.date, request.endTime);
            var newEventId = newEvent.getId().split('@')[0];
            sheet.getRange(lastRow, 11).setValue("approve");
            sheet.getRange(lastRow, 12).setValue(newEventId);
            break;
        }
    }
    for (var j = 1; j < values.length; j++) {
        if (values[j][10] == "approve") {
            var eventEditId = calendar.getEventSeriesById(values[j][11]);
            eventEditId.deleteEventSeries();
            sheet.getRange(j + 2, 11).setValue("");
            getConflicts(request);
            if (request.eventConflict == "approve" && values[j][10].length > 1) {
                var newEvent = calendar.createEvent("booked", request.date, request.endTime);
                var newEventId = newEvent.getId().split('@')[0];
                sheet.getRange(j + 2, 11).setValue("approve");
                sheet.getRange(j + 2, 12).setValue(newEventId);
                break;
            } else {
                sheet.getRange(j + 2, 11).setValue("conflict");
                break;
            }
        }
    }
};

Edit:

This is the column description of the sheets (separated with |):

Timestamp Email Address name Check-in date Check-out date Room No. of people total day total edit URL Event Conflict Event ID

This is the function to get event conflicts in the calendar:

function getConflicts(request){
  var conflicts = request.calendar.getEvents(request.date, request.endTime);
  if (conflicts.length > 0) {
    request.eventConflict = "conflict";
  } else {
    request.eventConflict = "approve"
  }
  };

And this is the main function that will be triggered on formsubmit:

function main(){
  var request = new Submission(lastRow);
  getEndTime(request);
  draftEmail(request);
  updateCalendar(request);
};

This is the screenshot of the sheet:

screenshot of the sheets


FYI: This is my modified code based on Tedinoz answer

function updateCalendarTwo(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var responsename = "XXXXXXXX"
  var copyname = "YYYYYYYYY";
  var responsesheet = ss.getSheetByName(responsename);
  var copysheet = ss.getSheetByName(copyname);
  var calendar = CalendarApp.getCalendarById('XYXYXYXYXYXYX');
  // columns on copysheet
  var checkInCol = 4;
  var checkOutCol = 5;
  var roomNumCol = 6;
  var appCol = 11
  var eventIDCol = 12;
  var revCol = 14;

  var response = e.range;
  var rRow = response.getRow()

  var rLC = responsesheet.getLastColumn();
  var cLC = copysheet.getLastColumn();
  var rLR = responsesheet.getLastRow();
  var cLR = copysheet.getLastRow();

  if (rLR > cLR){
    var resprange = responsesheet.getRange(rLR,1,1,rLC);
    var respdata = resprange.getValues();
    copysheet.appendRow(respdata[0]);
    var eventTitle = copysheet.getRange(rRow,roomNumCol).getValue();
    var startDate = copysheet.getRange(rRow,checkInCol).getValue();
    var endDate = copysheet.getRange(rRow,checkOutCol).getValue().getTime()+ 24 * 60 * 60 * 1000;
    var conflicts = calendar.getEvents(new Date(startDate), new Date(endDate));
    if (conflicts.length < 1) {
    var event = calendar.createAllDayEvent(eventTitle, new Date(startDate), new Date(endDate));
    var eventID = event.getId().split('@')[0];
    copysheet.getRange(rRow,appCol).setValue("approve");
    copysheet.getRange(rRow,eventIDCol).setValue(eventID);
    } else {
    copysheet.getRange(rRow,appCol).setValue("conflict");
    }
  } else {
    var resprange = responsesheet.getRange(rRow,1,1,9);
    var respdata = resprange.getValues();
    var copyrespRange = copysheet.getRange(rRow,1,1,9);
    copyrespRange.setValues(respdata);

    var respAppRange = copysheet.getRange(rRow,appCol);
    var respApp = respAppRange.getValue();

    if (respApp == 'conflict') {
    var eventTitle = copysheet.getRange(rRow,roomNumCol).getValue();
    var startDate = copysheet.getRange(rRow,checkInCol).getValue();
    var endDate = copysheet.getRange(rRow,checkOutCol).getValue().getTime()+ 24 * 60 * 60 * 1000;
    var conflicts = calendar.getEvents(new Date(startDate), new Date(endDate));
    if (conflicts.length < 1) {
      var editedEvent = calendar.createAllDayEvent(eventTitle, new Date(startDate), new Date(endDate));
      var editedEventID = editedEvent.getId().split('@')[0];;
      copysheet.getRange(rRow,appCol).setValue("edited");
      copysheet.getRange(rRow,eventIDCol).setValue(editedEventID);
      } else {
      copysheet.getRange(rRow,appCol).setValue("conflict");
      };
    } else {
      var eventEditId = copysheet.getRange(rRow,eventIDCol).getDisplayValue();
      var editedEvent = calendar.getEventSeriesById(eventEditId);
      editedEvent.deleteEventSeries();
      var eventTitle = copysheet.getRange(rRow,roomNumCol).getValue();
      var startDate = copysheet.getRange(rRow,checkInCol).getValue();
      var endDate = copysheet.getRange(rRow,checkOutCol).getValue().getTime()+ 24 * 60 * 60 * 1000;
      var conflicts = calendar.getEvents(new Date(startDate), new Date(endDate));
      if (conflicts.length < 1) {
        var editedEvent = calendar.createAllDayEvent(eventTitle, new Date(startDate), new Date(endDate));
        var editedEventID = editedEvent.getId().split('@')[0];;
        copysheet.getRange(rRow,appCol).setValue("edited");
        copysheet.getRange(rRow,eventIDCol).setValue(editedEventID);
        } else {
        copysheet.getRange(rRow,appCol).setValue("conflict");
        };
      };
  
    var revRange = copysheet.getRange(rRow,revCol);
    var revOldValue = revRange.getValue();
    if (revOldValue == null || revOldValue == ""){
      revOldValue = 0;
    }
    var revNewValue = revOldValue+1;
    revRange.setValue(revNewValue); 
  }
}
9
  • Welcome. 1) You say the main function that WILL be triggered on formsubmit:". Have you actually run onformsubmit, and what was the result? 2) In function main(), what is your understanding of the purpose and result of this line?-var request = new Submission(lastRow); 3) function getEditUrl, line: sheetRes.getRange(2, urlCol, resultUrls.length).setValues(resultUrls); generates an error "Exception: The number of rows in the range must be at least 1." Have you ever run this code with no existing data in the sheet other than the title row? – Tedinoz Jun 9 at 5:30
  • How to make sure that when respondent edits his/her own response, it will always update event from the same column as the edit URL? Have you read the StackOverflow Q/A: How to check if current submission is editing response or a new response. This seems to be the basic issue that you are trying to resolve – Tedinoz Jun 9 at 6:18
  • Thanks! I will check this link, if I manage to solve the problem I will share here. cheers m8 – Sarimu Jun 9 at 7:16
  • getConflicts relies on getEvents(request.date, request.endTime). This would be useful for listing all the events on a given day, but how does it take into account the Room number? Would not a conflict exist only if there was an existing event for the same day(s) for the same Room number? – Tedinoz Jun 14 at 4:52
  • The Google Workspace (the "paid" version of Google Apps) has the ability to create rooms. Are you using the paid version or the free version of Google Apps? – Tedinoz Jun 14 at 4:55
0

You want to know how to get index of edited row.

The edited row is rRow. (Refer lines 27 & 28 of the answer)

var response = e.range;
var rRow = response.getRow()

The following answer is based on linking a spreedsheet to the form for output of responses; and copying of responses to second )working) sheet for calendar analysis and addition of relevant information.

The answer includes a revision count, based on How to check if current submission is editing response or a new response. This is probably not required for the OP's situation.


function wa154967_01(e) {

  // this function should be in a project lined to the spreadsheet
  // it should be added as an installable trigger 'On Form Submit'


  // setup spreadsheet and sheets
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var responsename = "Form Responses 1"
  var copyname = "CopyResponses";
  var responsesheet = ss.getSheetByName(responsename);
  var copysheet = ss.getSheetByName(copyname);

  // identify the calendar
  var calendar = CalendarApp.getCalendarById('INSERTYOURIDHERE');

  // columns on copysheet
  var roomNumCol = 2;
  var checkInCol = 4;
  var checkOutCol = 5;
  var eventIDCol = 6;
  var urlCol = 7;
  var revCol = 8;
 
  // Logger.log(JSON.stringify(e)); // DEBUG
  
  var response = e.range;
  var rRow = response.getRow()
  // Logger.log("DEBUG: response row = "+rRow);

  var rLC = responsesheet.getLastColumn();
  var cLC = copysheet.getLastColumn();
  var rLR = responsesheet.getLastRow();
  var cLR = copysheet.getLastRow();
  // Logger.log("DEBUG: response row = "+rRow+", response last row = "+rLR+", and copy last row = "+cLR);

  // this 'IF' distinguises between a NEW response and an UPDATE response

  if (rLR > cLR){
    // the last row of the response sheet is greater than the last row of the copy sheet
    // so the response is a new response
    // Logger.log("DEBUG: this is a new response")
    var resprange = responsesheet.getRange(rLR,1,1,rLC);
    var respdata = resprange.getValues();
    // Logger.log("DEBUG: the form response range is "+resprange.getA1Notation())
    // copy the form submission to the working sheet
    copysheet.appendRow(respdata[0]);

    // get the edit url
    var formRes = FormApp.openById('ENTERYOURFORMIDHERE');
    var formResponses = formRes.getResponses();

    // NOTE THE ADJUSTMENT OF '-2'
    // 1 - the response row includes the header row, so deduct 1 to account for the headers.
    // 2 - the response row is an index, but 'formResponses is an array which is zero-based, so deduct 1 to allow for zero based.
    // '1' to account for headers plus '1' to account for zero-based = '-2'
    var responseUrl = formResponses[+rRow-2].getEditResponseUrl();
    // Logger.log("DEBUG: Form response url = "+formResponses[+rRow-2].getEditResponseUrl())
    var copyrespRange = copysheet.getRange(rRow,urlCol);
    // Logger.log("DEBUG: the range for the edit url on copysheet =  "+copyrespRange.getA1Notation())

    // update the reponse url on the spreadsheet
    copyrespRange.setValue(responseUrl);

    // update the calendar
    // Logger.log('DEBUG: The calendar is named "%s".', calendar.getName());


    var eventTitle = copysheet.getRange(rRow,roomNumCol).getValue();
    var startDate = copysheet.getRange(rRow,checkInCol).getValue();
    var endDate = copysheet.getRange(rRow,checkOutCol).getValue();
    var extraDay = (1000 * 60 * 60 * 24); // for an all day event, end date is the following day
    // Logger.log("DEBUG: the event title is "+eventTitle+", the check-in date = "+startDate+", and the checkout date = "+new Date(endDate.getTime()+extraDay) )

    // create the event
    var event = calendar.createAllDayEvent(eventTitle, new Date(startDate), new Date(endDate.getTime()+extraDay));
    // get the event ID
    var eventID = event.getId();
    // Logger.log('DEBUG: Event ID: ' + eventID);

    // update the record with thr event id
    copysheet.getRange(rRow,eventIDCol).setValue(eventID);

  }
  else
  {
    // the last row of the response sheet is not greater than the last row of the copy sheet
    // so the response is an update
    // Logger.log("DEBUG: response is an update")

    var resprange = responsesheet.getRange(rRow,1,1,rLC);
    var respdata = resprange.getValues();
    // Logger.log("DEBUG: the response sheet input range is "+resprange.getA1Notation());

    var copyrespRange = copysheet.getRange(rRow,1,1,rLC);
    // Logger.log("DEBUG: the copy sheet output range is  "+copyrespRange.getA1Notation())
    // update the edited row
    copyrespRange.setValues(respdata);

    // update revision count
    var revRange = copysheet.getRange(rRow,revCol);
    // Logger.log("DEBUG: the copy sheet rev range = "+revRange.getA1Notation());
    var revOldValue = revRange.getValue();
    
    if (revOldValue == null || revOldValue == ""){
      revOldValue = 0;
    }
    var revNewValue = revOldValue+1;
    // Logger.log("DEBUG: the copy sheet old rev value = "+revOldValue+", and the new value will be "+revNewValue);
    
    // update the revision count
    revRange.setValue(revNewValue); 

  }

}

Form Response Sheet

Formresponses


Working sheet

working sheet


5
  • Wow! thank you so much for this! Creating another sheet to contain revision has never crossed my mind! Thank you again! I will update this post after I adapt it to the current code! Much appreciated!! – Sarimu Jun 14 at 8:02
  • If you have one calendar per room, you could list/define the various calendars within the script then select the appropriate calendar based on the room number, and allow the code to operate against that calendar. – Tedinoz Jun 15 at 5:44
  • thank you for the suggestion! I will edit my post and post my modified code so others can also see it – Sarimu Jun 15 at 10:32
  • EDIT: To ensure that the Calendar End date aligns with the Form End date, it's necessary to add an extra day to for the Form end date. If this is not done, then the duration of the event in Google calendar will be displayed one day short of the intended date. This was also noticed and suggested by @Sarimu. – Tedinoz Jun 16 at 1:47
  • FWIW: I found that if the Calendar title is edited to show the relevant rRow (such as var eventTitle = copysheet.getRange(rRow,roomNumCol).getValue()+"/"+rRow;, then it is creates a helpful (to this coder at least) distinguistion between events when viewing the Google Calendar. – Tedinoz Jun 16 at 1:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.