cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chris90
Helper I
Helper I

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions

It looks like you want to run the script on the copied workbook. Have you manually typed in the file path? If so, that won't work: you need to use the file id returned from the copy file action. See Solved: Re: Run Excel script error when passing dynamic fi... - Power Platform Community (microsoft.... and Excel Online (Business) - Connectors | Microsoft Learn for more information.

View solution in original post

29 REPLIES 29

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
Helper I
Helper I

Hi @MichelleRanMSFT yes of course:

 

Chris90_0-1685690574863.png

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
Employee
Employee

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

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
Regular Visitor

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

@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.

 

liwei1_0-1686153720944.png

 

RR-Bobik
Regular Visitor

@liwei1 still not working for me...

RRBobik_0-1686218814349.png

 

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

it's from a team shared folder.

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
Helper I
Helper I

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

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

liwei1_1-1686584402607.png

from a sharepoint folder

liwei1_3-1686584562056.png

 

 

 

 

 

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
Helper I
Helper I

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?

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
Helper I
Helper I

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.

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):

MichelleRanMSFT_0-1686768884018.png

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
Helper I
Helper I

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
Helper I
Helper I

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);
}

 

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!    

Welcome to the Power Automate Community

You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun.   Now that you are a member, you can enjoy the following resources:   Welcome to the Community   News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate.  Be sure to subscribe to this board and not miss an announcement.   Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows.  You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook.  Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today.   Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome.   Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback  board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together.     Power Automate Community Team

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Users online (4,230)