CODEWITHSUNDEEP
google-apps-scriptgoogle-forms
Colin Smith
Colin Smith

Reputation: 27

Redirect to a different section of a Google Form

I have a google form that has many sections. It is linked to a Google Sheet. The form is an entry form to a sports tournament and it has different age groups

The google sheet has a list of age groups in column B, in column c it shows the maximum number of spaces available for that age group, in column D it shows the current number of entries and in column E it show the number of place left. The entries are calculated from the responses of the google form.

I need the form to automatically redirect to a different section if an age group has 0 spaces left. Each age group has its own section for a reserve list.

I have this code currently

function onFormSubmit(e) {
  try {
    var form = FormApp.openById('1EQ5gH6fq9hCBLERo3pTR0YX5AUHBPUFJ47rvWPogGQc'); // Replace with the actual Form ID
    var spreadsheet = SpreadsheetApp.openById('1OU_QDWXxoBBhGSqN9KbBX0EdVlRRL5ZWQrdSmbIh22Y'); // Replace with the actual Spreadsheet ID
    var overviewSheet = spreadsheet.getSheetByName('Overview'); // Replace with the correct sheet name containing the capacity information
    var ageGroupsColumnIndex = 2; // Column index for age groups (assuming age groups are in column B)
    var maxTeamsColumnIndex = 3; // Column index for maximum number of teams (assuming in column C)
    var currentTeamsColumnIndex = 4; // Column index for current number of teams (assuming in column D)

    // Mapping of age groups to their corresponding maximum capacities and reserve section numbers
    var ageGroupToSectionMap = {
      "Under 7": {
        entrySection: 3,
        reserveSection: 19
      },
      "Under 8": {
        entrySection: 4,
        reserveSection: 20
      },
      "Under 9": {
        entrySection: 5,
        reserveSection: 21
      },
      "Under 9 Girls": {
        entrySection: 6,
        reserveSection: 22
      },
      "Under 10": {
        entrySection: 7,
        reserveSection: 23
      },
      "Under 10 Girls": {
        entrySection: 8,
        reserveSection: 24
      },
      "Under 11": {
        entrySection: 9,
        reserveSection: 25
      },
      "Under 12": {
        entrySection: 10,
        reserveSection: 26
      },
      "Under 12 Girls": {
        entrySection: 13,
        reserveSection: 29
      },
      "Under 12 PAN": {
        entrySection: 11,
        reserveSection: 27
      },
      "Under 13": {
        entrySection: 12,
        reserveSection: 28
      },
      "Under 14": {
        entrySection: 14,
        reserveSection: 30
      },
      "Under 14 Girls": {
        entrySection: 15,
        reserveSection: 31
      },
      "Under 16 PAN": {
        entrySection: 16,
        reserveSection: 32
      }
      // Add more age group entries if needed
    };

var data = overviewSheet.getDataRange().getValues();
var ageGroups = data.map(function (row) {
  return row[ageGroupsColumnIndex - 1];
});
var maxTeams = data.map(function (row) {
  return row[maxTeamsColumnIndex - 1];
});
var currentTeams = data.map(function (row) {
  return row[currentTeamsColumnIndex - 1];
});


    for (var i = 0; i < ageGroups.length; i++) {
      var currentAgeGroup = ageGroups[i];
      Logger.log('Processing age group:', currentAgeGroup);

      var maxCapacity = maxTeams[i];
      var currentCapacity = currentTeams[i];
      var spacesLeft = maxCapacity - currentCapacity;

      if (currentAgeGroup in ageGroupToSectionMap) {
        var sectionInfo = ageGroupToSectionMap[currentAgeGroup];
        var entrySectionNumber = sectionInfo.entrySection;
        var reserveSectionNumber = sectionInfo.reserveSection;

        Logger.log('Entry section number:', entrySectionNumber);
        Logger.log('Reserve section number:', reserveSectionNumber);

        Logger.log('Processing age group:', currentAgeGroup);
Logger.log('Entry section number:', entrySectionNumber);
Logger.log('Reserve section number:', reserveSectionNumber);
Logger.log('Item Responses:', itemResponses);



        var itemResponses = e.response.getItemResponses();
        for (var j = 0; j < itemResponses.length; j++) {
          var itemResponse = itemResponses[j];
          var item = itemResponse.getItem();
          if (item.getType() === FormApp.ItemType.PAGE_BREAK) {
            var pageNumber = item.asPageBreakItem().getPageNumber(); // Use getPageNumber() instead of getPageNavigationType()
            Logger.log('Page number:', pageNumber);
            if (pageNumber === entrySectionNumber) {
              if (spacesLeft > 0) {
                // There are spaces left for this age group, continue with the form submission.
                Logger.log('Spaces left for', currentAgeGroup);
                return;
              } else if (reserveSectionNumber) {
                // Spaces are full for this age group, redirect to the reserve section if available.
                var reserveSectionItem = form.getItems(FormApp.ItemType.PAGE_BREAK)
                  .filter(function (item) {
                    return item.asPageBreakItem().getPageNumber() === reserveSectionNumber;
                  })[0];
                if (reserveSectionItem) {
                  form.setDestination(FormApp.DestinationType.PAGE, reserveSectionNumber);
                  Logger.log('Redirecting to reserve section for', currentAgeGroup);
                  return;
                }
              }
            }
          }
        }
      }
    }
  } catch (error) {
    Logger.log('Error: ' + error.toString());
  }

}

