Edit many sheets in same time - Excel online No VBA or Power automate or else

Question

My team had to move to Office365 and we cannot use anymore our former MS Excel with his VBA or our former G Sheets with his Scripts.

I already redo the workbook. Basically, it has many exactly same sheets. The only difference between the sheets is what column is shown or hidden. The VBA or the scripts we had just group all the sheets together, so when a there was an edit somewhere, it happen on every sheet as well.

For example, here was my vba for the sheet1 :

Private Sub Worksheet_Activate()
    Sheets(Array("January", "February", "March", "April", "May")).Select
End Sub

I understand that VBA cannot work with excel online. I understand as well that we cannot group sheets on excel online. I understand that I can teach my team to always open this excel file with the desktop app to benefit VBA, but it will be difficult, because they are not that good with excel and actually doesn't know what vba is.

What I ask is : Is there another way to make it happen with excel online ? I heard it has his own kind of script. I heard there is power automate.

Answer 1

Using Office Scripts, you can take the approach where you load the worksheets into an array and then simply run the same section of code over each item in a for loop ...

function main(workbook: ExcelScript.Workbook)
{
  let myWorksheets: ExcelScript.Worksheet[] = [];

  myWorksheets.push(workbook.getWorksheet("January"));
  myWorksheets.push(workbook.getWorksheet("February"));
  myWorksheets.push(workbook.getWorksheet("March"));

  myWorksheets.forEach(worksheet => {
    worksheet.getRange("A1:C5").setFormula("=ROW() & \".\" & COLUMN()");
  });
}

In my example, I've hardcoded the worksheets to a specific few but you could always do it for all worksheets if need be using the workbook.getWorksheets() method.

There doesn't appear to be a method which groups the sheets like that which exists in VBA.

If you're not familiar with Office Scripts, be sure to follow the documentation ... https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel ... to get a handle on it.

Further to that, these scripts can be executed from PowerAutomate/LogicApps using the Run script action ...

... the documentation does explain that.