Activate multiple forms at the same time

Question

I organize my students to take the test by giving each student a Google form. To set the time to accept and reject responses for all forms, I used a trigger with a loop like this:

function stopFormRespones() {
    var ss = SpreadsheetApp.getActive();
    for (var l = 2; l <= ss.getSheetByName('listStudent').getLastRow(); l++) {
        var link = ss.getSheetByName('listStudent').getRange('N' + l).getValue();
        var form = FormApp.openByUrl(link);
        form.setAcceptingResponses(false);
    }
}

function openFormRespones() {
    var ss = SpreadsheetApp.getActive();
    for (var l = 2; l <= ss.getSheetByName('listStudent').getLastRow(); l++) {
        var link = ss.getSheetByName('listStudent').getRange('N' + l).getValue();
        var form = FormApp.openByUrl(link);
        form.setAcceptingResponses(true);
    }
}

function scheduledCollection() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var start = ss.getSheetByName('title').getRange('F12').getValue();
    var end = ss.getSheetByName('title').getRange('F14').getValue();
    var t = new Date();
    if (t > start & t < end) {
        openFormRespones();
    } else {
        stopFormRespones();
    }
}

function StartStop() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var start = ss.getSheetByName('title').getRange('F12').getValue();
    var end = ss.getSheetByName('title').getRange('F14').getValue();
    var limit = ScriptApp.newTrigger('scheduledCollection')
        .forSpreadsheet(ss)
        .onChange()
        .create();
}

However, if the number of forms is large (more than 50 forms), the trigger seems to have an error. It does not execute on time as set. I think the cause is because the loop runs out of time allowed. How can I fire the forms at once (without using loops)? Looking forward to your help.

You are making way too many calls to Google Services. There's no need to loop over spreadsheet data by calling getSheetByName(), getRange(), and getValue() for every single row of data. You can get ALL items in your column in a single sweep and without using the 'for' loop by simply calling sheet.getRange("N2:N" + lastRow).getValues(). You can then loop over actual values without creating instances of a sheet and range objects every single time. — Anton Dementiev, Nov 09 '22 at 17:55
Use [batch operations](https://developers.google.com/apps-script/guides/support/best-practices#use_batch_operations) — Rubén, Nov 10 '22 at 08:40

Rubén · Accepted Answer · 2022-11-10T09:40:21.667

The main problem with your script is that it's reading one cell on each for loop iteration. This is a problem because

the time execution limit of Google Apps Script and
Google Apps Script methods are slow

The solution is to reduce the number of calls to Google Apps Script methods. This could be done by

reading all the cells that we need from the same sheet on a single operation
usign function parameters to avoid having to repeat operations in functions that are called by other functions.
Split the number of forms to be processed on each execution. One way to do this is by parametizing scheduledCollection by passing the start row and number of links to be processed, create a function to set the parameter values for each set. The below code only include 2, setOne and setTwo, please add as many as you need.

Bonus:

Remove variable declarations of variables that aren't used.

Note:

Avoid using on change trigger to call setOne, setTwo, etc. as this can of trigger executes on every spreadsheet change, instead use a custom menu to call of them one by one or use a user interface created with the HtmlService to call all of them asyncronously, this will allow them to run in parallel.

function stopFormRespones(links) {
    for (var l = 0; l < links.length; l++) {
        var link = links[l];
        var form = FormApp.openByUrl(link);
        form.setAcceptingResponses(false);
    }
}

function openFormRespones(links) {
    for (var l = 0; l < links.length; l++) {
        var link = links[l];
        var form = FormApp.openByUrl(link);
        form.setAcceptingResponses(true);
    }
}

function scheduledCollection(row,howMany) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    // read values in a single operation. Use deconstruction to assign values to variables
    var [start, _ ,end] = ss.getSheetByName('title').getRange('F12:F14').getValues().flat();

    var t = new Date();
    
    // Read the links once
    var sheet = ss.getSheetByName('listStudent');
    var lastRow = sheet.getLastRow();
    var links = sheet.getRange(row,14,howMany).getValues().flat();

    if (t > start & t < end) {

        // pass links as a function parameter
        openFormRespones(links);

    } else {

        // pass links as a function parameter
        stopFormRespones(links);

    }
}

function setOne(){
  var row = 1;
  var howMany = 10;
  scheduledCollection(row,howMany);
}

function setTwo(){
  var row = 11;
  var howMany = 20;
  scheduledCollection(row,howMany);
}

Long processing time likely due to getValue and cell inserts

If I call the functions `setOne()`, `setTwo()`,... from a user interface created with HtmlService only once, the `scheduledCollection(row,howMany)` function runs only once at that time. And by the time `star` or `end` this function will not run. — Đình Tấn Nguyễn, Nov 10 '22 at 18:20
@ĐìnhTấnNguyễn That depends on the the client-side code. — Rubén, Nov 10 '22 at 18:23

Activate multiple forms at the same time

1 Answers1