Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

Ways to import Excel data into a flow

(0) ShareShare
ReportReport
Posted on by 8
Hello,
 
Has anyone found any good ways to import Excel data into a flow to email out?  I tried the Action: List Rows present in a table option but that maxes out at 5k rows.  Side note - whoever set these ridiculously low limits at Msft needs to be beaten but that's another story lol.  Has anyone been able to do this successfully in their flow and if so could you please give a simple example of it?
 
Thanks! 
  • rzaneti Profile Picture
    rzaneti 3,365 on at
    Ways to import Excel data into a flow
    Hi,
     
    Office Scripts is available only in the 365 version of Excel, but you can apply it to files created in previous versions, as long as they are stored in OneDrive or SharePoint. 
     
    About the missing Automate tab, what is your current 365 license? I did some research and found this thread in the Excel Community listing the licenses that support the Office Scripts: https://answers.microsoft.com/en-us/msoffice/forum/all/automate-tab-in-excel/f89cb0f4-6ab7-4cfa-9340-e49736115fa2 . It looks like E1 or personal licenses are excluded. 
     
    About where to paste the script, you must select the Automate tab (if you have a license that supports it, of course), as highlighted in red and click New Script (in green). It will display the Code editor (in blue). Click edit, and then paste the script into the script pane (in yellow), replacing the code that is already there:
     
     
    Change the name of your Script and save it. In Power Automate, you must select the Script that you just created by name (in my case, retrieve table):
     
     
     
    As recommended by Alex, you could also use an HTTP request to access this data. This is a good idea and I'm digging into this topic to try to identify the ideal endpoint to make this HTTP request.
     
    Let me know if it works for you or if you need any additional help!


    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Accepted Answer.
    If this answer helps you in any way, please give it a like.

    http://digitalmill.net/
    https://www.linkedin.com/in/raphael-haus-zaneti/
  • CU05121502-0 Profile Picture
    CU05121502-0 8 on at
    Ways to import Excel data into a flow
     
    I think i'm missing some connection or something - It is asking me for the parameters and there just seems to be too many errors.  The file in question is 2020 version of Excel that doesn't have the Automate tab for example.  Is it possible then that it can't run scripts against it?  Or is there something inside Excel that I need to do?  All I did is I took your script and put it into the Run Script action in PA - was I supposed to so something else? In Excel? 
  • CU05121502-0 Profile Picture
    CU05121502-0 8 on at
    Ways to import Excel data into a flow
     
    Thanks for the reply - maybe I misunderstood - is this supposed to be entered into the Excel file somewhere?  I thought the script just went into the Run Script action in PA.  I've never used this action/script before so its possible I put it in the wrong spot?

    Here is the script I ran:

    function main(workbook: ExcelScript.Workbook) {
    const shoptable = workbook.getTable('Table_ExternalData_1')

    return {data: table.getRangeBetweenHeaderAndTotal().getValues()}

    }
  • rzaneti Profile Picture
    rzaneti 3,365 on at
    Ways to import Excel data into a flow
    Hi,
     
    Can you share an image of your script in the Excel spreadsheet? I did a quick search online and it looks like the error that you are receiving may happen when there is some error in the code. Besides that, it should not be asking for parameters in PA. Here is how it should look like:
     
    About the size issue that I've mentioned, it is actually more like a "timeout issue". This Power Automate action has a known limitation of only process scripts that takes less than 120 seconds to run. Reading data from tables is usually a not time-consuming task for an Office Script, but the size of a table can impact in the time to run. I believe that 62k rows and 20 columns may not take much more than 1 minute, assuming that the script will only retrieve the data from the table and make no transformation on it before sending to PA.
     
    Let me know if it works for you or if you need any additional help!


    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Accepted Answer.
    If this answer helps you in any way, please give it a like.

    http://digitalmill.net/
    https://www.linkedin.com/in/raphael-haus-zaneti/
  • AlexEncodian Profile Picture
    AlexEncodian 4,302 on at
    Ways to import Excel data into a flow
    The easiest option is Encodian's Excel - Extract Rows action (1 click set-up) which works for huge excel files with or without tables. Its a standard connector but it is 3rd party (free for low volumes). Alternatively you're looking at scripts or Graph API
  • CU05121502-0 Profile Picture
    CU05121502-0 8 on at
    Ways to import Excel data into a flow
    Thanks @rzaneti​​​​​​​
     
    I'm getting a lot of errors when trying to run this - first its asking for the script parameters and i'm not sure what to put there - reading online it seems like an arbitrary value - is that right?
     
    The main error I'm getting - and not sure if its related to the parameters, but is this:
     
    Failed to retrieve dynamic inputs. Error details: 'The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScript' failed with status code 'BadRequest'. This may indicate invalid input parameters. Error response: { "status": 400, "message": "Unable to parse script reference.\r\nclientRequestId: dc7b3c9c-f4b7-4411-bf37-f4a4fbe3ab36", "error": { "message": "Unable to parse script reference." }, "source": "excelonline-eus.azconn-eus-003.p.azurewebsites.net" }'
     
    Trying to figure these out but any help would be appreciated - not sure if this overall will work for my 62k rows with 20 columns but hoping so - you mentioned the size issue.  
     
    Thanks!
  • Suggested answer
    rzaneti Profile Picture
    rzaneti 3,365 on at
    Ways to import Excel data into a flow
     
    Depending on the qty of columns, you can use an Office Script for it (Run script action in PA). The downsize of this approach is that your data will be retrieved in a bi-dimensional array format, rather than in an array of objects. For example, a table like this:
     
    Will be represented like this in PA:
     
    I ran a test with this same table, but with 99,999 records and it worked fine (17 seconds to run the script), but you can expect some slowness from PA when accessing the raw outputs:
     
     
    The script itself is pretty simple, and you just need to change the value between parenthesis in the const table line for your actual table name:
    
    function main(workbook: ExcelScript.Workbook) {
        const table = workbook.getTable('Table2')
    
        return {data: table.getRangeBetweenHeaderAndTotal().getValues()}
    
    }
    
     
    To access the output in Power Automate, you can simply use this expression: body('Run_script')['result']['data'].
     
    If you need more instructions on how to create the scripts, you can check the instructions from the following articles:
     
    Let me know if it works for you or if you need any additional help!


    -------------------------------------------------------------------------
    If this is the answer for your question, please mark the post as Accepted Answer.
    If this answer helps you in any way, please give it a like.

    http://digitalmill.net/
    https://www.linkedin.com/in/raphael-haus-zaneti/
     
  • CU05121502-0 Profile Picture
    CU05121502-0 8 on at
    Ways to import Excel data into a flow
    Yes - that is where the 5k comes in.  The default for List Rows present in a table is 256.  If you go to Settings and change it to Pagination - I put in 70k that i'm looking for but it immediately gives an error saying the max is 5k.  
  • abm abm Profile Picture
    abm abm 32,275 on at
    Ways to import Excel data into a flow
    Hi
     
    Did you change the threshold limit of your Excel Action step where you reading the records?
     
    Thanks

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

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,445

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,741

Leaderboard