Excel Online - Office script - renaming sheet based on cell value

[yomarcos]

Hi everyone,

I need to create a script to change the name of a sheet based on A1 on that same sheet. I need this script to run automatically.

Is this doable?
Does anyone know how?

I tried and failed.

Thank you

Moderator Edit:
This is for xl Online, so VBA won't work

 

[severynm]

Hi.

This script will rename the specified sheet according to what is in cell A1.

function main(workbook: ExcelScript.Workbook) {
    let worksheet = workbook.getWorksheet("Sheet 1");
    let newName = worksheet.getRange("A1").getValue().toString();
    worksheet.setName(newName);
}

Office scripts only support running on demand inside of excel online. Explain what you mean by "running automatically" - should it run on a schedule or only when some event happens? To run on a schedule, you'll need to use a different (and very handy) Microsoft product: Power Automate. From your script, click Create Flow. It will take you to the Power Automate site where you create the flow and choose your schedule and target file/script to run.

 

[yomarcos]

Hi severynm,
Thank you for your answer.
I get an error though:

Also, I need this to work for all the sheets, not just for Sheet 1.

When I say "running automatically", I mean I need this to run like an Excel formula: all the time. But if it needs to be narrowed down, I need this to update all sheets starting from sheet 2 when there is a change in Column A, Sheet 1.

 

[yomarcos]

I found a way to change the sheet name based on A1

function main(workbook: ExcelScript.Workbook) {
  let ws = workbook.getWorksheets();
  for (let i = 1; i < ws.length; i++) {
    let NewName = ws.getCell(0, 0).getValue();
    ws.setName(NewName);
  }
}