Google sheets – How to get row index of a column, based on the index of edit URL from the same row?

I am coding a room booking system using combination of Google forms and Google calendar.

When there is a new booking order:

  • An event will be automatically created on the selected calendar.
  • An edit response URL will also be generated automatically and put in column 10 of the spreadsheet in the same row where the form answer was inserted.
// 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++) {
    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);

However, problem occurs when there are more than 2 orders; as the next order will delete the calendar event from the previous order.

// 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('');
    for (var i = 0; i < responses.length; i++) {
        if (request.eventConflict == "conflict") {
            sheet.getRange(lastRow, 11).setValue("conflict");
        } else if (request.eventConflict == "approve") {
            var newEvent = calendar.createEvent("booked",, request.endTime);
            var newEventId = newEvent.getId().split('@')(0);
            sheet.getRange(lastRow, 11).setValue("approve");
            sheet.getRange(lastRow, 12).setValue(newEventId);
    for (var j = 1; j < values.length; j++) {
        if (values(j)(10) == "approve") {
            var eventEditId = calendar.getEventSeriesById(values(j)(11));
            sheet.getRange(j + 2, 11).setValue("");
            if (request.eventConflict == "approve" && values(j)(10).length > 1) {
                var newEvent = calendar.createEvent("booked",, request.endTime);
                var newEventId = newEvent.getId().split('@')(0);
                sheet.getRange(j + 2, 11).setValue("approve");
                sheet.getRange(j + 2, 12).setValue(newEventId);
            } else {
                sheet.getRange(j + 2, 11).setValue("conflict");

My questions:

  1. 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? –> I have separate function that will send edit URL to respondents
  2. When there is more than two submission, the 3rd submission will delete event of the 2nd one. (I am sure the issue is on the updateCalendar() function).

I have been struggling so much for the past few days trying to figure out the best way to come up with best loop method. Any help / response is greatly appreciated.


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.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);

This is the screenshot of the sheet
screenshot of the sheets