Rename excel worksheet from script

[guyinkalamazoo]

I know this is a very stupid question, but I need something in power automate that will rename a worksheet after a user puts the file into a SharePoint folder.  Everything is working with the flow.  The file that is deposited in the location is copied, moved and renamed.  The worksheets are always the same name, but for automation purposed with Power BI need to be consistent and case-sensitive.  I want to rename the worksheets using power automate and everything points to a script.  However, does the script HAVE to be in the file that it is processing?  Or is there another way to do this?

Thanks

[DamoBird365 · Accepted solution]

Hi @guyinkalamazoo 

You can use office scripts.  https://docs.microsoft.com/en-us/office/dev/scripts/overview/excel

 Script:

function main(workbook: ExcelScript.Workbook,

  NewSheetName: string)

{

  let sheet1 = workbook.getWorksheet("Sheet1");

  sheet1.setName(NewSheetName);

}

This will create a bespoke action that you call with a variable for the new sheet name (assuming sheet1 is the default).

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 😉

[guyinkalamazoo]

Does the script itself have to be in the same excel file?  This is my confusion.  This is a file that is uploaded every morning and it is one that is extracted out of data from SAP.  So the file is NOT going to have a script every time it is uploaded.  How does a script from one file reference the incoming file?

[DamoBird365]

Hi @guyinkalamazoo 

The script is written in excel, saved in OneDrive and called as a bespoke action in PowerAutomate. It will run on the file that you specify either manually or dynamically. I’ve a fair few demos on my YT. You don’t need to know programming as office scripts has a recording tool but for have supplied you a working script to try. Honestly it’s not as daunting as it looks.

Damien

[guyinkalamazoo]

Thanks for the information @DamoBird365 .  I subbed to your YouTube also

[DamoBird365]

Let me know how you get on.

Damien

[mechilletorres]

This is nice @DamoBird365  🙂

Can you help share the script also on how to copy a worksheet to a new worksheet? thanks

[Prateep1991]

Hi DamoBird365,

I think I'm missing something here. Firstly when I'm adding the script I get this error.

Also I'm not sure how it will create a bespoke action where I can pass the new sheet name in arguments.
I do not see the additional NewSheetName argument when I'm selecting this script from the Run Script action in Power Automate. Could you please let me know where I'm going wrong? It will be really helpful. Thanks in advance!

[pg2020]

hey there.

This works 🙂

Please can tell me anyone how i can get variable : "Filename without extension"
This is included in the onedrive for business connector, but not in the Sharepoint Connector 😞

So i can paste the variable Filename without extension as name for the new spreadsheet 🙂

[dferrar]

I hope this helps!

/**
 * This script logs the name of the workbook without the ".xlsx" extension.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the workbook's name.
  let name = workbook.getName();

  // Remove the file extension.
  name = name.substring(0, name.lastIndexOf(".xlsx"));

  // Display the name in the console.
  console.log(name);
}