finding a user entered variable in a Google Sheet and using that variable to return info from two other sheets

Question

My objective/flow is as follows:

1) User enters name (Lead) on "output" sheet.

2) Formula takes lead name and checks against "Cleaned data" sheet to find all instances of name(Lead) in column 5 and returns all strings in the corresponding rows in column 2

3) These strings are matched to another sheet "Raw Data" and finally, any matches found in "Raw Data" are returned to row 1 on "output"

function producthierarchy(){
  var leadSheet = SpreadsheetApp.openById("xxx").getSheetByName("[Output] Lead");
  var alloSheet = SpreadsheetApp.openById("xxx").getSheetByName("[Cleaned Data] Teams w. Allocations");
  var hierSheet = SpreadsheetApp.openById("xxx").getSheetByName("[Raw Data] Product Hierarchy");
  var leadRange = leadSheet.getRange("D1");
  var lead = leadRange.getValue();            //This should return the Lead's name entered in cell B1
  Logger.log(lead)


  var dataRange = alloSheet.getRange("A2:E")
  var data = dataRange.getValues()
  Logger.log(data);

  for (i in data) {
    var column = data[i]
    var userName = column[0];
    var productName = column[1];
    var alloAdjusted = column[2];
    var alloUnadjusted= column[3];
    var managers = column[5];

    if (managers.indexOf(managers)){
   Logger.log("works");
    }
  }
   }

Is it not possible to pass .indexOf a variable? That is my main blocker at the moment. I can find what the user has entered as "Lead" and My columns are right. However, I can't seem to find a way to find this entered data in my "Cleaned data" sheet

Andand Andand · Accepted Answer · 2018-10-24T22:36:27

This is why I don't post here - I found that my code had an error (check the small things kids) and was referencing the wrong column. However, for any other people struggling, here is the code I used to match the strings:

for (i in data) {
    var column = data[i]
    var userName = column[0];
    var productName = column[1];
    var alloAdjusted = column[2];
    var alloUnadjusted= column[3];
    var managers = column[4];

    var managersTest = new RegExp(Utilities.formatString('\|%s\|', lead))
    if (managers.match(managersTest)){
  //productName.getValue()
   Logger.log("works");
    }
  }
   }

finding a user entered variable in a Google Sheet and using that variable to return info from two other sheets

1 Answers