Google Scripts spreadsheet script: function within for loop does not fully execute before loop finishes

Question

I'm writing a script to use with google spreadsheet. I want the script to cycle through a spreadsheet of URLs. As it cycles through, the function should call an UI instance which displays the url link and receive user input about the url being 'current' or 'expired' and marks it the spreadsheet.

Ideally after the user picks an option an clicks the 'next' button, the dialog box for that url will close and the dialog box for the next url will open.

The code is below.

   function getUrlsToCheck(){

   /**
   * Asks user to input range of urls to check.
   */

   var spreadsheet = SpreadsheetApp.getActive();
   var sheet = spreadsheet.getSheets()[0];

  // Prompt the user for the range with the starting row and ending row.
   var rowStart = Browser.inputBox('Starting Entry',
      'Please enter the starting row of the urls you want to check' +
      ' (for example, "1"):',
      Browser.Buttons.OK_CANCEL);
    if (rowStart == 'cancel') {
    return;
  }

    var rowNumber1 = Number(rowStart);
       if (isNaN(rowNumber1) || rowNumber1 < 2 ||
        rowNumber1 > spreadsheet.getLastRow()) {
        Browser.msgBox('Error',
        Utilities.formatString('Row "%s" is not a valid starting point.', rowStart),
        Browser.Buttons.OK);
    return; 
  }

   var rowEnd = Browser.inputBox('Ending Entry',
      'Please enter the ending row of the urls you want to check' +
       ' (for example, "56"):',
       Browser.Buttons.OK_CANCEL);
    if (rowEnd == 'cancel') {
    return;
  }

    var rowNumber2 = Number(rowEnd);
    if (isNaN(rowNumber2) || rowNumber2 < rowNumber1 || rowNumber1 == rowNumber2) {
        Browser.msgBox('Error',
        Utilities.formatString('Row "%s" is not a valid ending point.', rowEnd),
        Browser.Buttons.OK);
    return; 
}

    var numOfRowsToCheck = (rowNumber2 - rowNumber1);
    var range = sheet.getRange(rowNumber1, 1, (numOfRowsToCheck + 1), 1);
    var data = range.getValues();

  /*
   *Loop to retrieve URLs and show a link in the Ui instance
   */

    for(var i = 0; i < data.length; i++) {

    var activeRow = (Number(i) + rowNumber1);

    var range4 = sheet.getRange(activeRow, 4);
    var range5 = sheet.getRange(activeRow, 5);

    var app = UiApp.createApplication().setHeight(150).setWidth(250);
    app.setTitle("Check URL");

    var link1 = app.createAnchor('Check', data[i]);
    var panel = app.createVerticalPanel();

    var infoLabel = app.createLabel(Utilities.formatString('Check the link for entry %s', activeRow)).setId('infoLabel');
    var infoLabel1 = app.createLabel('').setId('infoLabel1');

    var current = app.createRadioButton("group", "Current").setName('current').setId('current');
    var expired = app.createRadioButton("group", "Expired").setName('expired').setId('expired');

    var hidden = app.createHidden("yourObject", Utilities.jsonStringify(activeRow));

    var handler = app.createServerChangeHandler('current');
    handler.addCallbackElement(panel).addCallbackElement(hidden);
    current.addClickHandler(handler);

    var handler2 = app.createServerChangeHandler('expired');
    handler2.addCallbackElement(panel).addCallbackElement(hidden); 
    expired.addClickHandler(handler2);

    var next = app.createButton('Next').setId('next');
    var handler3 = app.createServerHandler('next');
    handler3.addCallbackElement(panel);
    next.addClickHandler(handler3);

    panel.setSpacing(5);

    panel.add(infoLabel);
    panel.add(link1);
    panel.add(current);      
    panel.add(expired);
    panel.add(infoLabel1);
    panel.add(next);

    app.add(panel);

    spreadsheet.show(app);  
   }     
  }

     function next(e){
     var app = UiApp.getActiveApplication(); 
     app.close();

   return app;
  }

function current(e){
  var app = UiApp.getActiveApplication(); 
  app.getElementById('expired').setValue(false);

  var yourObj = Utilities.jsonParse(e.parameter.yourObject);

  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheets()[0];
  var range4 = sheet.getRange(yourObj, 4);
  var range5 = sheet.getRange(yourObj, 5);

  range4.setValue('x');
  range5.setValue('');

  app.getElementById('infoLabel1').setText('This link has been marked current');

  return app;
 }

function expired(e){
  var app = UiApp.getActiveApplication(); 
  app.getElementById('current').setValue(false);

  var yourObj = Utilities.jsonParse(e.parameter.yourObject);

  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheets()[0];
  var range4 = sheet.getRange(yourObj, 4);
  var range5 = sheet.getRange(yourObj, 5);

  range4.setValue('');
  range5.setValue('x');

 app.getElementById('infoLabel1').setText('This link has been marked expired'); 

  return app;
 }

The problem I'm having is as the loop cycles through, there isn't enough time for the dialog box that accepts the user information to load; so you only see the dialog box of last url.

There are a couple things I've tried and I must be doing it wrong since they haven't worked.

• I've tried inserting a system pause.

• I've also tried making the function a generator function, but the
  yield command didn't compile right.

• I've also tried using a do while loop instead of a for loop. The
  While condition being tied to the whether or not the next button is
  disabled.

Any suggestions on how to get every step of the function to execute before the loop finishes?

Answer 1

The problem with this code is that, it creates all the dialog boxes all at the same time but you want to create the dialog boxes only after you have done with the first one. It might be better to rewrite a lot of things, but for this demo, I'll just convert your code to be able to run synchronously.

