Power Platform Community Forum Thread Details

I'm currently working on a Power Automate flow that automates the process of appending data from an Excel attachment (sent from my ERP system) to an existing Excel file stored in OneDrive for Business.

Here's a breakdown of how the flow works:

Trigger: The flow is triggered when I receive an email with the Excel file as an attachment (let's call this the Source file).
Save Attachment: The attachment is automatically saved in OneDrive for Business.

Step 1 - Create Table in Source: The first Office Script converts the data in the Source file into a table if it isn't already structured as one.

function main(workbook: ExcelScript.Workbook) {
    // Get the active worksheet
    let selectedSheet = workbook.getActiveWorksheet();

    // Get the used range in the sheet
    let usedRange = selectedSheet.getUsedRange();

    // Add a new table at the used range
    let newTable = selectedSheet.addTable(usedRange, true);
}

Step 2 - Add Export Date: The second Office Script adds a new column called Export_Date to the far right of the table in the Source file. This column is populated with the date the file was processed.

function main(workbook: ExcelScript.Workbook) {
  // Get the file name of the active workbook
  let fileName = workbook.getName(); // This will return the file name with the extension, e.g., "WPs_Updates__13092024.xlsx"

  // Extract the date part from the file name (assuming the last 8 characters before .xlsx)
  let dateFromFileName = fileName.slice(fileName.indexOf('__') + 2, fileName.indexOf('.xlsx'));

  // Format the date as DD/MM/YYYY
  let exportDate = `${dateFromFileName.slice(0, 2)}/${dateFromFileName.slice(2, 4)}/${dateFromFileName.slice(4, 8)}`;

  // Get the active worksheet
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the used range in the sheet
  let usedRange = selectedSheet.getUsedRange();

  // Get the total number of columns in the used range
  let lastColumn = usedRange.getColumnCount();

  // Calculate the column where "Export_Date" will be written (one column to the right of the last column)
  let exportDateColumn = lastColumn + 1;

  // Set the header "Export_Date" in the first row of the export date column (Row 1, Column to the right)
  let exportDateHeaderCell = selectedSheet.getCell(0, exportDateColumn - 1); // Row 1 is index 0, columns are 0-indexed.
  exportDateHeaderCell.setValue("Export_Date");

  // Get the total number of rows in the used range
  let rowCount = usedRange.getRowCount();

  // Populate the "Export_Date" column with the extracted date for each row
  for (let i = 1; i < rowCount; i++) { // Starting from the second row (index 1)
    let exportDateCell = selectedSheet.getCell(i, exportDateColumn - 1); // Row i, same column
    exportDateCell.setValue(exportDate);
  }
}

Step 3 - Capture Data: The third Office Script extracts all the data from the Source table as a String[][] (2D array of strings), making it ready to be appended to the target file.

function main(workbook: ExcelScript.Workbook) {
  // Get the active worksheet
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the table named "Table1"
  let sourceTable = selectedSheet.getTable("Table1");

  // Get the values of the entire table (excluding headers)
  let tableData = sourceTable.getRangeBetweenHeaderAndTotal().getValues();

  // Return the table data as output
  return tableData;  // This returns a 2D array (array of arrays)
}

Step 4 - Append to Target: The final Office Script appends this data in a for each loop from the Source to an existing Excel file in OneDrive (let's call this the Target file). The Target table contains the same column headers as the Source, ensuring consistency. The data is stacked vertically, row by row.

function main(workbook: ExcelScript.Workbook, data: string[][]) {
  // Get the active worksheet
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the destination table named "Table1"
  let targetTable = selectedSheet.getTable("Table1");

  // Get the number of columns in the target table by checking the header row range
  let targetColumnCount: number = targetTable.getHeaderRowRange().getColumnCount();

  // Append each row of data to the target table, adjusting the number of columns if needed
  data.forEach(row => {
    // Trim the row to match the target column count if it has more columns
    let adjustedRow = row.slice(0, targetColumnCount);

    // Append the row to the table
    targetTable.addRow(-1, adjustedRow); // -1 adds the row at the end of the table
  });
}

Issue: Flow Timeout and Performance Bottleneck

However, I am facing issues regarding the time it takes for the flow to finish, especially with larger files. Sometimes, the flow times out around the 17-minute mark, causing delays and incomplete executions. This is especially problematic when processing bigger files with a significant number of rows.