Problems with 365 connector limitations

(0) Share

Report

Posted on by senvd

I have a flow that runs a query against a dataset, then it process this data to transform in a csv file and then a xlsx file.

The problem occurs when the office 365 connector (Send a HTTP Request - POST method) runs. I realize that this connector can't deal with files bigger than 4mb.

I tried another method, without using premium connectors. It uses office script, but in my tests it did not work with more than a thousand lines.

The goal with this flow is simple:

Take data from power bi, transform in xlsx file and send in an email.

Could anyone help me?

Categories:

Using connectors

Building flows

General topics

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Expiscornovus 33,851 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @senvd,

Out of interest, what is your data source/dataset? Is it a Semantic Model in a Power BI workspace or something different (like a parquet file in Fabric)?

Additionally, is the requirement that the file need to be attached? Or would a link to the file also be ok?

Happy to help out 😁

I share more #PowerAutomate and #SharePointOnline content on my Blog, LinkedIn, Bluesky profile or Youtube Channel

Was this reply helpful? Yes No
Assisted by AI

Ruturaj 41 on at

Like (0)

Report
Copy link

Link copied!
The solution outlined (using a Template + Office Script) is the standard workaround for high-volume data. The key to making it work for more than 1,000 lines is to avoid loops in your script and instead paste the entire dataset as a single block (Array) using range.setValues().

Phase 1: Preparation (The Template)

Before building the flow, you need a destination file.

Create a blank Excel file (e.g., ReportTemplate.xlsx).

Add your headers in Row 1 (exactly matching your data columns).

Format the headers/columns as desired.

Save this file in a SharePoint Document Library that the Flow can access.

Phase 2: The Office Script (The Engine)

You need a script that accepts the data as a big block (JSON) rather than iterating row-by-row.

Open your ReportTemplate.xlsx in Excel Online.

Go to the Automate tab -> New Script.

Paste the following code and save it as BulkPasteData.

function main(workbook: ExcelScript.Workbook, jsonData: MultiColData[]) { // 1. Get the worksheet let sheet = workbook.getActiveWorksheet(); // 2. Check if we have data to process if (jsonData.length > 0) { // 3. Convert JSON object array to 2D array (required for setValues) // We assume the JSON keys match the column order you want const dataValues = jsonData.map(row => { return [row.Col1, row.Col2, row.Col3]; // Replace with your ACTUAL JSON keys }); // 4. Determine the range size // Start at A2 (assuming headers are in A1) // resize(numRows, numCols) let range = sheet.getRange("A2").getResizedRange(dataValues.length - 1, dataValues[0].length - 1); // 5. Write data in ONE operation (This is the performance fix) range.setValues(dataValues); } } // Interface to define your data structure (Update types as needed) interface MultiColData { Col1: string; Col2: string; Col3: number; }

Phase 3: The Power Automate Flow

Here is the step-by-step construction of the Flow.

1. Get and Clean Data

Run a query against a dataset: (Power BI action) fetches your raw data.

Select (Data Operation):

From: The body/output of the Power BI step.

Map: Define your columns here. This effectively converts your data into a clean JSON Array.

Note: Ensure the keys here match the Interface in your script (e.g., Col1, Col2).

2. Manage the File (The "Template Logic")

Get file content (SharePoint):

Point this to your ReportTemplate.xlsx.

Create file (SharePoint):

Folder Path: A temp folder or the final destination.

File Name: Report_@{utcNow('yyyyMMdd')}.xlsx

File Content: Output from the "Get file content" step above.

(This creates a fresh copy of the template so you don't overwrite the master).

3. Write the Data

Run script (Excel Online Business):

Location/Library: SharePoint details.

File: Select the Id from the "Create file" step.

Script: Select BulkPasteData.

jsonData: Pass the Output of the "Select" action here.

4. Send the Email

Get file content (SharePoint) - Optional:

Some flows require re-reading the file to ensure the content is updated, though usually, the file created in Step 2 is accessible.

File Identifier: The Id from the "Create file" step.

Send an email (V2):

Attachments Name: Report.xlsx

Attachments Content: The output of the "Get file content" step (the one that points to the new file).

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

Ricardo D. Souza 6 on at

Like (0)

Report
Copy link

Link copied!

All Power Platform connectors will have some kind of limitation related to records retrieved, so is necessary to implement some pagination mechanism on automations based on connector documentation.

The solution for your scenario depends on the quantity of records to be retrieved, but I suggest that you use a Power Platform Dataflow to get the data and a cloud flow to create the export. Even so, it is possible that you have to implement pagination on the cloud flow.

Regards

1 people found this reply helpful.

Was this reply helpful? Yes No
senvd 6 on at

Like (0)

Report
Copy link

Link copied!

@Ruturaj I'm still getting the "Gateway Timeout" message with this script

Was this reply helpful? Yes No
senvd 6 on at

Like (0)

Report
Copy link

Link copied!

@Ruturaj I tried to run the flow with only 1000 lines and it worked. When I try with more lines, it doesn't work and shows a badGateway message: GatewayTimeout

Was this reply helpful? Yes No