web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Batch Transfer from Po...
Power Automate
Unanswered

Batch Transfer from Power Automate to Excel

(0) ShareShare
ReportReport
Posted on by 128

Good Day to All

 

I have this excel script in which it gets data from power automate SELECT action, and transfer data to excel file in less than a minute for 3500 data files.

 

Now I have a problem, whenever the excel database have tables or other formula, the script will have an error of BADGATEWAY.

 

For anyone who is expert out there, I will share this excel script batch transfer so you can analyze and hopefully find a solution to the problem.

 

Here is the excel batch transfer script.  

 

function main(workbook: ExcelScript.Workbook,
    wsName: string,
    examplevar4: arrayvar[] //Multiple rows in Table
) {
    let sheet = workbook.getWorksheet(wsName);

 

    // Determine the last used row in the worksheet
    const lastUsedRow = sheet.getUsedRange().getLastRow();

 

    // Start populating data from the row after the last used row
    const startingRow = lastUsedRow ? lastUsedRow.getRowIndex() + 2 : 2;

 

    for (let i = 0; i < examplevar4.length; i++) {
        const currentObject = examplevar4[i];
        const formattedrow = [
            [
                currentObject.a, currentObject.b, currentObject.c, currentObject.d,
                currentObject.e, currentObject.f, currentObject.g, currentObject.h,
                currentObject.i, currentObject.j, currentObject.k, currentObject.l,
                currentObject.m, currentObject.n, currentObject.o, currentObject.p,
                currentObject.q, currentObject.r, currentObject.s, currentObject.t,
                currentObject.u, currentObject.v, currentObject.w, currentObject.x,
                currentObject.y, currentObject.z, currentObject.aa, currentObject.ab,
                currentObject.ac, currentObject.ad, currentObject.ae, currentObject.af,
                currentObject.ag
            ]
        ];

 

        // Set the values in the next available row
        const rowIndex = startingRow + i;
        sheet.getRange(`A${rowIndex}:AG${rowIndex}`).setValues(formattedrow);
    }

 

    // Return a response to the Cloud Flow
    return "Packing Tape";
}

 

// Defining Interfaces for MultiVar and Array
interface arrayvar {
    a: string,
    b: string,
    c: string,
    d: string,
    e: string,
    f: string,
    g: string,
    h: string,
    i: string,
    j: string,
    k: string,
    l: string,
    m: string,
    n: string,
    o: string,
    p: string,
    q: string,
    r: string,
    s: string,
    t: string,
    u: string,
    v: string,
    w: string,
    x: string,
    y: string,
    z: string,
    aa: string,
    ab: string,
    ac: string,
    ad: string,
    ae: string,
    af: string,
    ag: string
}

 

 

 

 

And here is my flow 

 

Amirin_0-1697579310714.pngAmirin_1-1697579325004.png

 

God Bless to all.

Categories:
  • takolota1 Profile Picture
    4,980 Moderator on at

    @Amirin 

     

    You can also use this batch Excel template which already has a batch create script that can intake any JSON array & append values to any columns where the JSON keys match the column headers

    https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

     

     

    ______________________________
    Subscribe to YT: https://youtube.com/@tylerkolota?si=me85UONqMsR9Z4Ec

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

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Leaderboard > Power Automate

#1
Mohsin Ali Profile Picture

Mohsin Ali 377

#2
11manish Profile Picture

11manish 302 Super User 2026 Season 2

#3
Henil Profile Picture

Henil 130

Last 30 days Overall leaderboard