Search code examples
javascriptstringgoogle-apps-scriptgoogle-sheetsevaluate

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

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.

Solution

  • 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.

    Related