Pages

Tuesday, 27 October 2020

Search spreadsheet for multiple criteria

The following Google Apps Script has been extracted from a tool I am developing to manage a Google Group via a Google Sheet. The purpose of this particular script is to enable searching through rows of spreadsheet data and simultaneously looking for matches against 2 pieces of criteria:

  1. That the user running the script (their email address) is listed in the sheet (Email address column) and has the role (Role column) of either 'manager' or 'owner'.
  2. That there is at least 1 'owner' listed somewhere in the Role column.

Now what we want to avoid is looping through all of the data twice just to look for our 2 pieces of criteria. We also want to avoid unnecessarily scanning all of the rows once our criteria is met. So this script shows a way of doing that by using true/false flags to determine how the single loop should behave.

Rows of email address and role in spreadsheet
Screenshot of spreadsheet data to search

1) searchSheet.gs

This Function will gather all of the data from the spreadsheet (in a 2-D array) and loop through it row-by-row looking for matches against our specified criteria.

The Function begins by getting the usual spreadsheet data and removing the Header row (row 1) as we do not need this for the loop:

var ss = SpreadsheetApp.getActiveSheet();
var usersDetails = ss.getDataRange().getValues();
var shifty = usersDetails.shift();

We can get the email address of the person running the script via the Session:

var theUserEmail = Session.getActiveUser().getEmail();

As we enter the loop one of the checks we need before running the below 'checkUserRole' Function is the status of its Flag. We only want the Function to run if it has not found a match, because if it does eventually find a match and returns 'true' then subsequent runs of the same Function could switch it back to 'false' (if say the user is in the middle of the list and it continues trying to scan through the rest):

if (userRoleFlag == false) {
    var userRoleFlag = checkUserRole(theUserEmail, emailAddress, role);
}

After this we move on to checking the 'owner' Flag which is only concerned with looking for the text 'owner' in the Role column. As above though we only want this to run if it has not made a positive match - as there may be an 'owner' in row 2 which sets the Flag to 'true' but then the next row may be the role 'member' which sets it back to 'false':

if (ownerFlag == false) {
    if (role == roleType) {
        var ownerFlag = true;
}

else {
    var ownerFlag = false;

}

Finally this Function, after each row, needs to evaluate the status of the 2 pieces of criteria to determine if it can break out of the loop - as we do not want to search through 150+ rows if we find what we are looking for within the first 5 (for example):

if (userRoleFlag && ownerFlag) {
    break;      
}
else {
    // do nothing as loop needs to continue running
}

2. checkUserRole.gs

This Function will look at the current row and see if the person running the script matches the email address in the sheet and that they have the role of either 'manager' or 'owner'. It is called as part of the above loop so it can happen sequentially rather than as an entirely separate loop:

// check for email address in sheet then if matching role is found
if ((theUserEmail == emailAddress) && (role == 'manager' || role == 'owner')) {
    // yes there is a match set success flag
    var userRoleFlag = true;
}
else {
    // no match found set success flag
    var userRoleFlag = false;
}

Search spreadsheet for multiple criteria download (please use 'File' > 'Make a copy' for your own version).

No comments:

Post a comment