Search code examples
google-sheetsgoogle-apps-scriptgoogle-sheets-formula

Find a match of 2 or 3 names in one cell, enter those matches into corresponding cell on another sheet?

Here is my sheet.

Master sheet column Q contains comments with driver names, frequently more than one name will be included. Column S is where the driver names should go. My current formula finds only one name, even if more than one is present. I need it to find all names present and enter like this: Kent, Kyle, Pitts.

DriversBusesEmojis sheet column I has the list of coach driver last names.

I have no idea how to do this. Ultimately I want those coach driver names in the Working sheet column S. If I use the formula to get them directly in that sheet/column, I cannot assign other drivers manually or make changes to coach drivers. My thinking is if a formula used on a different sheet to get the data I need in the column I need, then when the data is imported (a script already does the importing) it will bring that name/names with it and still be editable on the Working sheet.

Would a script be a better option? Can the current import script be modified to include finding the name/names and entering them into the appropriate cell?
I also have very little know how regarding google app scripts. I have gotten a lot of help and figured a little out myself. So I will need detailed explanations how to do this. The import script is complicated to my beginners brain, so I haven't been able to figure out how to incorporate this functionality so far.

Note: Whenever possible, I have incorporated the formula into the header title. For example on the Master sheet column S, the header cell has a formula that displays the header text and the formula. This way I don't lose the formula if I change sorting/filtering etc.

I also know messing with the form response sheet is not a good idea, but I'm hoping this one thing won't cause a problem anywhere. If there is a better suggestion, please tell me!

I've tried various formula solutions. So far I can only get one name to be found.

The formula is:

=IFNA(BYROW(Master!$Q$2:$Q,LAMBDA(g,IF(g="",,INDEX(FILTER($I$2:$J,$I$2:$I<>"",REGEXMATCH(g,"(?i)"&$I$2:$I)),1,)))))
The result is pulled into the Master sheet column S with this formula:
={"Coach Driver";IMPORTRANGE("https://docs.google.com/spreadsheets/d/1jO8auzYZ6drlGi3m7lon6gHTBVboDNgH5e0x4OwQoAA/edit?gid=818123556#gid=818123556", "DriversBusesEmojis!L2:L")}
Solution

  • You have a Google Form where users can request buses. The Form includes a "Comment" field where, among other things, the names of drivers can be recorded. The Form is linked to a spreadsheet.

    On the same spreadsheet, there is a list of driver names.

    For each request/submission, you want to use list of driver names to create a new column for each submission that contains a comma-separated list of the drivers whose names appear in the "Comments".

    Consider this script which should be triggered by an installable onFormSubmit trigger.

        function getFormData(e) {
    
      // Logger.log(JSON.stringify(e)) // DEBUG
      
      // get  Event Objects 
      var values = e.values
      var editedRow = e.range.rowStart
      
      // number of questions in the form
      var itemCount = values.length
      // Logger.log("DEBUG: number of questions in response = "+itemCount)
      
      // copy the response to "Working" sheet
      var ss = SpreadsheetApp.getActiveSpreadsheet()
      var workingSheet = ss.getSheetByName("Working03")
      var targetRange = workingSheet.getRange(editedRow,1,1,itemCount)
      // Logger.log("DEBUG: the target range = "+targetRange.getA1Notation())
      targetRange.setValues([values])
    
      // get the Comments value as a String
      var driverComments = e.namedValues['Comments'].toString()
      // Logger.log("DEBUG: Driver comments = "+driverComments)
    
      // get the driver names from Column O = 15
      var driverSheet = ss.getSheetByName("DriversBusesEmojis")
      var driverCount = driverSheet.getRange("O2:O").getValues().filter(String).length
      // Logger.log("DEBUG: the number of drivers = "+driverCount)
      var driverList = driverSheet.getRange(2,15,driverCount,1).getValues().flat()
      // Logger.log(driverList) // DEBUG
    
      // create temp array to hold driver names from Comments
      var tempArray = new Array
    
      // loop through the driver list and test for inclusion in the comment
      for (var i=0;i<driverCount;i++){
        var result = driverComments.indexOf(driverList[i])
        // Logger.log("DEBUG: i:"+i+", drivername = "+driverList[i]+", result = "+result)
        if (result >1){
          tempArray.push(driverList[i])
        }
      }
      // Logger.log(tempArray) // DEBUG
    
      // join array values as a string
      var drivers = tempArray.join()
      // Logger.log(drivers) // DEBUG
    
      // set the output range on "Working"
      var targetRange = workingSheet.getRange(editedRow,(+itemCount+1))
      // Logger.log("DEBUG: the target range = "+targetRange.getA1Notation())
    
      // update the Drivers column on "Working"
      targetRange.setValue(drivers)

}

    Comments

    • Event Objects are used where possible.
    • Each submission is copied to a "Working" sheet so that the original form response is kept intact.
    • A loop is used to work through the Driver names and indexOf (the String version) is used to test for the driver name in the comment.
    • a temporary Array is used to accumulate any/all driver names found in the comment.
    • a join is used to convert the content of the temporary array into a comma separated string.

    Driver Output

    output

    DriversBusesEmojis

    List