Ok, I've been playing around with the solution and got the following.
I'm using the two tables below. One table (ColorsTable) contains the data I want to copy, and the other table (ColorsTableV2) will be where the rows will be copied.
And using the Office Script below which is almost identical to the one you're using except I changed it to take in the worksheet name and table name. You would just need to setup the TableData interface to include your fields.
function main(
workbook: ExcelScript.Workbook,
worksheetName: string,
tableName: string): TableData[] {
// Get the table in the specified worksheet.
const table = workbook.getWorksheet(worksheetName).getTable(tableName);
// Get all the values from the table as text.
const texts = table.getRange().getTexts();
// Create an array of JSON objects that match the row structure.
let returnObjects: TableData[] = [];
if (table.getRowCount() > 0) {
returnObjects = returnObjectFromValues(texts);
}
// Log the information and return it for a Power Automate flow.
console.log(JSON.stringify(returnObjects));
return returnObjects;
}
// This function converts a 2D array of values into a generic JSON object.
// In this case, we have defined the TableData object, but any similar interface would work.
function returnObjectFromValues(values: string[][]): TableData[] {
let objectArray: TableData[] = [];
let objectKeys: string[] = [];
for (let i = 0; i < values.length; i++) {
if (i === 0) {
objectKeys = values[i];
continue;
}
let object = {};
for (let j = 0; j < values[i].length; j++) {
object[objectKeys[j]] = values[i][j];
}
objectArray.push(object as TableData);
}
return objectArray;
}
interface TableData {
Name: string;
Color: string;
"Sign Off": string;
}
Below is the full flow. I'll go into each of the actions.
Run script uses the script above, passing in the worksheet name and table name.
Select adds my properties into an array so it's the correct format for the Graph API call to add the rows.
Site and Add data to Excel (below) are using Invoke an HTTP request from Azure AD.
Site uses the following Uri to get the id from the SharePoint site where my Excel file is located.
https://graph.microsoft.com/v1.0/sites/happywolf.sharepoint.com:/sites/powerplatformtesting?$select=id
Add data to Excel uses the following Uri including the id from the previous action.
https://graph.microsoft.com/v1.0/sites/@{body('Site')?['id']}/lists/Documents/drive/root:/ColorsSheet.xlsx:/workbook/tables/ColorsTableV2/rows/add
And the following Body:
{
"index": null,
"values": @{body('Select')}
}
After running the flow, I get the following output.
With 16 rows of data, it took approx. 6 seconds to complete.
With 1000 rows of data, it took approx. 8 seconds to complete.
