This is an attempt at porting some of my boss's code from VBA to Google Apps. Are there more elegant ways of doing this? I know that later versions of ECMAScript can do this more nicely but Google Apps Script seems to be missing some features from that.

function checkDoubleBooking() {
  var sData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Schedule").getDataRange().getValues();
  for (var row=scheduleStartRow-1; row<sData.length; row++) {
    var teacher = sData[row][scheduleTeacherCol-1];
    var room = sData[row][roomNoCol-1];
    if (!cellEmpty(teacher) || !cellEmpty(room)) {
      var semesterNo = sData[row][semesterCol-1];
      for (var clashRow=row+1; clashRow<sData.length; clashRow++) {
        var clashTeacher = sData[clashRow][scheduleTeacherCol-1];
        var clashRoom = sData[clashRow][roomNoCol-1];
        var clashSemester = sData[clashRow][semesterCol-1];
        var teacherClashPossible = (teacher == clashTeacher) && (!cellEmpty(teacher));
        var roomClashPossible = (room == clashRoom) && (!cellEmpty(room));
        var anyClashPossible = (teacherClashPossible || roomClashPossible);
        if ((anyClashPossible) && (semesterNo == clashSemester)) {
          originalSlots = sData[row][slotsCol-1].replace(" ","").split(",");
          clashSlots = sData[clashRow][slotsCol-1].replace(" ","").split(",");
          var clashPossible = false
          for (var originalSlot=0; originalSlot<originalSlots.length; originalSlot++) {
            if(clashSlots.indexOf(originalSlots[originalSlot]) != -1) {
              clashPossible = true
          if (clashPossible) {
            for (var week=1; week<=12; week++) {
              if (!cellEmpty(sData[row][weekColsInArray(week)])) {
                if (!cellEmpty(sData[clashRow][weekColsInArray(week)])) {
                  var colour;
                  if (roomClashPossible && teacherClashPossible) {
                    colour = staffAndRoomClashColour;
                  } else {
                    if (roomClashPossible) {
                      colour = roomClashColour;
                    } else {
                      colour = staffClashColour;
                  fillClashColour(row+1, weekColsInArray(week)+1, colour);
                  fillClashColour(clashRow+1, weekColsInArray(week)+1, colour);
  • 1
    \$\begingroup\$ Ok so you're running two loops. Google Apps affords you the ability to simply search for a particular cell/row etc. - why not simply let google handle that? If you get a match then you obviously have a clash. That to me seems a lot easier. but if your code works, then leave it. secondly i'd advise to use use sub functions to break up that huge function you have. it will make things more readable. \$\endgroup\$
    – BenKoshy
    May 8 '17 at 2:13
  • \$\begingroup\$ I really wanted to use a search but I couldn't see any method in Sheet or Spreadsheet that does it. There's the 2D Array utility library which has filters, but that's not part of Google Sheets. I know the Query language can do that, but that doesn't seem to be accessible from script, nor to have the power to do this on its own. \$\endgroup\$
    – Mark Green
    May 8 '17 at 13:07
  • 1
    \$\begingroup\$ Try this: gist.github.com/BKSpurgeon/e5afa3fc2a8fc1fc1472980b26e5a9cf you'll obviously have to change the code to suit your purposes. hope this helps \$\endgroup\$
    – BenKoshy
    May 8 '17 at 13:19
  • \$\begingroup\$ Oh, that's handy. The autocompletion for Arrays in the editor doesn't show findIndex or filter being available, but they are. \$\endgroup\$
    – Mark Green
    May 8 '17 at 15:10
  • \$\begingroup\$ I'd leverage the new sheet metadata feature. When a new row is added to the sheet I'd create a hash from the data and store it as a metadata id or key for the sheet. When you need to check for a double booking, create a hash for the data you want to evaluate and check it against the sheet metadata using a simple look up... I imagine you'd have to refactor your code considerably but it would make the code simpler and faster too. \$\endgroup\$ Feb 9 '18 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.