Skip to main content

Notifications

Community site session details

Community site session details

Session Id : KFiCKOHNoyp80hHIDmKANS
Power Automate - Using Connectors
Answered

Office Script issue when executed through 'Run script from Sharepoint Library

Like (0) ShareShare
ReportReport
Posted on 12 Oct 2023 05:29:48 by 21

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:

 

function 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. 

  • Verified answer
    Gilli Profile Picture
    21 on 13 Oct 2023 at 03:06:36
    Re: Office Script issue when executed through 'Run script from Sharepoint Library

    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:

    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;
    }

     

     

  • Verified answer
    Gilli Profile Picture
    21 on 12 Oct 2023 at 07:51:31
    Re: Office Script issue when executed through 'Run script from Sharepoint Library

    @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. 

  • Michael E. Gernaey Profile Picture
    42,027 Super User 2025 Season 1 on 12 Oct 2023 at 06:50:29
    Re: Office Script issue when executed through 'Run script from Sharepoint Library

    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

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Using Connectors

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 8 Super User 2025 Season 1

#2
lbendlin Profile Picture

lbendlin 7 Super User 2025 Season 1

#3
KT-07051015-0 Profile Picture

KT-07051015-0 4

Overall leaderboard

Featured topics

Loading started