Filtering Spreadsheet by logged account Google Apps Script

Question

I want to create a script to shows in a Ui SplitLayoutPanel the data from a spreadsheet base on the user logged in the Google. I wrote something like this until now:

function doGet() {
  var app = UiApp.createApplication();
  var sPanel = app.createSplitLayoutPanel();
  sPanel.addWest(app.createLabel('Hierarchy'), 100);
  var handler=app.createClientHandler("MyHierarchy");
  handler
  sPanel.addNorth(app.createLabel('List of my Hierarchy'), 200);
  sPanel.add(app.createLabel('Timesheet'));
  sPanel.setHeight('100%').setWidth('100%');
  app.add(sPanel);
  return app;
}

function MyHierarchy() {
  var app = UiApp.createApplication();
  var ss=SpreadsheetApp.openById("id_of_the_Spreadsheet")
  var user=Session.getActiveUser().getEmail();
  return app;
}

In the spreadsheet i will have a list of employees and theirs respective managers (with their Google Apps email accounts). I want to filter the data based on the email of this managers. Any idea on how can i continue?

To be clear, you want to take a manager's email, and display all of his employees (everyone that works under him)? — Phil Bozak
Exactly! This is what i need. But this needs to be related do the user who is using the app at the moment. — Bruno Estrazulas

Phil Bozak Phil Bozak · Accepted Answer · 2013-02-26T16:34:22

Well, this would just be a simple lookup, and you'll want to write this in a separate function. I'll throw some mock-up code at you to get you started.

function getEmployeesUnder(manager) {
  var employees = [];
  var sheet = SpreadsheetApp.openById("YOUR SHEET ID").getSheetByName("Your sheet name");
  var values = sheet.getDataRange().getValues();
  for (var i in values) {
    if (values[i][1] == manager) {
      employees.push(values[i][0]);
    }
  }
  return employees;
}

This assumes that the managers are in the second column, and the employees are in the first column.

It would be entirely up to you to decide how to display this data.

Filtering Spreadsheet by logged account Google Apps Script

1 Answers