Script to validate if value exists in a list

Question

I have a google spreadsheet which will be used for collaboration and I was able to create a UI interface which will fetch and update data from a table to make it easier for users.

For the work I need to restrict users to only be able to update rows if they are assigned to them and I am able to do it. But along with that, I also need to allow administrator to assign or update rows which are not assigned to them.

I have a table with all employee's email address and an admin flag for those who are admin.

Name____________Email________________Is Admin
Employee 1_______emp1@domain.com_____Admin
Employee 2_______emp2@domain.com_____
Employee 3_______emp3@domain.com_____
Employee 4_______emp4@domain.com_____Admin
Employee 5_______emp5@domain.com_____
Employee 6_______emp6@domain.com_____

How can I write a something in my script that will allow me to if the user who is triggering a function has admin right or not.

I user Session.getActiveUser().getEmail() to pull out the users email address. I am creating the tool to be used within google apps domain.

Code that checks if the user is the owner of the row, this part works fine but what I want to do is if a user is admin they basically will skip this check.

if (s.getRange("E4").getValue() != usr()){
  msg("Once a Task is assigned to an employee, only assigned employee can make any changes to the Task. Thank you");
  return;
}
s = sheet
usr() = function calling to check active user email

If I can do something like countifs where I can check count based on the email address and Admin criteria and if its >= 1 proceed if its 0 show error that you can not make changes.

Please let me know.

Really appreciate the help.

Thank you.

Zooooon

You have a line of code that updates a row, and you probably have a line of code before that, that checks if the user is the owner of that row. Can you post those few lines of code? If you already have a conditional check, can you add a test for whether that user is an admin? — Alan Wells, May 26 '15 at 22:13
My row has a column which has user assigned to value. currently I am checking to see if its assigned only that user can make the change. But what I need to do is to allow admin to make changes for others. Even if its not assigned to them. — Taizooooon, May 26 '15 at 22:21
Felt the code that I have will be irrelevant so didn't post it. Here is the code that checks if this line belongs to the right owner and it works. ` if (s.getRange("E4").getValue() != usr()){ msg("Once a Task is assigned to an employee, only assigned employee can make any changes to the Task. Thank you"); rmsgbox.clearContent(); return; } ` It only moves forward if the owner is the same person as the one who is triggering the code. — Taizooooon, May 27 '15 at 22:07

Akshin Jalilov · Answer 1 · 2015-05-28T05:41:44.867

0

Are you looking for something like this?

function myFunction() {
  var s = SpreadsheetApp.getActiveSheet();
  var usr = Session.getActiveUser().getEmail();
  var emails = s.getRange("E2:F").getValues();

for (var i=0;i<emails.length;i++) {
    if (emails[i][0] === usr){
      if (emails[i][1] === "Admin") {
        //Do something when Admin
        return;
      } else {
        //Do something when not admin
        return;
        }
    } else {
      //Do something when email not in the list
    }
  }
}

I am assuming your data is in the range "D:F"

edited May 28 '15 at 05:41

answered May 28 '15 at 05:35

Akshin Jalilov

1,658
1
12
12

Thank you for the suggestion. I will try this solution as soon as I get time to work on the tool and give you feedback or questions if I run in to any issue. I do have a curious questions, in if condition you have used === and I generally use ==, is there any difference and which one should be advised. Sorry this might seem like dumb question but I am very naive and learning. – Taizooooon May 28 '15 at 15:56
[Here](http://stackoverflow.com/a/359509/2589810) is a detailed answer to your question – Akshin Jalilov May 28 '15 at 16:30
Thank you for sharing that kb. Also thank you for the suggested solution, it worked and with minor changes I was able to make it work well. – Taizooooon May 29 '15 at 16:31

Script to validate if value exists in a list

1 Answers1