Power Platform Community Forum Thread Details

Hi,

I have a power automate that runs an excel script.

When I run the excel script through power automate it gives a different result then if I run it through manually.

BUT it is only happening opn 3 of the 46 sheets the codes run on.

Log output from the two versions is

Manually run

Power automate run

"[2022-12-05T03:39:37.5960Z] 21/45 - Jupiter",
"[2022-12-05T03:39:37.8930Z] cols: 8",
"[2022-12-05T03:39:37.8930Z] rows: 54",
"[2022-12-05T03:39:37.9550Z] [[\"Part code\",\"Short Description\",\"Description\",\"Warranty\",\"RRP \\n(Inc GST)\",\"TRADE \\n(Ex GST)\",\"InSync\\nGold\",\"InSync\\nPlatinum\"]]",
"[2022-12-05T03:39:37.9710Z] 1 + insync\nplatinum",
"[2022-12-05T03:39:37.9710Z] {}",
"[2022-12-05T03:39:37.9710Z] 3 + 7",
"[2022-12-05T03:39:38.0650Z] 1 + insync\ngold",
"[2022-12-05T03:39:38.0800Z] {}",
"[2022-12-05T03:39:38.0800Z] 3 + 6",

The difference is when i check the regex in power automate it doesnt find a match but when i run in excel directly it does find a match. (it is supposed to find a match)

this runs on over 40 worksheets and fails on 3-4 of them through PA.

Code is

function main(workbook: ExcelScript.Workbook, level: string) {

level = "plat"

let allsheets = workbook.getWorksheets()

level = level.toLowerCase()

const tradeKeyWords: string [] = [" rrp", "trade"]

const platKeyWords : string[] = ["rrp", "trade", "platinum", "special"]

const goldKeyWords: string[] = ["rrp", "trade", "gold", "special"]

let searchExp = new RegExp(tradeKeyWords.join("|"), "mgi")

if (level == "trade"){ searchExp = new RegExp(tradeKeyWords.join("|"),"mgi")}

if (level == "gold") { searchExp = new RegExp(goldKeyWords.join("|"), "mgi") }

if (level == "plat") { searchExp = new RegExp(platKeyWords.join("|"), "mgi") }

for (let i = 17; i < allsheets.length ; i++){

//console.log(allsheets[i].getName())

console.log( i + "/" + allsheets.length + " - "+ allsheets[i].getName())

let headrow = allsheets[i].getUsedRange().find("Trade", { completeMatch: false, matchCase: false, searchDirection: ExcelScript.SearchDirection.forward }).getRowIndex()

let numcols = allsheets[i].getUsedRange().getColumnCount();

let numrows = allsheets[i].getUsedRange().getRowCount();

console.log("cols: " + numcols)

console.log("rows: " + numrows)

let headvalues = allsheets[i].getRangeByIndexes(headrow,0,1,numcols).getValues()

console.log(headvalues)

numcols = numcols -1

let testValue = false

testValue = headvalues[0][numcols].toString().toLowerCase().includes("trade")

while( testValue === false ){

console.log("1 + " + headvalues[0][numcols].toString().toLowerCase())

console.log(searchExp)