StrategicReporter

/*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);
//  cell.setFormula("=QUERY(\'" + SpreadsheetApp.getActiveSheet().getName() + "\'!A\:D\;\"select A where \(D contains \'" + status + "\'\) and \(C contains \'" + year + "\'\)\"\)");
  
  // Here's the idea for trolling through the rows and hiding all the ones that aren't complete.
  var col = 3,
      range = sheet.getDataRange(),
      values = range.getValues();
  
  for (i=1;i<range.getHeight();i++)
  {
    if (values[i][col-1].toString()==year)// && values[i][col+1].toString()==status)
    {
      Logger.log(i);
      Logger.log(values[i][col-1].toString());
      Logger.log("Yes");
      SpreadsheetApp.getActiveSheet().hideRows(i);
    }
//    Logger.log(values[i][col-1].toString()=="2013")
  }
  return ui;
}

function clearReport() {
  SpreadsheetApp.getActiveSheet().getRange("H1").clear();
  SpreadsheetApp.getActiveSheet().unhideRow(SpreadsheetApp.getActiveSheet().getDataRange())
}

// 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'),
        btnClear = ui.createButton('Clear'),
        clearHandler = ui.createServerHandler('clearReport');
    panelYear.add(lblYear);
    panelYear.add(txtYear);
    panelStatus.add(lblStatus);
    panelStatus.add(txtStatus);
    panelInput.add(panelYear);
    panelInput.add(panelStatus);
    panelButtons.add(btnGenerate);
    panelButtons.add(btnExit);
    panelButtons.add(btnClear);
    panelInput.add(panelButtons);
    ui.add(panelInput);
    ui.setWidth(300);
    ui.setHeight(100);
    btnExit.setWidth(80);
    btnExit.addClickHandler(exitHandler);
    btnGenerate.setWidth(80);
    btnGenerate.addClickHandler(addRowHandler);
    addRowHandler.addCallbackElement(txtYear);
    addRowHandler.addCallbackElement(txtStatus);
    btnClear.setWidth(80);
    btnClear.addClickHandler(clearHandler);
    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);
}