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

[Gilli]

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. 

[FLMike]

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

[Gilli · Accepted solution]

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

[Gilli · Accepted solution]

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