Convert Excel data to table

[jf367]

Is there a way to convert Excel data to a table using Power Automate?

I get sent a daily report in Excel (always the same number of columns but amount of rows change), but because it isn't formatted in a table, PowerApps doesn't recognise it. I'm looking for a way to create an automatically triggered Automate to format the data into a table so that it can be used.

Thank you

[Yutao]

Hi @jf367 ,

With the new "Run script" action of the Excel Online (Business) connector, you should be able to run some Office Scripts against a workbook to convert a range of cells into a table.

Here is a sample script that can convert a specified range of cells into a table:

function main(workbook: ExcelScript.Workbook, sheetName: string, range: string, hasHeader: boolean) {
  workbook.getWorksheet(sheetName).addTable(range, hasHeader);
}

And here is a sample flow that consumes this script with an email trigger:

 Here are a few links that might be helpful to learn about using Office Scripts with Power Automate:

- Announcing support for Office Scripts in the Excel Online (Business) connector
- Run Office Scripts with Power Automate
- Call scripts from a manual Power Automate flow
- Pass data to scripts in an automatically-run Power Automate flow

Hope this helps!

[jf367]

Hi @Yutao 

Thank you for the below, the links and screenshot were really helpful, however, I've just found out that my company hasn't enabled Office Scripts in Excel (and aren't going to for some time). Is there any other way around it? If it's easier, I can get the data in CSV format and add it in to a table, but I'm not sure how to do that either?

Thank you again

[Yutao]

Hello @jf367 ,

Sorry actually I should have thought about this - I guess we can probably just use the "Create table" action of the "Excel Online (Business)" connector to convert an existing range into a table. Here is a sample flow:

Thoughts?

[Anonymous]

Hi 

Can this script be placed in another worksheet or does it have to be in the worksheet that the connector runs on?

[Yutao]

@Anonymous - the script is not associated with any workbook so as long as you can see it in the "Script" dropdown list, you should be able to run it against any other workbook that you have access to.

[nich3play3r]

Thanks for this information, @Yutao . I'm looking to create a table in an Excel sheet that's been created in the course of my flow; it will have a static number of columns but variable number of rows. How would I modify this code:

function main(workbook: ExcelScript.Workbook, sheetName: string, range: string, hasHeader: boolean) {
  workbook.getWorksheet(sheetName).addTable(range, hasHeader);
}

Would I set the range variable using the 

getUsedRange()

function?

Thanks! 

[222lovebirds]

Hello,

Does the range have to be determined in the flow? Can it be automatically detected somehow?
I have an excel data sheet sent to me daily and the range changes very frequently.

[nich3play3r]

@222lovebirds you can run that ExcelScript at any point in your flow as code to get the shape of your Excel file in its current state.

[Zap174]

Hello! Did you find an answer to this? I am facing the same issue now in which I need to create a table for a file of dynamically changing numbers of rows.

[nich3play3r]

@Zap174 here's the code for the Office Script for Excel script I use in my flow to determine how many non-blank rows are in the output created in an earlier step in the flow. I'm also including the script for a code that creates a table out of the used range in an Excel sheet.... HTH.

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Get the range containing all the cells with data or formatting.
	let usedRange = selectedSheet.getUsedRange();
	// Auto fit the rows of range all cells on selectedSheet
	selectedSheet.getRange().getFormat().autofitRows();
	// Auto fit the columns of range all cells on selectedSheet
	selectedSheet.getRange().getFormat().autofitColumns();
	let newTable = selectedSheet.addTable(usedRange, true);
	// Give the table a name for easy reference in other scripts.
	newTable.setName("DGR");
}
	

/*
This script counts the empty rows on a given worksheet, and can return them to a Power Automate Flow.
*/

function main(workbook: ExcelScript.Workbook, sheetName:string = 'Top Giver Notifications'): number {
  // Get the worksheet named "Top Giver Notifications".
  const sheet = workbook.getWorksheet(sheetName);

  // Get the entire data range.
  const range = sheet.getRange("A2:J2");

  // If the used range is empty, end the script.
  if (!range) {
    console.log(`No data on this sheet.`);
    return;
  }

  // Log the address of the used range.
  console.log(`Used range for the worksheet: ${range.getAddress()}`);

  // Look through the values in the range for blank rows.
  const values = range.getValues();
  let emptyRows = 0;
  for (let row of values) {
    let emptyRow = true;

    // Look at every cell in the row for one with a value.
    for (let cell of row) {
      if (cell.toString().length > 0) {
        emptyRow = false
      }
    }

    // If no cell had a value, the row is empty.
    if (emptyRow) {
      emptyRows++;
    }
  }

  // Log the number of empty rows.
  console.log(`Total empty rows: ${emptyRows}`);

  // Return the number of empty rows for use in a Power Automate flow.
  return emptyRows;
}

[Zap174]

@nich3play3r Thank you so much! The script to turn a range into a table is just what I was looking for. I really appreciate your help!

[xghostchc]

Hi.
Any way to use the range but dynamically? The script create the table but without the data already in my sheet. I can have different ranges for each one. 

[arthurgand]

Hello everyone,

The Office 365 Basic version doesn't enable the correct scripting for Excel?