Flow: Copy Excel file if original file was updated & remove rows/columns

[Chris90]

I am currently upgrading my existing flow and struggeling with Office Scripts.

My flow does already:

Trigger: If a selected Sharepoint file (Excel file in my case) has changed

Flow: Copy the file to another sharepoint (Goal: Use it with other collaborators and change it) and replace the existing one

Now I want to improve it, as the file is 85mb big I want to remove all columns and rows which aren't necessary for my usecase. To do so I wrote an Office Script and extended the flow to a 3rd step:

3. Excel Online - Run Sharepoint Script

Selecting the newly copied file and the script.osts file. But I get an error message:

The dynamic operation request to API 'excelonlinebusiness' operation 'GetSingleScriptV2' failed with status code 'BadGateway'. Error response: { "error": { "code": 502, "source": "europe-001.azure-apim.net", "clientRequestId": "", "message": "BadGateway", "innerError": { "status": 502...

I saw that 502 errors might be caused by read-only files, but this isn't the case.

But as I'm new to Office Scripts and how to run them with Power Automate I don't know if this is the right way how to do it and in the docs I found on several places that Office Scripts can't be executed if they are stored on Sharepoint. Do you have some tipps how to progress?

[MichelleRanMSFT]

Hi @Chris90, sorry to hear you're running into this issue! Office Scripts that are stored in SharePoint can be run in Power Automate if you use the "run script from SharePoint library" action, which it looks like you're doing. Would you be able to share a screenshot of what parameters you're using for the run script action and where you're encountering this error? That will help us identify where the error could be coming from!

[Chris90]

Hi @MichelleRanMSFT yes of course:

Some seconds after adding all of the parameters I get the 502 error message and I can't save my flow. It is the 3rd step of the flow:

1. Trigger: If an element or file was changed

2. Action: Copy the file

3. Run script from SharePoint library

The copied file and the script are stored in the same folder.

[liwei1]

Thanks for reporting this issue. We're looking into it now. Once we have a workaround for you, I'll be back in touch.

[liwei1]

Thanks for reporting this issue. We're looking into it now. Once we have a workaround for you, I'll be back in touch.

[RR-Bobik]

I'm experiencing the same issue right now. I only have one action in the flow - Run script from SharePoint library.

[liwei1]

@Chris90 and @RR-Bobik , can you try again? There was tool update in past days. I just verified, both "Run Script" and "Run Script from Sharepoint library (preview)" worked fine.

[RR-Bobik]

@liwei1 still not working for me...

[liwei1]

hi, @RR-Bobik sorry for the inconvenience. Did you select script file (*.osts)? from a team shared folder or your onedrive folder? Thanks

[RR-Bobik]

it's from a team shared folder.

[liwei1]

Would you be able to share a screenshot of what parameters you're using for the run script action and where you're encountering this error? That will help us identify where the error could be coming from!

[Chris90]

@liwei1 I just made a ts file and renamed it to osts. Is there another way how to make an osts file?

[liwei1]

Please try to create a new osts file from excel Office Scripts in Excel - Office Scripts | Microsoft Learn

To add an osts in a sharedpoint, (1) create an osts file from UI (Excel -> Automate), then save it to a sharepoint folder (by default it saves to the default folder); (2) copy existing osts from the default folder to a sharepoint folder. The default folder is in OneDrive for Business, OneDrive, Documents/Office Scripts/yourscript.osts (see my screen shot). you can access the default folder from OneDrive as well.

To run the flow, you can select osts from the default folder, or any accessible sharepoint folders. Can you run the script in the default folder? and then copy the same script to the sharepoint folder, and run again? I would like to know the issue from the script or from the sharepoint folder? Thanks.

in default folder

from a sharepoint folder

[liwei1]

Office Scripts code is written in Typescript. But the .osts file is actually a JSON file and the script code itself is just a string property of that JSON. So simply renaming a .ts file to .osts won't work because it won't be in the expected JSON format.

[Chris90]

Thanks @liwei1 , I can confirm that in power automate I can define the process step without any issues. I just have a run time issue, but it might be caused by the amount of data. Is there a hint how to deal with that? Especially in case of remove rows based on filter results?

[MichelleRanMSFT]

Hi @Chris90, what specific runtime issue are you encountering?

If you're encountering a timeout issue, which can indeed be caused by processing a large amount of data, you should either optimize your script or split your workflow into multiple run script calls: Platform limits and requirements with Office Scripts - Office Scripts | Microsoft Learn

It would also help if you shared your script code!

[Chris90]

Hi @MichelleRanMSFT indeed a timout in regards of the platform limits if I run the script manually. I already tried to reduce the workload by repeating the steps for each filter criteria.

That's the script code:

function main(workbook: ExcelScript.Workbook) {
    let currentTable = workbook.getTable("Merge4_MAIN___TEXT___UOM___EAN___SUPPLY");

    currentTable.getAutoFilter().clearCriteria();
    
    let selectedSheet = workbook.getWorksheet('Material Master Data Report');
    selectedSheet.getRange("J:N")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("P:R")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("Q:R")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("R:U")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("T:T")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("U:W")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("W:X")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("X:Z")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("AE:AE")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("AL:AL")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("AT:AT")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("AZ:BH")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("BG:BS")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("BP:BU")
        .delete(ExcelScript.DeleteShiftDirection.left);
    selectedSheet.getRange("BQ:CF")
        .delete(ExcelScript.DeleteShiftDirection.left);

    currentTable.getColumnByName("Distribution Channel").getFilter().applyValuesFilter(["02"]);
    let visibleRows2 = currentTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows()
    let firstVisibleRow2 = visibleRows2[0].getRange().getRowIndex() + 1
    let lastVisibleRow2 = visibleRows2[visibleRows2.length - 1].getRange().getRowIndex() + 1
    selectedSheet.getRange(`${firstVisibleRow2}:${lastVisibleRow2}`).delete(ExcelScript.DeleteShiftDirection.up)
    currentTable.getColumnByName("Distribution Channel").getFilter().clear()
    currentTable.getColumnByName("Distribution Channel").getFilter().applyValuesFilter(["03"]);
    let visibleRows3 = currentTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows()
    let firstVisibleRow3 = visibleRows3[0].getRange().getRowIndex() + 1
    let lastVisibleRow3 = visibleRows3[visibleRows3.length - 1].getRange().getRowIndex() + 1
    selectedSheet.getRange(`${firstVisibleRow3}:${lastVisibleRow3}`).delete(ExcelScript.DeleteShiftDirection.up)
    currentTable.getColumnByName("Distribution Channel").getFilter().clear()
    currentTable.getColumnByName("Distribution Channel").getFilter().applyValuesFilter(["04"]);
    let visibleRows4 = currentTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows()
    let firstVisibleRow4 = visibleRows4[0].getRange().getRowIndex() + 1
    let lastVisibleRow4 = visibleRows4[visibleRows4.length - 1].getRange().getRowIndex() + 1
    selectedSheet.getRange(`${firstVisibleRow4}:${lastVisibleRow4}`).delete(ExcelScript.DeleteShiftDirection.up)
    currentTable.getColumnByName("Distribution Channel").getFilter().clear()
    currentTable.getColumnByName("Distribution Channel").getFilter().applyValuesFilter(["05"]);
    let visibleRows5 = currentTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows()
    let firstVisibleRow5 = visibleRows5[0].getRange().getRowIndex() + 1
    let lastVisibleRow5 = visibleRows5[visibleRows5.length - 1].getRange().getRowIndex() + 1
    selectedSheet.getRange(`${firstVisibleRow5}:${lastVisibleRow5}`).delete(ExcelScript.DeleteShiftDirection.up)
    currentTable.getColumnByName("Distribution Channel").getFilter().clear()
    currentTable.getColumnByName("Distribution Channel").getFilter().applyValuesFilter(["06"]);
    let visibleRows6 = currentTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows()
    let firstVisibleRow6 = visibleRows6[0].getRange().getRowIndex() + 1
    let lastVisibleRow6 = visibleRows6[visibleRows6.length - 1].getRange().getRowIndex() + 1
    selectedSheet.getRange(`${firstVisibleRow6}:${lastVisibleRow6}`).delete(ExcelScript.DeleteShiftDirection.up)
    currentTable.getColumnByName("Distribution Channel").getFilter().clear()
}

removing columns works fine, just removing filtered rows is causing the timeout.

[MichelleRanMSFT]

Given the repetitive logic for removing filtered rows, I think you can split your script into two scripts. First, a script to delete columns:

function main(workbook: ExcelScript.Workbook) {
	let currentTable = workbook.getTable("Merge4_MAIN___TEXT___UOM___EAN___SUPPLY");

	currentTable.getAutoFilter().clearCriteria();

	let selectedSheet = workbook.getWorksheet('Material Master Data Report');
	selectedSheet.getRange("J:N")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("P:R")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("Q:R")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("R:U")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("T:T")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("U:W")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("W:X")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("X:Z")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("AE:AE")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("AL:AL")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("AT:AT")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("AZ:BH")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("BG:BS")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("BP:BU")
		.delete(ExcelScript.DeleteShiftDirection.left);
	selectedSheet.getRange("BQ:CF")
		.delete(ExcelScript.DeleteShiftDirection.left);
}

Second, a script to apply a single filter and remove the filtered rows:

function main(workbook: ExcelScript.Workbook, filter: string) {
	let currentTable = workbook.getTable("Merge4_MAIN___TEXT___UOM___EAN___SUPPLY");
	let selectedSheet = workbook.getWorksheet('Material Master Data Report');

	currentTable.getColumnByName("Distribution Channel").getFilter().applyValuesFilter([filter]);
	let visibleRows = currentTable.getRangeBetweenHeaderAndTotal().getVisibleView().getRows();
	let firstVisibleRow = visibleRows[0].getRange().getRowIndex() + 1;
	let lastVisibleRow = visibleRows[visibleRows.length - 1].getRange().getRowIndex() + 1;
	selectedSheet.getRange(`${firstVisibleRow}:${lastVisibleRow}`).delete(ExcelScript.DeleteShiftDirection.up);
	currentTable.getColumnByName("Distribution Channel").getFilter().clear();
}

Then you can set up your flow like this (I'm using the OneDrive run script action, but it should work the same for the SharePoint action):

Basically, the idea here is to break up your workflow into multiple calls to the run script action, meaning that it will be less likely to time out.

Let me know if that helps or if you have any questions!

[Chris90]

Hi @MichelleRanMSFT thanks for the improvements. I still get a time out and the file size is 75mb. If I copy the data and save it as a new file it only has 315kb. Do you have some ideas how to reduce the file size automatically?

//edit: Solved it, there were some sheets hidden and I deleted them. Now everything is working. Thanks!

[Chris90]

don't know what happend but again the flow doesn't work. I get an error message:

{
  "message": "Graph Item not found, was it unshared or deleted?\r\nclientRequestId: add50282-bd52-4d9a-a1ac-b16215a0a2ec",
  "storageErrorCode": 5
}

But running the script manually works - I just added the part of removing all worksheets to just have the relevant one and to remove ~80mb file space?!

function main(workbook: ExcelScript.Workbook) {
  let wsArr = workbook.getWorksheets();
  let wsToKeep = workbook.getWorksheet("Material Master Data Report");
  let currentTable = workbook.getTable("Merge4_MAIN___TEXT___UOM___EAN___SUPPLY");

  currentTable.getAutoFilter().clearCriteria();

  //Loop through all worksheets in the worksheet collection
  for (let i = wsArr.length - 1; i >= 0; i--) {

    //Check if the item in the wsArr collection is equal to the wsToKeep variable
    //Only proceed if not equal to
    if (wsArr[i].getName() != wsToKeep.getName()) {

      //Make the worksheet hidden prior to deletion to remove error risk
      wsArr[i].setVisibility(ExcelScript.SheetVisibility.hidden)

      //Delete the worksheet    
      wsArr[i].delete();
    };

  }

  let selectedSheet = workbook.getWorksheet('Material Master Data Report');
  selectedSheet.getRange("J:N")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("P:R")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("Q:R")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("R:U")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("T:T")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("U:W")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("W:X")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("X:Z")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("AE:AE")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("AL:AL")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("AT:AT")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("AZ:BH")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("BG:BS")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("BP:BU")
    .delete(ExcelScript.DeleteShiftDirection.left);
  selectedSheet.getRange("BQ:CF")
    .delete(ExcelScript.DeleteShiftDirection.left);
}