StrategicPlanReport

/*global SpreadsheetApp: false, UiApp: false */

// Close the current UI
function exit() {
    "use strict";
    var ui = UiApp.getActiveApplication();
    return ui.close();
}

// Callback action for button labelled "Generate Report"
// Take the values from two text boxes in the active GUI and

// =QUERY('Sheet1'!A:D;"select A where (D contains 'Complete') and (C contains '2013')")
function generateReport(e) {
    "use strict";
    var ui = UiApp.getActiveApplication(),
        sheet = SpreadsheetApp.getActiveSheet(),
        cell = sheet.getRange("H1"),
        year = e.parameter.txtYear_Name,
        status = e.parameter.txtStatus_Name,
        txtYear = ui.getElementById('txtYear_Id'),
        txtStatus = ui.getElementById('txtStatus_Id'),
        query1 = "=QUERY\(\'",
        query2 = "\'!A\:D\;\"select A where \(D contains \'",
        query3 = "\'\) and \(C contains \'",
        query4 = "\'\)\"\)";
    cell.setValue(query1 + SpreadsheetApp.getActiveSheet().getName() + query2 + status + query3 + year + query4)
    return ui;
}

// Build a GUI with two labels, two text boxes and two buttons.
function StrategicPlanReport() {
    "use strict";
    var ui = UiApp.createApplication(),
        ss = SpreadsheetApp.getActiveSpreadsheet(),
        uiTitle = 'Generate Report From Year',
        panelInput = ui.createVerticalPanel(),
        panelYear = ui.createHorizontalPanel(),
        panelStatus = ui.createHorizontalPanel(),
        panelButtons = ui.createHorizontalPanel(),
        lblYear = ui.createLabel('Year:'),
        lblStatus = ui.createLabel('Status:'),
        txtYear = ui.createTextBox(),
        txtStatus = ui.createTextBox(),
        btnGenerate = ui.createButton('Generate'),
        btnExit = ui.createButton('Exit'),
        exitHandler = ui.createServerHandler('exit'),
        addRowHandler = ui.createServerHandler('generateReport');
    panelYear.add(lblYear);
    panelYear.add(txtYear);
    panelStatus.add(lblStatus);
    panelStatus.add(txtStatus);
    panelInput.add(panelYear);
    panelInput.add(panelStatus);
    panelButtons.add(btnGenerate);
    panelButtons.add(btnExit);
    panelInput.add(panelButtons);
    ui.add(panelInput);
    ui.setWidth(200);
    ui.setHeight(100);
    btnExit.setWidth(80);
    btnExit.addClickHandler(exitHandler);
    btnGenerate.setWidth(80);
    btnGenerate.addClickHandler(addRowHandler);
    addRowHandler.addCallbackElement(txtYear);
    addRowHandler.addCallbackElement(txtStatus);
    txtYear.setName('txtYear_Name');
    txtStatus.setName('txtStatus_Name');
    txtYear.setId('txtYear_Id');
    txtStatus.setId('txtStatus_Id');
    ui.setTitle(uiTitle);
    txtYear.setFocus(true);
    ss.show(ui);
}