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?