You’re offline. This is a read only version of the page.
Hello,
I have a strange issue.
When I run my excel script with office online or excel desktop, the logging result looks fine. However, when I use Automate to trigger it, the return result was wrong.
Share
Reportfunction main(workbook: ExcelScript.Workbook) {
// Get the active worksheet
let sheet = workbook.getWorksheets()[0];
let rowCount = sheet.getUsedRange().getRowCount();
let objList: { [key: string]: string }[] = [];
for (let row = 2; row < rowCount; row++) {
let tempObj = {
columna: "",
columnb: "",
columnc: "",
columnd: "",
columne: "",
columnf: "",
columng: "",
columnh: "",
columni: "",
columnj: "",
columnk: "",
columnl: sheet.getCell(row, 11).getText().toString(),
columnm: "",
columnn: sheet.getCell(row, 13).getText().toString(),
columno: sheet.getCell(row, 14).getText().toString(),
columnp: "",
columnq: "",
columnr: "",
columns: "",
columnt: "",
columnu: "",
columnv: sheet.getCell(row, 21).getText().toString(),
columnw: "",
columnx: sheet.getCell(row, 23).getText().toString(),
columny: "",
columnz: ""
};
objList.push(tempObj);
}
// Convert the object to a JSON string
let json = JSON.stringify(objList);
json = json.replace(new RegExp('columna', 'g'), 'Airline Carrier')
json = json.replace(new RegExp('columnb', 'g'), 'Flight Number')
json = json.replace(new RegExp('columnc', 'g'), 'AWB #')
json = json.replace(new RegExp('columnd', 'g'), 'Origin Pickup City')
json = json.replace(new RegExp('columne', 'g'), 'Destination Delivery City')
json = json.replace(new RegExp('columnf', 'g'), 'Shipper Name')
json = json.replace(new RegExp('columng', 'g'), 'Origin Terminal Name')
json = json.replace(new RegExp('columnh', 'g'), 'Loading Port Name')
json = json.replace(new RegExp('columni', 'g'), 'Discharge Port Name')
json = json.replace(new RegExp('columnj', 'g'), 'Destination Terminal Name')
json = json.replace(new RegExp('columnk', 'g'), 'Actual Pick-up Arrival Date')
json = json.replace(new RegExp('columnl', 'g'), 'Actual Delivery Arrival Date')
json = json.replace(new RegExp('columnm', 'g'), 'Original Port ETD Date')
json = json.replace(new RegExp('columnn', 'g'), 'Actual Port Arrival Date')
json = json.replace(new RegExp('columno', 'g'), 'Actual Port Departure Date')
json = json.replace(new RegExp('columnp', 'g'), 'Destination Terminal Arrival Date')
json = json.replace(new RegExp('columnq', 'g'), 'Customer Reference Number')
json = json.replace(new RegExp('columnr', 'g'), 'BOL')
json = json.replace(new RegExp('columns', 'g'), 'Actual Quantity')
json = json.replace(new RegExp('columnt', 'g'), 'Actual Weight')
json = json.replace(new RegExp('columnu', 'g'), 'Volume')
json = json.replace(new RegExp('columnv', 'g'), 'CHR Status')
json = json.replace(new RegExp('columnw', 'g'), 'Customer Name')
json = json.replace(new RegExp('columnx', 'g'), 'CHR Number')
json = json.replace(new RegExp('columny', 'g'), 'House B/L Number')
json = json.replace(new RegExp('columnz', 'g'), 'Dimensional Weight')
// Log the JSON string
console.log(json)
return json
}
And this is my table content
It should return value:
[
{
"Airline Carrier": "",
"Flight Number": "",
"AWB #": "",
"Origin Pickup City": "",
"Destination Delivery City": "",
"Shipper Name": "",
"Origin Terminal Name": "",
"Loading Port Name": "",
"Discharge Port Name": "",
"Destination Terminal Name": "",
"Actual Pick-up Arrival Date": "",
"Actual Delivery Arrival Date": "Actual Delivery Arrival Date",
"Original Port ETD Date": "",
"Actual Port Arrival Date": "Actual Port Arrival Date",
"Actual Port Departure Date": "Actual Port Departure Date",
"Destination Terminal Arrival Date": "",
"Customer Reference Number": "",
"BOL": "",
"Actual Quantity": "",
"Actual Weight": "",
"Volume": "",
"CHR Status": "CHR Status",
"Customer Name": "",
"CHR Number": "CHR Number",
"House B/L Number": "",
"Dimensional Weight": ""
},
{
"Airline Carrier": "",
"Flight Number": "",
"AWB #": "",
"Origin Pickup City": "",
"Destination Delivery City": "",
"Shipper Name": "",
"Origin Terminal Name": "",
"Loading Port Name": "",
"Discharge Port Name": "",
"Destination Terminal Name": "",
"Actual Pick-up Arrival Date": "",
"Actual Delivery Arrival Date": "actual Delivery Arrival Date 2",
"Original Port ETD Date": "",
"Actual Port Arrival Date": "Actual Port Arrival Date2",
"Actual Port Departure Date": "actual Port Departure Date2",
"Destination Terminal Arrival Date": "",
"Customer Reference Number": "",
"BOL": "",
"Actual Quantity": "",
"Actual Weight": "",
"Volume": "",
"CHR Status": "CHR Status2",
"Customer Name": "",
"CHR Number": "CHR Number2",
"House B/L Number": "",
"Dimensional Weight": ""
}
]
But when I use Automate it was:
[
{
"Airline Carrier": "",
"Flight Number": "",
"AWB #": "",
"Origin Pickup City": "",
"Destination Delivery City": "",
"Shipper Name": "",
"Origin Terminal Name": "",
"Loading Port Name": "",
"Discharge Port Name": "",
"Destination Terminal Name": "",
"Actual Pick-up Arrival Date": "",
"Actual Delivery Arrival Date": "CHR Number",
"Original Port ETD Date": "",
"Actual Port Arrival Date": "CHR Number",
"Actual Port Departure Date": "CHR Number",
"Destination Terminal Arrival Date": "",
"Customer Reference Number": "",
"BOL": "",
"Actual Quantity": "",
"Actual Weight": "",
"Volume": "",
"CHR Status": "CHR Number",
"Customer Name": "",
"CHR Number": "CHR Number",
"House B/L Number": "",
"Dimensional Weight": ""
},
{
"Airline Carrier": "",
"Flight Number": "",
"AWB #": "",
"Origin Pickup City": "",
"Destination Delivery City": "",
"Shipper Name": "",
"Origin Terminal Name": "",
"Loading Port Name": "",
"Discharge Port Name": "",
"Destination Terminal Name": "",
"Actual Pick-up Arrival Date": "",
"Actual Delivery Arrival Date": "CHR Number2",
"Original Port ETD Date": "",
"Actual Port Arrival Date": "CHR Number2",
"Actual Port Departure Date": "CHR Number2",
"Destination Terminal Arrival Date": "",
"Customer Reference Number": "",
"BOL": "",
"Actual Quantity": "",
"Actual Weight": "",
"Volume": "",
"CHR Status": "CHR Number2",
"Customer Name": "",
"CHR Number": "CHR Number2",
"House B/L Number": "",
"Dimensional Weight": ""
}
]
The automate workflow has no problem, I just simply trigger it to run certain file.
@Truman - Save the Script with a different unique name, select the new script and try to run it... Use the path and select the document manually.
Hey
I see that you are passing "id" manually select the file and run it. "Power Automate has some limitation a for the dynamic selection of workbooks"
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.
Hey @Truman
In the run script action if you manually select the path can you check if it gives correct result...
If you run the script in the Excel Online on the Excel file do you get correct results?
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.
#1
WarrenBelz
146,743
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,089
Most Valuable Professional
All responses (
Answers (
