Get Excel Data into Power Automate That Is Not Formatted as a Table

8 min readFeb 15, 2023

Introduction

Excel Online connectors, like in Power Automate or Logic Apps, typically require data to be formatted as an Excel Table to be accessed.

This is Excel data that is not formatted as a table…

Sample Excel data not formatted as a table

…and this is Excel data that is formatted as a table:

Same sample data as previous image but formatted as a table this time

The typical method to create a table is select the data you want in the table, and select Insert > Table:

How to insert a table in Excel from existing data

The problem with this reality is many users don’t format their data as an Excel Table, even if their data is in rows and columns like a table. I wanted the ability to get this unformatted data into Power Automate (or Logic Apps) without requiring every user to format their data as an Excel Table.

This example will show how to use Office Scripts to convert tabular data in Excel into an Excel Table.

Note: This article assumes that the Excel file containing the data we need is located on OneDrive or SharePoint.

Important note about the example in this article:

I initially thought that Power Query would be the perfect solution for this. I could just create my own Excel workbook, use Power Query to connect to the Excel workbook on SharePoint or OneDrive that contained the data I needed, and that connection would create the Excel Table in my workbook. Then I would be able to use my newly created workbook for my Power Automate flow. I could just refresh the connection regularly to get the latest data, with no interruptions to the user.

The problem I discovered is that Power Query for Excel Online doesn’t currently support refreshes for all data sources like the desktop version of Excel does. When trying to refresh the connection for SharePoint in Excel Online, I got the following error:

Since Power Query wasn’t supported for my use-case, I began looking for a different solution that led me to the example I will show in this article.

In full disclosure, I will admit that this solution is a bit “hacky” and more of a work-around than a true solution, but I am still showing it as something that could potentially be useful to you.

Step 1: Create Sample Data

Let’s say we have an Excel Online file on OneDrive or SharePoint with data like this:

Sample data that isn’t formatted as a table on a worksheet named Data

This data is pretty basic — just two columns of numbers and a third column that sums their values. Also note that I’ve named the worksheet Data in this example.

In its current form, we wouldn’t be able to bring this data into a Power Automate flow or Logic App through the Excel Online connector since it’s not formatted as an Excel Table.

Let’s take a look at how we can use Office Scripts to get this data into a consumable format for Power Automate.

Step 2: Create Script

With our sample data ready, let’s add a script to create an Excel Table from it.

Open the Excel file containing the sample data from the previous step, and navigate to Automate > New Script, and create a script with the following:

function main(workbook: ExcelScript.Workbook) {
  // Step 1: 
  // Set constants: sheet names and table name
  // data_sheet_name is the name of sheet that contains the data we want
  // copy_sheet_name is the name of sheet that we will use for copying data to
  // table_name is the name of table that we will create with our copied data
  // first_cell is the address of the first cell containing our data, including headers
  const data_sheet_name: string = "Data"
  const copy_sheet_name: string = "SuperSecretNameForCopySheet"
  const table_name: string = "TableForPowerAutomate"

  // Step 2: 
  // Check if the sheet named copy_sheet_name variable exists already
  // If it does exist, delete and re-add it as a blank sheet
  // If it doesn't exist, add it as a blank sheet.
  if (typeof workbook.getWorksheet(copy_sheet_name) === "undefined") {
    // Add copy_sheet_name worksheet since it doesn't exist
    workbook
      .addWorksheet(copy_sheet_name)
  }
  else {
    // Change copy_sheet_name worksheet to hidden (from veryHidden) 
    // so it can be deleted
    workbook
      .getWorksheet(copy_sheet_name)
      .setVisibility(ExcelScript.SheetVisibility.hidden)

    // Delete copy_sheet_name worksheet since it does exist
    workbook
      .getWorksheet(copy_sheet_name)
      .delete()

    // Re-create copy_sheet_name worksheet as a blank sheet
    workbook
      .addWorksheet(copy_sheet_name)
  }

  // Step 3: 
  // Set sheet objects
  let data_sheet =
    workbook
      .getWorksheet(data_sheet_name)
  
  let copy_sheet =
    workbook
      .getWorksheet(copy_sheet_name)

  // Step 4: 
  // Hide the copy_sheet (as veryHidden) to keep user from seeing it
  // or being able to unhide it
  copy_sheet
    .setVisibility(ExcelScript.SheetVisibility.veryHidden)

  // Step 5: 
  // Get full used range of data_sheet (the data to copy over)
  let data_range = 
    data_sheet
      .getUsedRange()

  // Step 6: 
  // Copy the data_range to copy_sheet
  let copy_to_address = copy_sheet.getRange("A1")
  
  copy_to_address.copyFrom(data_range, ExcelScript.RangeCopyType.all)

  // Step 7: 
  // Get the used range in copy_sheet after data has been copied over
  let copy_range = copy_sheet.getUsedRange()

  // Step 8: 
  // Turned used range in copy_sheet into a table
  workbook.addTable(copy_range, true).setName(table_name)
}

There are comments in the script explaining each step, but here is some further clarification behind what this script is doing.

Note: When I reference a variable name in italics, I am meaning the value that is assigned to that variable.

Step 1: Set the constant values we will use later. We set the name of the sheet that has the data we want (data_sheet_name), the name of the sheet we will copy data to (copy_sheet_name), and the name of the Excel Table we will create (table_name).
Step 2: We are checking to see if there is already a sheet named copy_sheet_name. If it does not exist, we create a new blank sheet named copy_sheet_name. If that sheet does exist, we set it to hidden, delete it, and create a new blank sheet named copy_sheet_name. We change the sheet to hidden because this script sets the copy_sheet to veryHidden in Step 4 to keep the user from ever seeing it. But a sheet can’t be deleted if it’s veryHidden, so we change it to hidden to allow for deletion and re-creation.
(Note: You could perhaps just delete the table, and not the whole worksheet. I decided on deleting the whole worksheet so that I would be starting with a fresh sheet each time. Now when I copy the source data over to the new sheet, I’m more confident there wouldn’t be any problems with data types for the columns).
Step 3: Set the sheet objects we will use in our script. One is the sheet we are copying from and the other is the sheet we are copying to.
Step 4: Set our copy_sheet to veryHidden, so that the creator and/or user of this workbook never sees this sheet. They also wouldn’t be able to unhide it in the workbook unless they write their own Office Script to do that.
Step 5: Get the used range of our data that we want to copy. This would get all data that is populated on data_sheet.
Step 6: Copy the data over from data_sheet to copy_sheet, with cell A1 being the starting cell for our pasted values.
Step 7: Get the used range of the data we have copied over. This would get all data that is populated on the copy_sheet.
Step 8: Turn the used range on copy_sheet into a table named table_name.

Rename your script to something more descriptive, and select Save script:

Screenshot of saved Office Script that we created

Go ahead and Run your script as well, to build the table for the first time. After the script completes, you should notice that you won’t be able to see the copy_sheet and the table_name created in it, which is expected behavior based on our script logic.

Make sure that the Excel file containing the sample data is saved to OneDrive or SharePoint.

Step 3: Create your flow

Create a new Power Automate flow (or Logic App) and select whatever trigger you want for your flow.

Then, using the appropriate connector (most likely Excel Online for Business), add a Run script activity:

Make sure that you choose the path for the Excel file containing the data where the script should be run, and choose your Script name from the drop-down box. This would be whatever you named your script from the previous step.

Next, using the appropriate connector (most likely Excel Online for Business), add a List present rows in a table activity. There is one important note here: make sure to choose Enter custom value for the Table property, and manually enter the table name. Do not choose your table name from the drop down. If you choose the table from the drop down, it will find the table by its internal ID. Since we are dropping and recreating the table every time, this ID would no longer be valid after the first run.