How to get a string and use it as part of the syntax in apps script?

Question

I am trying to build a Gmail database for all users in our company, I want to get these Gmails through piece of code and apply an if condition on them to see if they match or not, but I am not successful so far. I don't know if it's because the string I retrieve can't be read as an apps script syntax or it's because I am doing it wrong. here is my code:

I put all needed Gmails in 1 cell (B3) like this ---> (user == 'abc1@gmail.com') || (user == 'abc2@gmail.com')

//my code
    var user = Session.getEffectiveUser();
    var mailDB         = SpreadsheetApp.openById('Sheet ID').getSheetByName('Sheet Name'); //mails database
    var cellcondition  = HeadOfficeMailDB.getRange("A3"); // cell with certain value 22
    var Gmails     = mailDB.getRange("B3").getValue(); //retrieve Gmails in the cell as one whole string
    if((Gmails) && (cellcondition == 22))
    {
      var newcell  = HeadOfficeMailDB.getRange("C3").setValue(4);
    }

I set the Database with 2 different Gmails, but when I run the code with a third Gmail not included in Database, it runs anyway. it seems like it doesn't recognize the string as a syntax, or am I doing something wrong?

kindly if you have any fix or recommendations or other better ideas to handle such issue please don't hesitate to provide me with your assistance immediately.

I would just store the emails as comma separated strings and use split(',') and indexOf() to see if there is a match. Of course the other problem is that getEffectiveUser().getEmail() doesn't always return an email. — Cooper, Jun 30 '20 at 17:20

score 2 · Accepted Answer · answered Jun 30 '20 at 17:36

2

The following line

var cellcondition  = HeadOfficeMailDB.getRange("A3");

assigns a Class Range objec to cellcondition. Replace it by

var cellcondition  = HeadOfficeMailDB.getRange("A3").getValue();

to assign the value of A3 to cellcondition.

To evaluate the value of Sheet Name!B3 ((user == 'abc1@gmail.com') || (user == 'abc2@gmail.com')) you could use eval() but doing this is an enormous security risk. It's better to store the email address as a list (separated by using a separator like a comma) then use String.prototype.split() and Array.prototype.index() or create a Set object.

javascript pass eval variables

answered Jun 30 '20 at 17:36

Rubén

34,714
9
70
166

1

I am in your Debt, Rubén :) Thank you so much, eval() was pretty much what I was looking for!! and I totally understood immediately why it's such an enormous risk, but I guess if I protect the sheet and make it view only while making needed verification using its data, then nobody will have access to edit or modify current users Gmails stored in DB. If there are other risk please enlighten me. THANK YOU!! – Samy Jun 30 '20 at 17:51
@Samy Still better to avoid `eval` and use the alternative methods described above. You may accidentally change the sharing permissions, use a publicly provided formula, which evaluates to javascript, use =import*() and that import*() might get a js string. Granted the risk is low, but a targeted attack still is possible. Even if security risks are ignored, `eval` is a performance nightmare. – TheMaster Jul 01 '20 at 06:35

How to get a string and use it as part of the syntax in apps script?

1 Answers1

Linked