Split a Workbook into Multiple Worksheets

It is possible to quickly split a workbook into multiple worksheets based on a key column using Office Scripts in Power Automate. For instance, if your Excel sheet contains data relating to sales and for each of those sales, the sales manager responsible is in a column, you can automatically detect the unique names, create sheets for each of them, and populate those sheets with the sales relevant to that manager. Furthermore, if you would rather unique workbooks for each of those distinct names, I’ve got a solution for that too and you could use the final script just to bulk load data into Excel efficiently, without using any add a row actions.

If you are new to Office Scripts, I would recommend you take a look at the Microsoft Documentation. Key things to note are that data sent to and from a script in Power Automate is limited to 5MB and the range of cells is limited to 5 million. A user can make 1600 api calls to Office Scripts per day. If you are familiar with the out of the box add a row to a table action, you will be aware how long it can take to load data into Excel. Office Scripts can perform bulk data loads, formatting, ordering, all from a single action. I’ve a few Office Scripts articles on my blog too and even more on my Office Scripts – YouTube playlist!

Sample Excel Workbooks

I headed over to Kaggle and picked up a couple datasets, the first had 400+ rows for the Top 10 Highest Grossing Films (1975-2018) and another with 100,000+ for Top 48 automakers daily stock prices 2010-2022. The former had a column for the main genre, of which there were 16 distinct values, the latter 48 (!) car manufacturer codes. Using this data I was able to test the reliability of my proof of concept.

The scripts

I have three scripts for this solution, albeit some of my inspiration and code came from Microsofts own sample solution for Combine workbooks into a single workbook. Running these from Power Automate is really straight forward using the single action, an example of which can be seen below:

The three scripts are as follows:

Split Workbook into Multiple Worksheets Based on Key Column

This has three input parameters as seen above, the key column name you want to split the workbook on, a table name (if one exists and / or to create if it doesn’t) and a default sheet name. The script has the ability to create a table commented out, a clever feature of Office Scripts if you are going to be receiving an excel file without a table created. Then it will retrieve the unique names from the column defined, loop through these creating a new sheet and filtering the data on the main sheet before copying the data into the new sheet for each loop. The only limitation I have seen here is that sheet names must be 30 characters or less. This script will run in a matter of seconds and result in an updated workbook with multiple sheets of data based on the unique key column name data. 17 sheets for the blockbuster example and 49 for the car manufacturer, remembering that the original worksheet will remain untouched.

function main(workbook: ExcelScript.Workbook,
  KeyColumn: string = "ManagerName",  //Specify Key Column Name to Filter On
  MainTable: string = "Table1",  //Either existing OR new table name
  SheetName: string = "Sheet1"  //Default sheet name
) {

// Get the worksheet by name
const selectedSheet = workbook.getWorksheet(SheetName);

// Alternatively, get the first worksheet (uncomment below and comment out above)
// const selectedSheet = workbook.getFirstWorksheet();

// Create a table using the data range.
let newTable = workbook.addTable(selectedSheet.getUsedRange(), true); //***Comment out if new table not required
  newTable.setName(MainTable); //***Comment out if new table not required

//Define Table Name
  const TableName = workbook.getTable(MainTable);
  
//Get all values for key column
  const keyColumnValues: string[] = TableName.getColumnByName(KeyColumn).getRangeBetweenHeaderAndTotal().getValues().map(value => value[0] as string);

 // Filter out repeated keys. This call to `filter` only returns the first instance of every unique element in the array.
  const uniqueKeys = keyColumnValues.filter((value, index, array) => array.indexOf(value) === index);
  console.log(uniqueKeys);

  // Filter the table to show only rows corresponding to each key and then for each filter
  uniqueKeys.forEach((key: string) => {
    TableName.getColumnByName(KeyColumn).getFilter()
      .applyValuesFilter([key]);

    // Get the visible view when a single filter is active.
    const rangeView = TableName.getRange().getVisibleView();
  
    // Create a new sheet
    let sheet = workbook.addWorksheet(`${key}`);
    
    // Set the range of data from the filter
    let range = sheet.getRangeByIndexes(0, 0, rangeView.getRowCount(), rangeView.getColumnCount());
    
    //Load Data into new Sheet based on selected range
    range.setValues(rangeView.getValues());

  });

//Clear Filter
  TableName.getColumnByName(KeyColumn).getFilter().clear();

}

Split Workbook into Data Arrays based on Key Column

This starts off very similar to the script that creates multiple sheets, and again has the option to create a table if the sheet does not contain one. The main difference here is that rather than creating sheets, the filtered data is passed to an array as defined by worksheetInformation and is returned back to Power Automate via the Output. This allows the final script to be called with the output as input, and from this we can create multiple unique Workbooks with the unique data in the main default worksheet, sheet1.

function main(workbook: ExcelScript.Workbook,
  KeyColumn: string = "ManagerName",  //Specify Key Column Name to Filter On
  MainTable: string = "Table1",  //Either existing OR new table name
  SheetName: string = "Sheet1"  //Default sheet name
) {

  /*Commented out if new table not required
  // Get the worksheet by name
  const selectedSheet = workbook.getWorksheet(SheetName);
  
  // Alternatively, get the first worksheet (uncomment below and comment out above)
  // const selectedSheet = workbook.getFirstWorksheet();

  // Create a table using the data range.
  let newTable = workbook.addTable(selectedSheet.getUsedRange(), true); 
  newTable.setName(MainTable); 
  */

  // Create an object to return the data for each workbook.
  let worksheetInformation: WorksheetData[] = [];

  //Define Table Name
  const TableName = workbook.getTable(MainTable);

  //Get all values for key column
  const keyColumnValues: string[] = TableName.getColumnByName(KeyColumn).getRangeBetweenHeaderAndTotal().getValues().map(value => value[0] as string);

  // Filter out repeated keys. This call to `filter` only returns the first instance of every unique element in the array.
  const uniqueKeys = keyColumnValues.filter((value, index, array) => array.indexOf(value) === index);
  console.log(uniqueKeys);

  // Filter the table to show only rows corresponding to each key and then for each filter
  uniqueKeys.forEach((key: string) => {
    TableName.getColumnByName(KeyColumn).getFilter().applyValuesFilter([`${key}`]);

    // Get the visible view when a single filter is active.
    const rangeView = TableName.getRange().getVisibleView();
    // Get values from filter
    let values = rangeView.getValues()

    worksheetInformation.push({
      name: `${key}`,
      data: values as string[][]
    });

  });

  //Clear Filter
  TableName.getColumnByName(KeyColumn).getFilter().clear();

return worksheetInformation

}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

Create Worksheet based on Data Array

Using the output from the previous script, we can supply an array of worksheet information and with this, determine the number of columns and rows before adding to the default sheet of a new Excel File. We then perform some nice to have formatting, like autofitting columns and create a table, so that the data is now queryable via the native Excel actions. Albeit why would you use list rows when you have Office Scripts?

function main(workbook: ExcelScript.Workbook, 
  MainTable: string = "Table1",  //new table name
  worksheetInformation: WorksheetData) {
  
  // Get default worksheet Sheet1
  let sheet = workbook.getWorksheet(`Sheet1`);

  // Create range based on the size of data 
  let range = sheet.getRangeByIndexes(0, 0, worksheetInformation.data.length, worksheetInformation.data[0].length);

  //Populate sheet with data
  range.setValues(worksheetInformation.data)
  
  //Autofit column width
  range.getFormat().autofitColumns();

  //Create New Table
  let newTable = workbook.addTable(range, true);
  newTable.setName(MainTable); 
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  data: string[][];
}

A sample Flow

The soltuion to split the workbook into multiple worksheets is all done in one action. Call the first script on the file of your choice and set the key column name, table name and sheet name. If you are looking to perform a split into multiple files, you need to combine the second and third script on an apply to each. I use a compose with the file content of an empty Excel for this and can then create a new file easily. Split the workbook into an array of data sets and then loop through them all to create new files and populate with data using the third script. Note that I inserted a delay of 3 seconds between the create file and run script. Excel can be a bit touchy when it comes to running scripts. Generally scripts will run but if you try to run them in parallel or access the same file continuously, you might get timeouts and retries.

Bulk load data to Excel Using Office Scripts

As another nice bonus, this final script can be used to bulk load data to Excel in a single action, as long as the data size is not greater than the 5MB limit. Potential data sources could be an API, Dataverse or Microsoft List datasets. Compose an array with an array of headers, followed by an array of data per row and you can pass it straight into a new Excel file using the technique above and the third script. A sample array from a Microsoft List can be seen below:

If you were looking for a method to create such an array structure, don’t go jumping on an apply to each loop, try something a bit more efficient like a compose (for the header), a select, and a final compose and a union as follows:

You can then pass the array to a newly created Excel File and this data will be populated in the new file almost immediately! You can see a similar method I have demonstrated here.

Office Scripts are a great method of extending Excel integration with Power Automate. The recording tool is a great way of learning what is possible and building sample code and the examples from the Microsoft Team are also incredibly useful for a newbie. I hope that the above article has provided you with enough information to try this yourself. Please let me know below how this has helped you and don’t forget to watch my video demo too.