Skip to content

Instantly share code, notes, and snippets.

@nmcspadden
Created April 18, 2013 22:13
Show Gist options
  • Save nmcspadden/5416662 to your computer and use it in GitHub Desktop.
Save nmcspadden/5416662 to your computer and use it in GitHub Desktop.
/*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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment