Add rows to Excel sheets without tables in Power Automate

Recently I came across a requirement to be able to insert rows of data into an Excel sheet, without being able to use tables functionality. It's not completely common that we wouldn't be able to use tables, but if there's another tool relying on our spreadsheet not having a table in place, or if there is perhaps another process being applied to our spreadsheet which results in a table being removed, then we need another solution to being able to add data to Excel, where a table is not present.

In this blog post, we'll look at how we can combine low-code and a small amount of TypeScript in Office Scripts to achieve this requirement.

Copying from one workbook to another

We're going to need some data for Power Automate to insert into our Excel sheet, so let's take this use case for example, copying rows from one workbook with a table to another without. This isn't the rule of the use case, we simply need to take some data from somewhere to be able to insert into our Excel workbook that doesn't have a table.

Let's take a look at our flow. As you can see I have a manual trigger, adjust this to your requirements. Then I'm collecting my data from one Excel workbook. You can take data from anywhere. We will be working with JSON here. Next I'm refactoring my JSON into a format appropriate for my script and for my target file. Then before I run my script I'm simply going to run the outputs of my select action through a compose action to be able to access the whole array contents more easily, otherwise without writing a formula / expression I will end up with an apply-to-each loop.

Here I'm refactoring my JSON array.

Then I'm using a compose action in my flow so that I don't have to write an expression to access the dynamic content I need from the select. You can write an expression if you prefer to reduce the number of actions in the flow.

Simply use body('Select')

Office Scripts

Now I can work on the part to take the data into my target workbook and apply it into my sheet. To do this, I will create a new Office Script in Excel, and I will use the following TypeScript code.

function main(workbook: ExcelScript.Workbook, jsonData: string) {

  // Declare an interface for the record format
  interface Record {
    FirstName: string;
    Surname: number;
    Email: string;
}
    // Parse the JSON data 
    const records: Record[] = JSON.parse(jsonData);
    
    // Convert the records to a 2D array
    const data: (string | number)[][] = records.map(record => [record.FirstName, record.Surname, record.Email]);

    // Get the active worksheet
    let sheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();

    // Find the last used row
    let lastRow: number = sheet.getUsedRange().getLastRow().getRowIndex();

    // Append each record to the sheet
    data.forEach((rowData, index) => {
        let row: number = lastRow + index + 1;
        rowData.forEach((value, colIndex) => {
            sheet.getCell(row, colIndex).setValue(value);
        });
    });
}

I'll save this Office Script in my target Excel workbook and then I'll head back to Power Automate.

Now in Power Automate, I can add and configure the step to run my Office Script. You can see that the parameter I created in the function / my code has been created in my action for me to populate. Here I will provide the JSON data I want to insert into my spreadsheet.

Let's test it out

So, only one last thing to do... let's test this out with 2000 or so records. In one spreadsheet I have a range of data Copilot generated for me and I'm going to run my flow to copy this data to the next spreadsheet without any need for tables in the target spreadsheet.

Here is my source file currently:

And here is my target file currently:

Next, I am going to execute my flow:

Things ran successfully! Cool, now let's check my target file which didn't have a table in it, hence we used a script to insert the data.

And there you have it! My target file now has data in it, without the need for a table. So we're now able to work with Power Automate to move data around without needing to add tables to workbooks.