The form is not automatically redirecting to the required sections. The execution log provides me with this information

Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:11 AM Info    Reserve section number:
Aug 8, 2023, 8:24:11 AM Info    Item Responses:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Processing age group:
Aug 8, 2023, 8:24:11 AM Info    Entry section number:
Aug 8, 2023, 8:24:12 AM Info    Reserve section number:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Reserve section number:
Aug 8, 2023, 8:24:12 AM Info    Entry section number:
Aug 8, 2023, 8:24:12 AM Info    Item Responses:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Entry section number:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Reserve section number:
Aug 8, 2023, 8:24:12 AM Info    Entry section number:
Aug 8, 2023, 8:24:12 AM Info    Reserve section number:
Aug 8, 2023, 8:24:12 AM Info    Item Responses:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Entry section number:
Aug 8, 2023, 8:24:12 AM Info    Reserve section number:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Entry section number:
Aug 8, 2023, 8:24:12 AM Info    Reserve section number:
Aug 8, 2023, 8:24:12 AM Info    Item Responses:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:
Aug 8, 2023, 8:24:12 AM Info    Processing age group:

Can anyone help me make this code work? I feel like it is nearly there but something isn't quite right in the code

The sample sheet link is https://docs.google.com/spreadsheets/d/1OU_QDWXxoBBhGSqN9KbBX0EdVlRRL5ZWQrdSmbIh22Y

The sample form link is https://docs.google.com/forms/d/1EQ5gH6fq9hCBLERo3pTR0YX5AUHBPUFJ47rvWPogGQc/edit

You will see on the sheet that it shows the age groups in column B, the maximum number of teams for each age group in column C, the current number of entries in column D and then the number of places remaining in column E.

In the form, you will see each age group has its own section. What I would like to happen is that when an age group is full, the form automatically diverts the user to the reserve list. For example, the under 9's is at full capacity, so when the user selects the under 9 age group, I need the form to go the the Under 9 Reserve List section automatically (rather than me having to manually change it)

Thanks for your anticipated help

Upvotes: 0

Views: 161

Answers (1)

Twilight
Twilight

Reputation: 2645

Replicating your question, It seems that what you are trying to do is not possible. You are setting up a question with multiple conditions which is a limitation of Google Forms "Go to Section". You are checking the age groups and status/number of places left which will be the basis of the next section. This also cannot be done via apps script since the script will only run after submitting the form and no way it can control the flow of the form.

You might consider changing your approach using web app or creating separate forms.

Reference: Is there a way to specify multiple conditions for "Go to section" feature in Google Forms?

Upvotes: 1

Related Questions