循环数组以匹配组成员身份 [英] looping array to match group membership

问题描述

我正在尝试设置一个脚本，该脚本在表单提交时获取用户的上下文，获取用户ID(电子邮件)，搜索电子表格以找到用户，然后查找经理的电子邮件，用户关联.下面是我一直在开发的一些代码，可以从电子表格中获取数据并创建一个数组并对其进行循环.我想知道是否有人可以帮助您提供有关构建电子表格以搜索和定位成员资格的最佳方法的建议，然后如何在脚本中做到这一点.

I'm in the process of trying to setup a script that get the context of a user on form submit, takes the user ID (email), searches a spreadsheet to locate the user, and then find the manager's email that the user is associated with. Below is some code i've been developing and I'm able to grab the data from the spreadsheet and create an array and loop over it. What I'm wondering if anyone could help provide suggestions on the best way to structure the spreadsheet to search and locate membership and then how to do that in the script.

function getUser() {
 //Uses the base class to get acive user's email
   var email = Session.getActiveUser().getEmail();
 //Uses the UserManager class to get info by passing in the getActive user from base class
   var userFirst = UserManager.getUser(Session.getActiveUser()).getGivenName();
   var userLast = UserManager.getUser(Session.getActiveUser()).getFamilyName();
 //Go to View then Log to see results
 return email;
 }

//Function used to look up manager of user submitting PTO request
function getManager() {
  var requestor = getUser();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var manager = ss.getSheetByName('Managers');
  var values = manager.getDataRange().getValues();

  for ( i = 0; i < values.length; i++) {
    var obj = values[i];
    Logger.log(obj);
    for (var j in obj) {
    var value = obj[j];
     //Logger.log(value);
      if (value == requestor){
       Logger.log(value);
      }
    }

我开始考虑在一张纸上有一个用户列表，在另一张纸上有一个经理列表，但是没有被出售，因为我不确定如何将用户绑定到经理上.然后想想可能是三列，每一列都以一个经理开头，而经理名称用户名下方的每一行呢?

I started to thinking have a user list on one sheet and managers on another but wasn't sold on that, as I wasn't sure how to tie the user to a manager. Then thought maybe three columns, each column starts with a manager and each row below the manager's name users names?

我希望脚本避免硬编码值.任何激发创意的想法或建议都非常欢迎.

I'd like the script to avoid hard coding values. Any thought or suggestion to spark ideas is so welcome.

谢谢

已更新 好的，所以我已经编写了代码，现在正在运行三个函数.我还决定使用包含两张纸的电子表格，一张用于经理，另一张用于员工.每个表都是相似的.三列(名称，电子邮件，groupID)员工的组ID将与该员工所属的经理的groupID相匹配.这是更新的代码:getUser()将获取活动用户，findEmployee()使用getUser获取电子邮件，然后将其匹配并返回groupID. getMnger()使用groupID查找经理电子邮件.

UPDATED Ok, so I've worked on the code and now have three functions running. I've also decided to go with using a spreadsheet with two sheets, one for managers and one for employees. The sheets for each are similar. Three columns (Name, email, groupID) The group ID for the employee will match the groupID of a manager to whom that employee belongs. Here is the updated code: getUser() will get the active user, findEmployee() uses getUser to get email then matches it and returns groupID. getMnger() uses the groupID to find managers email.

我不确定这是最好的方法，但我也在学习自学. (只能在这里和那里玩

I'm not sure this is the best way but I'm also learning teaching myself. (only get to play around here and there

function getUser() {
 //Uses the base class to get acive user's email
   var email = Session.getActiveUser().getEmail();
 //Uses the UserManager class to get info by passing in the getActive user from base class
   var userFirst = UserManager.getUser(Session.getActiveUser()).getGivenName();
   var userLast = UserManager.getUser(Session.getActiveUser()).getFamilyName();
 //Go to View then Log to see results
   return email;
 }

function findEmployee() {
  //Gets the active Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //Gets the sheet by name
  var employee = ss.getSheetByName('Roster');
  //get the values in sheet
  var values = employee.getDataRange().getValues();
  // makes a call to function getUser() to return email of active user
  var user = getUser();
  // loops over data values, matches to active user email, returns group ID
  for (var i=0; i < values.length; i++) {
    for (var j=0; j < values[i].length; j++) {     
      if ( values[i][j] == user) {
       return values[i][2];
       //used to verify / debug
       Logger.log(values[i][2]);
    }
   }
  }
 }

function getMnger(){
  //Gets current spreadsheet and locates the managers sheet and grabs the data
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var manager = ss.getSheetByName('Managers');
  var values = manager.getDataRange().getValues();
  var groupID = findEmployee();

  //Loops over the data creating associative array
  for (var i=0; i < values.length; i++) {
    for (var j=0; j < values[i].length; j++) {
      if ( values[i][j] == groupID) {
        //Used to debug and verify
          Logger.log(values[i][1]);
        return values[i][1];
    }      
   }     
  } 
 }

这是一项正在进行的工作.到目前为止，还有人在想什么吗?我现在最大的问题是如何根据另一个值返回我想要的值.在findEmployee()和getMnger()中，一旦匹配，它将返回values [] []中的硬编码索引.即返回值[i] [1]；或返回值[i] [2];

This is a work in progress. Anyone have thoughts so far on ? My biggest question now is how to return the value I want based on another value. In both findEmployee() and getMnger(), once it matches it returns a hard coded index in values[][]. i.e. return values[i][1]; OR return values[i][2];

有没有办法确保这里不需要硬编码值?基本上，我希望吸引用户并在工作表的一列中找到他，然后在他的电子邮件旁边的另一列中返回相应的groupID.有道理吗?

Is there a way to ensure that hardcode values are not needed here? Basically i'm looking to take the users and find him in a column on a sheet and then return the corresponding groupID in another column beside his email. Make sense?

谢谢！

推荐答案

您可以在电子表格中使用filter.而且，由于要避免对值进行硬编码，因此可以创建两个工作表，一个用于经理，一个用于用户，为每个经理分配一个唯一的密钥，然后为用户列表中的每个用户包括唯一的经理密钥.与其经理相对应.

You can use filter within the spreadsheet. And since you'd like to avoid hard coding values, you can create two sheets, one for managers and one for users, assign each manager with a unique key, and then for each user in the user list sheet, include the unique manager key corresponding to their managers.

这篇关于循环数组以匹配组成员身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！