room.js

function myFunction() {
  // Constants
  var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1T_Wijb2dFYmjEL3yWSETw2yJAvHE04uzDrDLjstVp4Q/edit';
  var DATE_COLUMNS = {
    CHECK_IN:  5,
    CHECK_OUT: 6,
  };
  
  // Set up initial objects
  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var bookingSheet = ss.getSheets()[0];
  var roomSheet =    ss.getSheets()[1];

  // Get all the checkout-checkin values from booking sheet
  var dateValues = bookingSheet.getSheetValues(
    2, 
    DATE_COLUMNS.CHECK_IN, 
    bookingSheet.getLastRow() - 1, 
    2
  );

  // For each booking date range, check all room availabilities
  for (var row = 0; row < dateValues.length; row++) {
    var bookingRows = {
      checkIn:  findRowWithDate(roomSheet, dateValues[row][0], 1) || 0,
      checkOut: findRowWithDate(roomSheet, dateValues[row][1], 1) || 0
    };
    
    // Get all room availibilities for that date range
    var roomAvailabilities = roomSheet.getSheetValues(
      bookingRows.checkIn,
      2,
      (bookingRows.checkOut - bookingRows.checkIn),
      roomSheet.getLastColumn() - 1
    );
    
    // Condense the availabilities into a vector
    var availabilityVector = getAllAvailableRooms(roomAvailabilities);

    // Get the vector of rooms to compare the availability vector to
    var roomsVector = roomSheet.getSheetValues(1, 2, 1, roomSheet.getLastColumn() - 1)[0];
    
    // Construct the vector of available rooms (only available rooms will be listed in vector)
    var roomsData = new Array();
    
    for (var i = 0; i < roomsVector.length; i++) {
      if (availabilityVector[i]) {
        roomsData.push(roomsVector[i]);
      }
    }
    
    // Construct and add in data validation dropdown to bookings sheet
    var dataValidationRule = SpreadsheetApp.newDataValidation()
                                           .requireValueInList(roomsData, true)
                                           .build();
    
    // Insert the data validation field
    var cellToInsert = bookingSheet.getRange('J' + (row+2));
    cellToInsert.setDataValidation(dataValidationRule);
  }
}

// Given sheet, date, and column, find the row in that sheet which contains that date.
//   - column is used to check values in that column against date
function findRowWithDate(sheet, date, column) {
  var sheetCoords = {
    rows: sheet.getLastRow(),
    cols: sheet.getLastColumn(),
  };
  
  // Start iteration over booking rows using hacky for-loop
  for (var row = 2; row <= sheetCoords.rows; row++) {
    var otherDate = sheet.getRange(row, column).getValues()[0][0];

    if (otherDate.getTime() === date.getTime()) {
      return row;
    }
  }
  return null;
}

// Go through the room availibilities to find rooms avaliable the *entire* time
function getAllAvailableRooms(availabilitiesArray) {
  var COL_LENGTH = availabilitiesArray[0].length;
  var ROW_LENGTH = availabilitiesArray.length;
  
  var returnVector = new Array(COL_LENGTH);
  
  
  for (var col = 0; col < COL_LENGTH; col++) {
    
    for (var row = 0; row < ROW_LENGTH; row++) {
      // If any of the rooms are taken, it can't be used
      if (availabilitiesArray[row][col] !== 'Y') {
        returnVector[col] = false;
        break;
      } 
    }
    
    // If this column is null, then all rooms are free
    if (returnVector[col] === undefined) {
      returnVector[col] = true;
    }
  }
  return returnVector;
}