Import data from an existing excel file from a folder

[WorkHard]

I have the following scenario:

I have an upstream software that spits out an updated report every day. The data comes out as an excel file and is saved in a folder on a file server. Each time the software spits out the report, it replaces the file with a new one (same name).

The report looks ugly, unformatted, and unorganized.

My plan is to import (create a connection) to this unorganized flat file data into a new excel file where I can organize it and make it look pretty. This new excel file will keep the data updated by periodically refreshing the import.

The end goal is to share this file with a few people in my org so that they can have an updated report daily without me manually doing this work.

• Is it possible to create a flow in Excel that will import data from a different excel file located in a folder?
• Is it possible to have it refresh on it's own without keeping the file open?
• Would this work in Excel for Web?

I just need a short description of what tools to use and what's possible. I'm new to flow.

Thank you!

[DamoBird365 · Accepted solution]

Hi @WorkHard 

You need to explore excel scripts.  You can create a bespoke function that you call from PowerAutomate and run on an excel file.  You can then manipulate the data using typescript.  If you don't know typescript, excel online has a recording tool. 

You could consider returning the data to Power Automate and doing as you please with it.  Create a PDF or a Word Document etc.  Send an email.

I've done a couple videos on Excel Scripts and Power Automate and wrote a brief article here.

Let me know how you get on.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[WorkHard]

@DamoBird365 , awesome.

So to summarize, I can use Power Automate to grab the unformatted excel file from the file server, do some magic in Power Automate, and instruct Power Automate to dump this into a sheet in the new excel file.
From there, use Excel Scripts to manipulate the look and feel of the report.

Did I get this right? 

Thank you!

[DamoBird365]

Hi @WorkHard 

With existing Excel Actions, you can really only grab data from files with tables.  WIth Excel Scripts (albeit in preview) you can define your own function to both retrieve data from Excel and save data to Excel, by ranges, cells, rows, columns.  You pretty much write a bespoke Action for Excel and could call 1,2,3 or more Excel scripts throughout your flow. 

Grab the data from excel, repurpose in a cloud flow, send an email, save some back to Excel.  With Excel Scripts, the functionality is down to your skills and patience.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[WorkHard]

This is my plan:

I'll try to see if there's a way to write an excel script that can access the contents from step 2.

[DamoBird365]

Hi @WorkHard 

Looks like a plan 😉.  You've got the logic.

Try copying and pasting the following as a demo, it should get you started.  It does search out the header of a row beginning with ID in order to define a range, but equally you could use the range from getUsedRange to define the data returned to your Cloud Flow.  console.log() is a useful expression as you can test within Excel and return back to the console any data that you want to spy on e.g. console.log(IDCell.getaddress()).

function main(workbook: ExcelScript.Workbook) {

  // Get the first worksheet 
  const selectedSheet = workbook.getFirstWorksheet();

  //get active range of WorkSheet
  let range = workbook.getActiveWorksheet().getUsedRange();

  // Get last used row of WorkSheet
  let lastrow = range.getRowCount();

  // Find first reference of ID in selectedSheet i.e. header row
  let IDCell = selectedSheet.getRange("A1").find("ID", { completeMatch: true, matchCase: true, searchDirection: ExcelScript.SearchDirection.forward });

  // Get the current active cell in the workbook.
  //and format current cell without Sheet1! reference
  let activeCell = IDCell.getAddress().replace("Sheet1!", "");

  //get table range  
  const TableRange = `${activeCell}:F${lastrow}`;

  // Create a table using the data range.
  let newTable = workbook.addTable(selectedSheet.getRange(TableRange), true);
  newTable.setName("NewTableInExcel");

  // Get the first (and only) table in the worksheet.
  let table = selectedSheet.getTables()[0];

  // Get the data from the table.
  let tableValues = table.getRange().getValues();

  //Return a response to the Cloud Flow
  return tableValues

}

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