Power Platform Community Forum Thread Details

ello,

I haveI created a Power Automate workflow that executes office scripts in respective Sharepoint Excel files.

The workflow runs flawlessly (no error message). The manual execution of the script works fine (the active filter is executed again and the new correct data selection is shown in the datasheet).

However, after an error-free execution of the workflow, the Excel files are not updated.
I am desperate; what could be the problem?

I uploaded a screenshot of the power workflow.

Thank you for your support

Ollivier

Please find the both scripts:

1- DataActualisation

function main(workbook: ExcelScript.Workbook) {
    // Aktualisieren Sie alle Datenverbindungen in der Arbeitsmappe
    workbook.refreshAllDataConnections();
}
 
2- Active Filter actualisation
 
function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getWorksheet("CSRD");
    let range = sheet.getRange("H9:T1000"); // Passen Sie den Bereich an Ihre Bedürfnisse an
    // Überprüfen, ob der AutoFilter bereits vorhanden ist
    if (!sheet.getAutoFilter()) {
        sheet.getAutoFilter().apply(range);
    } else {
        sheet.getAutoFilter().clearCriteria();
        sheet.getAutoFilter().apply(range);
    }
    // Filterkriterien anwenden
    sheet.getAutoFilter().apply(range, 1, {
        filterOn: ExcelScript.FilterOn.values,
        values: ["Ja"]
    });
}

Categories:

Using flows

If I am reading your script correctly, it just goes through the spreadsheet and makes the columns fit to width. I don't see any errors in it. Since your flow appears to be in German, I am not positive, but I think you are you using the run script action. Instead, save your script to an *.osts file and use the run script from SharePoint library action instead.

I use the attached file. Save it to your OneDrive in My files > Documents > Office Scripts. I find running the script from the file encounters less problems.

If this does not solve the issue, it could be all of the parallel actions you have in your flow and the script is overwhelmed. You may need to do this sequentially instead.

This is the script within the file:

{"version":"0.3.0","body":"\nfunction main(workbook: ExcelScript.Workbook) {\n // Get all the worksheets in the workbook\n let sheets = workbook.getWorksheets();\n\n // Loop through each worksheet\n sheets.forEach(sheet => {\n // Get all the tables in the worksheet\n let tables = sheet.getTables();\n\n // Loop through each table\n tables.forEach(table => {\n // Autofit the columns in the table\n table.getRange().getFormat().autofitColumns();\n });\n });\n}\n","description":"","noCodeMetadata":"","parameterInfo":"{\"version\":1,\"originalParameterOrder\":[],\"parameterSchema\":{\"type\":\"object\",\"default\":{},\"x-ms-visibility\":\"internal\"},\"returnSchema\":{\"type\":\"object\",\"properties\":{}},\"signature\":{\"comment\":\"\",\"parameters\":[{\"name\":\"workbook\",\"comment\":\"\"}]}}","apiInfo":"{\"variant\":\"synchronous\",\"variantVersion\":2}"}