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
17/40 - Jupiter
cols: 8
rows: 54
(1) [Array(8)]
1 + insync
platinum
/rrp|trade|platinum|special/gim
2 + falseinsync
platinum
1 + insync
gold
/rrp|trade|platinum|special/gim
3 + 6
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)
if (searchExp.test(headvalues[0][numcols].toString().toLowerCase()) == true){
console.log("2 + " + testValue + headvalues[0][numcols].toString().toLowerCase())
}
else{
console.log("3 + " + numcols)
allsheets[i].getUsedRange().getColumn(numcols).delete(ExcelScript.DeleteShiftDirection.left)
}
numcols = numcols -1
testValue = headvalues[0][numcols].toString().toLowerCase().includes("trade")
}
}
}
Share
Report
14
All responses (
Answers (
