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


God Bless to all.