Power Automate Flow to get specific cells based on user input

Question

I am currently working on a Microsoft Power Virtual Agent (PVA) chatbot that helps users understand complicated documents. Right now I am looking at making a flow that takes a user's zipcode as input and then returns a departments contact information from a Sheet. I am firstly not sure what the best solution is, I am using 'get rows' on a Google Sheet, but I am not sure if I should be using a Sharepoint list instead.

My real issues are the following: How do I get the right row out based on the zipcode a user provides? So if I give it 2000 as a Zipcode in PVA and turn that into an input for the flow, how does it run through the rows in a sheet and find the right one dynamically, then how do I select that row and turn the other cells that are part of it into variables? I tried to use control loops but could not get that to work right.

I've added a pic here of what I currently have

Answer 1

Try this and see how you go.

I created an Excel document that stores your zip codes. You need to store it in a SharePoint online folder, mine looks like this ...

You'll notice that in Excel Online (hopefully you have this, if not, it's a tenancy thing that needs to be activated) you'll have a tab in the ribbon called Automate ...

In there, create a new script, call it Zip Code Lookup and then paste in this code ...

function main(workbook: ExcelScript.Workbook, zipCodeToFilter: string)
{
  let activeSheet = workbook.getActiveWorksheet();
  let zipCodeRange = activeSheet.getUsedRange();
  let zipCodeValues = zipCodeRange.getValues();

  let zipCodeFilter = zipCodeValues.filter(row => {
    return row[0].toString() == zipCodeToFilter;
  });

  let zipCodeResult: ZipCode = null;

  if (zipCodeFilter.length == 1) {
    zipCodeResult = {
      zipCode: zipCodeFilter[0][0].toString(),
      phoneNumber: zipCodeFilter[0][1].toString(),
      departmenName: zipCodeFilter[0][2].toString(),
      website: zipCodeFilter[0][3].toString()
    }
  }

  return zipCodeResult;
}

interface ZipCode {
  zipCode: string,
  phoneNumber: string,
  departmenName: string,
  website: string
}

Now in PowerAutomate, you should be able to include an action called Run script which is a part of the Excel Online (Business) group of actions.

Here is an example of what I have configured. Specifically, I'm going to look up zip code 53478 ...

... then when the script runs, you get this output and all of the variables are split out for future use.

So I can set the department name into a new variable if I need to (but you don't because it's already a dynamic property) ...