You’re offline. This is a read only version of the page.
Hi,
I'm trying to generate a Word document by pulling information from an Excel file. I'm getting stuck trying to read the information from Excel.
I'm using 'Run script from SharePoint library', which then runs the following office script to return the data that will populate the Word document:
Share
Reportfunction main(workbook: ExcelScript.Workbook) {
// Set some variables
let ws = workbook.getWorksheet("Review");
// Create objectwith data
let objMeeting = {
"Meeting Date": ws.getRange("rngReviewDate").getValue(),
"Location": ws.getRange("C17").getValue(),
"Attendees": ws.getRange("C18").getValue(),
"Agenda": ws.getShape("tbAgenda").getTextFrame().getTextRange().getText(),
"General Notes": ws.getShape("tbPersonalNotes").getTextFrame().getTextRange().getText(),
"Superannuation Notes": ws.getShape("tbSuperNotes").getTextFrame().getTextRange().getText(),
"Investment Notes": ws.getShape("tbInvNotes").getTextFrame().getTextRange().getText(),
"Debt Notes": ws.getShape("tbDebtNotes").getTextFrame().getTextRange().getText(),
"Insurance Notes": ws.getShape("tbInsNotes").getTextFrame().getTextRange().getText(),
"RPisk Profile Notes": ws.getShape("tbRPNotes").getTextFrame().getTextRange().getText(),
"General Outcomes": ws.getShape("tbPersonalAction").getTextFrame().getTextRange().getText(),
"Superannuation Outcomes": ws.getShape("tbSuperAction").getTextFrame().getTextRange().getText(),
"Investment Outcomes": ws.getShape("tbInvAction").getTextFrame().getTextRange().getText(),
"Debt Outcomes": ws.getShape("tbDebtAction").getTextFrame().getTextRange().getText(),
"Insurance Outcomes": ws.getShape("tbInsAction").getTextFrame().getTextRange().getText(),
"Risk Profile Outcomes": ws.getShape("tbRPAction").getTextFrame().getTextRange().getText(),
}
return objMeeting;
}
If I manually run the Office Script in Excel I can see that the script works and objMeeting stores all the information correctly. However, in Power Automate the body from outputs is:
{
"result": {
"Meeting Date": "",
"Location": "",
"Attendees": "",
"Agenda": "",
"General Notes": "",
"Superannuation Notes": "",
"Investment Notes": "",
"Debt Notes": "",
"Insurance Notes": "",
"RPisk Profile Notes": "",
"General Outcomes": "",
"Superannuation Outcomes": "",
"Investment Outcomes": "",
"Debt Outcomes": "",
"Insurance Outcomes": "",
"Risk Profile Outcomes": ""
},
"logs": []
}
Thanks for any help.
After further testing it looks like the text boxes are not the issue. There is something going wrong with the object variable when the script is run from Power Automate. When it gets to the point of setting the value for 'Insurance Notes', it overwrites the value for all the object properties. Therefore, because the text of "tbRPAction" in my spreadsheet is "", the result in Power Automate has been all the object properties coming through as "".
I don't know what exactly the issue is, but have found a workaround that is ok for my case. Instead of returning an object I return an array. It's not as easy to reference but does the job. The Office Script is now as follows:
All responses (
Answers (function main(workbook: ExcelScript.Workbook) {
// Set some variables
let ws = workbook.getWorksheet("Review");
// Create objectwith data
let arrMeeting = [
ws.getRange("rngReviewDate").getValue(),
ws.getRange("C17").getValue(),
ws.getRange("C18").getValue(),
ws.getShape("tbAgenda").getTextFrame().getTextRange().getText(),
ws.getShape("tbPersonalNotes").getTextFrame().getTextRange().getText(),
ws.getShape("tbSuperNotes").getTextFrame().getTextRange().getText(),
ws.getShape("tbInvNotes").getTextFrame().getTextRange().getText(),
ws.getShape("tbDebtNotes").getTextFrame().getTextRange().getText(),
ws.getShape("tbInsNotes").getTextFrame().getTextRange().getText(),
ws.getShape("tbRPNotes").getTextFrame().getTextRange().getText(),
ws.getShape("tbPersonalAction").getTextFrame().getTextRange().getText(),
ws.getShape("tbSuperAction").getTextFrame().getTextRange().getText(),
ws.getShape("tbInvAction").getTextFrame().getTextRange().getText(),
ws.getShape("tbDebtAction").getTextFrame().getTextRange().getText(),
ws.getShape("tbInsAction").getTextFrame().getTextRange().getText(),
ws.getShape("tbRPAction").getTextFrame().getTextRange().getText()
]
return arrMeeting;
}
@FLMike thanks for pointing me in the right direction. After a bit more testing I've found the issue is reading text from text boxes. This works without issue when the Office Script is run directly in Excel. However, when the script is run from Power Automate it fails.
Hi @Gilli
Option 2:
Use Run Script (Excel Business) instead of the SharePoint one. You can STILL access it even if its in SharePoint, since its all just OneDrive behind sharepoint anyway.
Option 2.
Can you change your script to access the Table data in your Worksheet.
So like this
// Get the H1 worksheet.
let worksheet = workbook.getWorksheet("Review");
// Get the first (and only) table in the worksheet.
let table = worksheet.getTables()[0];
// Get the data from the table.
let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();
and then return the tableValues. Because I believe it needs to get data from the Table to work.
A secondary test, would be in another file, create a really simple Script, that just does something to the file. And then run that with PA, then validate that the Flow did in fact trigger the Script and that the script did in fact update the content somehow.
If you like my answer, I would really appreciate if you please Mark it as Resolved, and give it a thumbs up, so it can help others
Cheers
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey
#1
DBO_DV
25
Super User 2025 Season 1
#2
CU09051456-0
22
#3
Michael E. Gernaey
21
Super User 2025 Season 1
