Run Office-Script Error

[Anonymous]

Hi @,

I am creating an Excel spreadsheet with a flow.

After this Excel file is created, I would like to use an Office script to customize the format of the table. Both the Excel spreadsheet and the Office script are located in the document folder of a SharePoint site.

Executing the "Run Script" action is also the last action at the end of the flow.

As a test, I have already implemented a delay to be sure that the penultimate action, adding rows to the Excel spreadsheet, is actually completed:

If I now execute the flow, the last action "Run script" always results in the error message: Workbook not found.

However, a second test flow that only includes the action to run the script sometimes works fine.

What I noticed is that the output of the two flows is different.
For the regular flow the output looks like this:

{
    "host": {
        "connectionReferenceName": "shared_excelonlinebusiness",
        "operationId": "RunScriptProd"
    },
    "parameters": {
        "source": "sites/xxxxx.sharepoint.com,0e02d3bc-b17c-484a-a5d2-c98b85bf9403,6d77e321-dfd1-4111-a544-dca17298ba4a",
        "drive": "b!vNMCDnyxSkil0smLhb-UAyHjd23R3xFBpUTcoXKYukrUa8bmNOzbTZr7yFGQLlVE",
        "file": "/%252fFreigegebene%2bDokumente%252fPositionen.xlsx" 
        "scriptId": "ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F01L2ZOWBQF57J76CM2SRE2W2DU7SVISIGA",
        "ScriptParameters": {}
 }

and with the test flow so:

{
    "host": {
        "connectionReferenceName": "shared_excelonlinebusiness",
        "operationId": "RunScriptProd"
    },
    "parameters": {
        "source": "sites/xxxxx.sharepoint.com,0e02d3bc-b17c-484a-a5d2-c98b85bf9403,6d77e321-dfd1-4111-a544-dca17298ba4a",
        "drive": "b!vNMCDnyxSkil0smLhb-UAyHjd23R3xFBpUTcoXKYukrUa8bmNOzbTZr7yFGQLlVE",
        "file": "01QPOW3TCKY6FO3PPQTNAJINT7CEXPBWLV",
        "scriptId": "ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F01L2ZOWBQF57J76CM2SRE2W2DU7SVISIGA",
        "ScriptParameters": {}
    }
}

I can't explain the difference in the "file" parameter, but this seems to be the problem.

The Office script itself looks like this (1st try):

function main(workbook: ExcelScript.Workbook) 
{
  let backup = workbook.getTable("Backup");
  // Sort on table: backup column index: '0'
  backup.getSort().apply([{key: 0, ascending: true}]);
  // Auto fit the columns of range all cells on tabelle1
  backup.getRange().getFormat().autofitColumns();
}

2st try:

function main(workbook: ExcelScript.Workbook)
{
  // Your code here
  let currentWorksheet = workbook.getActiveWorksheet();
  currentWorksheet.getRange().getFormat().autofitColumns();
}

This is my first office script. Since it works in the test flow, I don't think it can be the problem. Does anyone have any idea how I can solve this problem ?

Regards

Herb

[GeoffRen · Accepted solution]

What is the exact location of your file? In the regular flow it is '/Freigegebene Dokumente/Positionen.xlsx', is that correct? Though if you selected both files in the regular and test flows using the file picker interface then they should have the same value. If you click 'Peek Code' what does it say for the file parameter? 

And if you delete and then recreate the regular Flow what happens?

[Anonymous]

Hi @GeoffRen,

Thank you for your feedback.

I think I've now figured out for myself what the real problem is.

In my regular flow, currently starting the Office script is the last action within the flow. The second to last action is the creation of the Excel file or the filling of the individual rows by an Apply to each loop. Thus, the Excel file is probably still locked by this action and the last action with the start of the script can therefore not be executed. In my attempts with the test flow, I had previously copied the Excel file to OneDrive and then started the flow. Now the file was no longer locked, and the script could be executed.

Unfortunately, there is obviously no way to unlock the file myself.

I have looked at a few solutions on the Internet, all of which include some kind of time loop until SharePoint has unlocked the file again.

I haven't quite finished creating my regular flow yet. There are still some time-consuming actions to come, so I will now try to put the action with the starts of the office script again at the very end, in the hope that by then the file is released again.

I hereby close my inquiry again and would like to thank you again for your help.

Regards

Herb