So instead of in a for loop in getUrlsToCheck, you have to probably create the dialog box in the callback handler, next. This does mean we have to essentially make a recursive call of sorts. We also have to keep track of i and pass it along. To make the edits to you code minimal, I moved out most of the code in getUrlsToCheck to a function that initialises the required data from arguments i,rowNumber1 and rowNumber2 and open up the dialog box.

I also added some code in verify that passes the data i+1,rowNumber1and rowNumber to the next handler. And in next, we call verify with the parsed JSON of the passed handler.

Finally to start off the loop, we call verify in getUrlsToCheck by passing rowNumber1,rowNumber2 and i=0.

So the code would look something like

function getUrlsToCheck() {
    /**
     * Asks user to input range of urls to check.
     */

    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheets()[0];

    // Prompt the user for the range with the starting row and ending row.
    var rowStart = Browser.inputBox('Starting Entry',
        'Please enter the starting row of the urls you want to check' +
        ' (for example, "1"):',
        Browser.Buttons.OK_CANCEL);
    if (rowStart == 'cancel') {
        return;
    }

    var rowNumber1 = Number(rowStart);
    if (isNaN(rowNumber1) || rowNumber1 < 2 ||
        rowNumber1 > spreadsheet.getLastRow()) {
        Browser.msgBox('Error',
            Utilities.formatString('Row "%s" is not a valid starting point.', rowStart),
            Browser.Buttons.OK);
        return;
    }

    var rowEnd = Browser.inputBox('Ending Entry',
        'Please enter the ending row of the urls you want to check' +
        ' (for example, "56"):',
        Browser.Buttons.OK_CANCEL);
    if (rowEnd == 'cancel') {
        return;
    }

    var rowNumber2 = Number(rowEnd);
    if (isNaN(rowNumber2) || rowNumber2 < rowNumber1 || rowNumber1 == rowNumber2) {
        Browser.msgBox('Error',
            Utilities.formatString('Row "%s" is not a valid ending point.', rowEnd),
            Browser.Buttons.OK);
        return;
    }

    var numOfRowsToCheck = (rowNumber2 - rowNumber1);


    /*
     *Moved everything to function verify();
     */
    verify({
        i: 0,
        rowNumber1: rowNumber1,
        rowNumber2: rowNumber2
    })
}

function verify(myObj) {
    //gets variable from passed json obj
    var i = myObj.i 
    var rowNumber1 = myObj.rowNumber1
    var rowNumber2 = myObj.rowNumber2

    //mostly the same
    var activeRow = (Number(i) + rowNumber1);

    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheets()[0];
    var numOfRowsToCheck = (rowNumber2 - rowNumber1);
    var range = sheet.getRange(rowNumber1, 1, (numOfRowsToCheck + 1), 1);
    var data = range.getValues();
    var range4 = sheet.getRange(activeRow, 4);
    var range5 = sheet.getRange(activeRow, 5);

    if (i < data.length) {

        var app = UiApp.createApplication().setHeight(150).setWidth(250);
        app.setTitle("Check URL");

        var link1 = app.createAnchor('Check', data[i]);
        var panel = app.createVerticalPanel();

        var infoLabel = app.createLabel(Utilities.formatString('Check the link for entry %s', activeRow)).setId('infoLabel');
        var infoLabel1 = app.createLabel('').setId('infoLabel1');

        var current = app.createRadioButton("group", "Current").setName('current').setId('current');
        var expired = app.createRadioButton("group", "Expired").setName('expired').setId('expired');

        var hidden = app.createHidden("yourObject", Utilities.jsonStringify(activeRow));

        var handler = app.createServerChangeHandler('current');
        handler.addCallbackElement(panel).addCallbackElement(hidden);
        current.addClickHandler(handler);

        var handler2 = app.createServerChangeHandler('expired');
        handler2.addCallbackElement(panel).addCallbackElement(hidden);
        expired.addClickHandler(handler2);

        //added this to pass state to next()
        var hidden = app.createHidden("yourObject", Utilities.jsonStringify({
            i: i + 1, //increment i
            rowNumber1: rowNumber1,
            rowNumber2: rowNumber2
        }));

        var next = app.createButton('Next').setId('next');
        var handler3 = app.createServerHandler('next');
        handler3.addCallbackElement(panel);
        handler3.addCallbackElement(panel).addCallbackElement(hidden);
        next.addClickHandler(handler3);

        panel.setSpacing(5);

        panel.add(infoLabel);
        panel.add(link1);
        panel.add(current);
        panel.add(expired);
        panel.add(infoLabel1);
        panel.add(next);

        app.add(panel);

        spreadsheet.show(app);
    }
}


function next(e) {
    var app = UiApp.getActiveApplication();
    app.close();

    //parse string to json and call verify to open next dialog
    verify(Utilities.jsonParse(e.parameter.yourObject));

    return app;
}

function current(e) {
    var app = UiApp.getActiveApplication();
    app.getElementById('expired').setValue(false);

    var yourObj = Utilities.jsonParse(e.parameter.yourObject);

    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheets()[0];
    var range4 = sheet.getRange(yourObj, 4);
    var range5 = sheet.getRange(yourObj, 5);

    range4.setValue('x');
    range5.setValue('');

    app.getElementById('infoLabel1').setText('This link has been marked current');

    return app;
}

function expired(e) {
    var app = UiApp.getActiveApplication();
    app.getElementById('current').setValue(false);

    var yourObj = Utilities.jsonParse(e.parameter.yourObject);

    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheets()[0];
    var range4 = sheet.getRange(yourObj, 4);
    var range5 = sheet.getRange(yourObj, 5);

    range4.setValue('');
    range5.setValue('x');

    app.getElementById('infoLabel1').setText('This link has been marked expired');

    return app;
}

We could probably make this better, but this works in testing.