** HELP BUILD FLOW ** UPLOAD EXCEL FILE TO SHARE POINT AND CREATE TABLE + MORE

[sbruk89]

Hi Community,

I would really appreciate your help I'm trying to help build a flow whereby when a file is uploaded lets say manually or automatically into a folder, I want the flow to create a table and name it "Latest" the file will be uploaded daily but the number of rows could change on a daily basis say one day it could be 1300 the next day it could be 1200 and so on. Once the table has been created I then would like the flow to be able to sort a couple of the columns in Ascending order and/or in descending, in the picture provided lets say I want Load date to sorted in Descending order  

Any help would be greatly appreciated.

[Nived_Nambiar]

Hi @sbruk89 

How does sorting should be done, would it be a constant operation /would be sort requirement changes ?

[sbruk89]

Hi @Nived_Nambiar,

Sorting will be always be Asset Reference Ascend and Load Date Descend. The file that is always being uploaded to sharepoint in the designated folder is "Automation - Key Fields only - Latest Order Input" the table/columns will always be the same.

[Nived_Nambiar]

Hi @sbruk89 

You can 2 office scripts , one for creating table and other to be used for sorting based on columns

see below for office scripts references.

For creating table

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();

    // Get the used range of the sheet
    let usedRange = sheet.getUsedRange();

    if (usedRange) {
        // Get the row count of the used range
        let rowCount = usedRange.getRowCount();

        // Get the column count of the used range
        let columnCount = usedRange.getColumnCount();

        // Get the starting row and column indexes
        let startRow = usedRange.getRowIndex();
        let startColumn = usedRange.getColumnIndex();

        // Define the range for the table
        let range = sheet.getRangeByIndexes(startRow, startColumn, rowCount, columnCount);

        // Create the table
        let table = sheet.addTable(range, true);
    }
}

This will create a table with table name table1

Next run this office script on the file which helps to sort based on column

Below script can used to sort for first column , you can edit as per your requirement.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();

    // Get Table 'Clients' on selectedSheet
    let myTable = selectedSheet.getTable('table1');
    myTable.getSort().apply([{ key: 0, ascending: false }]);
}

This will help you 🙂

Let me know if you have any questions !

Mark it as solution if it resolves your query !

[sbruk89]

thanks @Nived_Nambiar, I will try those shortly.

I have one issue and I don't know how to resolve it. The file "Automate - Key Field Only - Latest Order Input.xlsx" is uploaded and deleted daily so the "Create a Table" action breaks every time a new version of the above mentioned file is uploaded, is there anyway around this?

[Nived_Nambiar]

what error it is throwing ?

kindly let me know ?

[sbruk89]

@Nived_Nambiar 

[Tanya70]

I would use power query inside excel to do this. Then combine with power automate desktop and even online if you have the licenses for that.

[Nived_Nambiar]

Hi @sbruk89 

The way you have written script in run script action is wrong !

First take an excel file and  under Automate tab, select new script and write the two scripts which i have shared, and save it. Then use run script action, and select required script from dropdown option 

Hope this helps !

Mark it as solution if it resolves your query !

[sbruk89 · Accepted solution]

@Nived_Nambiar ,

Its not possible to add the script to the file as it is a report that is generated daily hence the need to upload to a sharepoint site and then for it to create a table. I have managed to figure out how to upload and create the table everytime its uploaded but im struggling with the sorting of the columns for "Asset Reference" & "RFS Date". Would it be possible to do an expression?

[Tanya70]

Maybe try this

https://powerusers.microsoft.com/t5/General-Power-Automate/Filter-Query-Order-By-on-Excel-Rows/td-p/...

[Nived_Nambiar · Accepted solution]

Hi @sbruk89 

I have shared you the script for sort purposes, have you tried that 

Also see whether this action helps as well ?