modify excel file (business) header names with a flow

[freddy65]

Hi All,

I have an excel file that gets uploaded automatically and saved to a SharePoint site (teams). The column names will include customer data and and data for each month of the financial Year.. Jul-23, Aug-23.... Jun-24. I am trying to find a way to dynamically remove the years from the table headers so its just Jul, Aug, Sept, Oct...etc..

id	Customer Description	Customer State	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24

to
															
id	Customer Description	Customer State	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun

Is something like this possible with Power Automate?

[Nived_Nambiar]

Hi @freddy65 

Run office script which can help to remove the year component from table headers

Use this office script for this purpose

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

  // Get all tables on the active worksheet
  let tables = sheet.getTables();

  // Define a regex pattern to match "month-year"
  const pattern = /^(January|February|March|April|May|June|July|August|September|October|November|December)-\d{4}$/i;

  // Loop through each table
  tables.forEach(table => {
    // Get the table headers
    let headers = table.getHeaderRowRange().getValues()[0];

    // Loop through each header and modify if it matches the pattern
    let newHeaders = headers.map(header => {
      if (pattern.test(header)) {
        // Split the header by '-'
        let [month, year] = header.split('-');
        return month; // Return the month part only
      }
      return header; // Return the original header if no match
    });

    // Set the modified headers back to the table
    table.getHeaderRowRange().setValues([newHeaders]);
  });
}

Use run script to run the office script as well in Power Automate.

Note that- due to some changes as per your requirement - there may be some changes in office script needed.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[freddy65]

Thank you for the quick response, as the file will be overridden over and over automatically I am unable to use a script which is why I was hoping for a different solution.  Thanks again

[Nived_Nambiar]

Hi @freddy65 

You can select script from run script- i think it won't get overridden all the time when file is overridden as it is stored separately.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[freddy65]

Hi @Nived_Nambiar 

I am giving this a go as I do not see any other solution. I have added the script to the excel file as my first step to then test on another file however I am getting the following errors.. Do I need to change the formatting of some of the header columns?

[Nived_Nambiar]

Hi @freddy65 

Yesterday i was not able to try the script in an excel file due to some issues. I have updated the script like below and i have tested it , it is working fine.

function main(workbook: ExcelScript.Workbook, sheetname:string, tablename:string) {
    // Get the active worksheet
    let sheet = workbook.getWorksheet(sheetname);

    // Get all tables on the active worksheet
    let table = sheet.getTable(tablename);

    // Define a regex pattern to match "month-year"
    const pattern = /^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sept|Oct|Nov|Dec)-\d{4}$/i;
	let headers: string[] = table.getHeaderRowRange().getValues()[0] as string[];
	let newHeaders = headers.map(header => {
            if (pattern.test(header)) {
                // Split the header by '-'
                let [month, year] = header.split('-');
                return month; // Return the month part only
            }
            return header; // Return the original header if no match
        });
	table.getHeaderRowRange().setValues([newHeaders]);

}

Here you have to pass 2 parameters- one is sheet name and other is table name where the data to changed is present.

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[freddy65]

Hi @Nived_Nambiar 

Thanks for getting back to me, much appreciated. I tried the new script in Excel and put both parameters in the code and the code runs but does not produce the results. Is there a way to test the output as I am new to Type script? Thanks again for the assist! 

I made the following changes:

let sheet = workbook.getWorksheet("MM Budget");
...
 let table = sheet.getTable("Data");

[Nived_Nambiar]

Hi @freddy65 

did you hardcoded that in code?- I have made the code to take from parameters the values - sheet name and table name.

Could you share how script looks finally in your case ?

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[freddy65]

Hi @Nived_Nambiar 

I made no other changes to your code. When I used your code it seems it can not find the sheetname or tablename so I thought I would hard code this in. With your code directly in Excel, It comes up with the following message, when I hardcode the sheet name then I get the same error with the tablename. The only thing I have not mentioned about my headers is that there are many more columns before the Month-Year columns but I don't see this as an issue.

Line 3: Workbook getWorksheet: The argument is invalid or missing or has an incorrect format.

[freddy65]

Apologies, I see what you did with the parameters and it did do something using Automate but it changed all years to 2024?

From:
Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24

To:
Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24

[Nived_Nambiar · Accepted solution]

Hi @freddy65 

Got it where the issue was. Actually I have assumed columns would be in format- Jan-2024, Feb-2024 etc. so the code was done in that way. Now it can work with any year formats, but assuming month format would be of MMM

Try this script

function main(workbook: ExcelScript.Workbook, sheetname:string, tablename:string) {
    // Get the active worksheet
    let sheet = workbook.getWorksheet(sheetname);

    // Get all tables on the active worksheet
    let table = sheet.getTable(tablename);

    // Define a regex pattern to match "month-year"
    const pattern = /^(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-\d+$/i;
	let headers: string[] = table.getHeaderRowRange().getValues()[0] as string[];
	let newHeaders = headers.map(header => {
            if (pattern.test(header)) {
                // Split the header by '-'
                let [month, year] = header.split('-');
                return month; // Return the month part only
            }
            return header; // Return the original header if no match
        });
	table.getHeaderRowRange().setValues([newHeaders]);

}

See how it worked

before running the script-

After running script-

Hope it helps !

Thanks & Regards,

Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

[freddy65]

Hi @Nived_Nambiar ,
That worked beautifully, this has also helped me understand what more I can do with scripting so thank you very much for your help!