How to: Save Excel email attachment to SharePoint, copy the file to subfolders and remove rows

[powerappdummy]

Hi all, first time poster here.

The aim of my flow is to extract the Excel attachment when I receive an email from a sender, this contains feedback for multiple teams. I want to save the spread sheet in a management folder, but then I'd like to share the information with sub-teams (but crucially remove data that's not relevant).

So far, my flow is doing the following:

1. finding the email in my inbox
2. searching for the Excel attachment
3. creating a file in SharePoint (management folder)
4. and also creating a file in the sub-teams.

How do I expand the use of this flow so that actions 3 or 4, create copies of the master file, then creates a table, searches the table, and deletes irrelevant rows?

Thanks

[rzaneti]

Hi @powerappdummy ,

The Excel connector actions for Power Automate does not provide a lot of freedom to manipulate Spreadsheets: it is basically limited to a table management.

However, you have the option to manipulate your file with the Excel Scripts, which in simple terms is like a "VBA for Excel Web". As it is based in JavaScript/TypeScript, you will need some knowledge about it to use, but there is a lot of information online and, depending on how the data that you need to transform is distributed, it may be not so complex (and of course, you can reach the community in case of any trouble).

After create an Excel Script, you will be able to run it from Power Automate by calling the action "Run Script", from Excel connector. It is a very powerful strategy and it will give you a lot of freedom to manipulate your file.

You can check for some details here: https://support.microsoft.com/en-us/office/introduction-to-office-scripts-in-excel-9fbe283d-adb8-4f1...

If you need any help about how to build a script for your file, please share a sample of your the document that you receive by email (replacing sensitive data for fake info) and I will do my best to help you!

[powerappdummy]

Hi @rzaneti 

I'm still working on this and I'm struggling with the Excel Script - hoping you can help. 

I've attached my spreadsheet showing the Column Headers. 

After lots of googling, I've come up with the following script in Excel to create and filter a table. Ideally I would like the script to delete the rows that aren't relevant but for the moment, I'm happy to just get the filtering working.

When calling this script using Power Automate, it runs with no errors (but doesn't do anything to the Excel file), and when running manually via Excel it gives me a "

Line 8: Worksheet addTable: You cannot perform the requested operation.

" error.

After much googling, the answers seem to say 'there's already a table in your worksheet', but there isn't in this instance, it's just raw xlsx data.

Any advice?

function main(workbook: ExcelScript.Workbook)
{
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Create a table with the used cells.
  let usedRange = selectedSheet.getUsedRange();
  let newTable = selectedSheet.addTable(usedRange, true);

  // Sort the table using the first column.
  newTable.getSort().apply([{ key: 5, ascending: true }]);
}

[rzaneti]

Hi @powerappdummy ,

Yes, this error can happens if you already have a table in your file at the same range. Your table may have a name, so you can access it directly, without necessarily add a new one. 

To access your existing table, you can replace your 'let new table..' row for this one:

let newTable = workbook.getTable([TABLE_NAME]);

Inside the parenthesis, you can put the table name, as it appears in your 'Table Design' tab in Excel. If you want to cover the both scenarios (use a table if it exists, and create it if it not exists), you can use the following block of code:

if(workbook.getTable([TABLE_NAME])!== undefined){
let newTable = workbook.getTable([TABLE_NAME])
} else{
let newTable = selectedSheet.addTable(usedRange, true).addName([TABLE_NAME]) //with this additional method, you will add a name for your table when it is created :)
}

Also, if you need to delete all records from your table before use it, you can use the following block of code:

try {
newTable.getRangeBetweenHeaderAndTotal().delete(ExcelScript.DeleteShiftDirection.up)
} catch{
null
}

In this block, you are basically capturing all rows from your table (except for the header and total rows) and deleting it. We are encapsulating it inside a try/catch because sometimes (at least in my use cases) you already have an empty table, so if you just delete it without insert into the "try" it will return an error. 

Let me know if it works for you, and if you find any additional issues with this task, do not hesitate to reach me. I know that these JavaScript commands may be difficult when we start to work with, and I will be happy to help.