Solved: Interpreting a scraped table | Variable translatio...

Dsimmons · ‎04-20-2023

Ok. Here we are with this one. I have the entire flow working as it should, all the way until the last part. Where I take a table scraped off a website, thats imported into a new workbook sheet. We will call this Sheet WebData. I have PAD analyze column 3, "Responses" which contains a number. 001, 002, 004, 006, 009, 999. 001 and 002 are "Good", 004 needs to return a value of "LocCoord", 006 as "HiFacility", 009 as "Unkn", and 999 as Late. There could possible be up to 20 of these per table.

I've tried using If statements, nested IF's, Else-If, Switch functions....not wanting to play ball. Now I'm coding it out in Java. Have 2 scripts written that should work, but I cannot get the variables to pass Into or Out of the script.

I currently have the flow set up to both store that column as a list in say %Resp_List%, and as individual variables, say %ExcelData1% thru %ExcelData20%.

Stuck like chuck on this one. And I'm feeling as though it might be a limitation of the Conditionals in PAD even when combined with Logic statements as well.

Any help would be lovely!

JS:

" // Read Resp_List from Power Automate input
var Resp_List = %Resp_List%;

// Initialize ListOutput as "Empty" by default
let ListOutput: string = "";
{

// Check if Resp_List contains a 4
if (Resp_List.includes(4)) {
  ListOutput = "LocCoord";
}
// Check if Resp_List contains a 6
else if (Resp_List.includes(6)) {
  ListOutput = "HiFacility";
}
// Check if Resp_List contains 999
else if (Resp_List.includes(999)) {
  ListOutput = "LATE";
}
// Check if all values in Resp_List are either 1 or 2
else if (Resp_List.every(val => val === 1 || val === 2)) {
  ListOutput = "Good";
}

// Output ListOutput as the final result
//ListOutput = %ListOutput%;
var ListOutput = %ListOutput%;
WScript.Echo(ListOutput);
}"

Nived_Nambiar · ‎04-20-2023

Hi @Dsimmons

I did not get the point behind why you are running the script for this,

Are you looking for an effiicient way of checking condition in PAD ?

Kindly let me know

View solution in original post

Nived_Nambiar · ‎04-20-2023

Hi @Dsimmons

I did not get the point behind why you are running the script for this,

Are you looking for an effiicient way of checking condition in PAD ?

Kindly let me know

Dsimmons · ‎04-20-2023

Didn't mean to close this.

I am unable to get the proper results back into excel based on on the statements there. If I use conditionals inside of PAD, regardless of iteration, they are not working. Any ideas? It doesn't have to be via script, although I will eventually writing it in pure JS.

Nived_Nambiar · ‎04-20-2023

So column 3 responses should contain the value like 003 ,004 etc right, would it be possible to share how the value looks like to see format ?

Dsimmons · ‎04-20-2023

Absolutely. I will when I get home, the table is formatted html. The var for the list I have not forced type on. I currently get no errors on either the PAD flow or the JS.

Nived_Nambiar · ‎04-20-2023

Ok @Dsimmons

Once i see the how value format of column3 looks i can suggest a better approach.

Current approach would be to use nested if , but if there are more number of validations to perform, then it won't be a good method.

Let me know once you share the format 🙂

Dsimmons · ‎04-22-2023

If I might be able to reach out to you via a direct message of some sort? For table data anyway. We can pick the conversation back up here. The data albeit in the public realm, I'd like to not post publicly if possible.

Nived_Nambiar · ‎04-22-2023

Hi @Dsimmons

You can message me privately else, if you can just show how column 3 values looks (using a sample value) that would be also fine 🙂

Dsimmons · ‎04-22-2023

There just tagged as a cell in an HTML table. <td>001</td>. It does say earlier on that

<html><head><meta http-equiv="Content-Type" content="text/html;

Nived_Nambiar · ‎04-22-2023

Ok so the value is directly 001, so why you u try with switch actions then ?

Dsimmons · ‎04-23-2023

Length of time prior, I never though pf evaluating a list using switch actions. It would have been a nightmare doing the for upwards of 20 variables in some cases. Hang tight. Let me try something.

Nived_Nambiar · ‎04-23-2023

Hi @Dsimmons

Also another easy would be trying using custom object creation, for this case, you can create a custom object like below

{
"001":"Good",
"002":"Good",
"004":"LocCoord",
"006":"HiFacility",
"009":"Unkn",
"999":"Late"
}

Now let's say you have stored this custom obj in var1 variable.

now the number 001 is stored in var2 variable

Now use var1[var2] to get type

I hope this would be easy to design 🙂

Dsimmons · ‎04-23-2023

The custom object would be inside the JS correct? Or is there a way to do that inside PAD? Im attempting to follow, but this is my first go at PAD, let alone coding something to use inside of PAD in JS.