Complete Guide to Excel Sheet Operations in Power Automate: Add, Delete, Rename, Copy, and Move

I’ve been asked about detailed Excel operations frequently lately, so here are some notes.

Office Script
Preparation
Creating a Sheet
Renaming a Sheet
Copying a Sheet
Moving a Sheet
Deleting a Sheet
Bonus: How to Detect Errors

Office Script

Office Script is a TypeScript/JavaScript-based programming feature that allows you to automate tasks in Excel Online.
Users can record and edit code to automate complex data processing, and by integrating with Power Automate, it’s possible to achieve automation of even broader workflows.

For more details, check here

Office Scripts samples - Office Scripts

Available Office Scripts samples and scenarios.

In this article, I’ve compiled operations related to Excel sheets.

Preparation

In this tutorial, we will be performing operations on an Excel file stored in SharePoint.

Office scripts can be edited by navigating to [Automate] → [New Script],

From Power Automate, you can call these scripts using the [Run script] action.

Creating a Sheet

To create a sheet, use the “addWorksheet” function of the Workbook.

function main(workbook: ExcelScript.Workbook, sheetName: string) {
    // Check if a sheet with the same name already exists
    if (workbook.getWorksheet(sheetName)) {
        console.log(A sheet named ${sheetName} already exists`);
        return;
    }

    // Create a new sheet
    const newSheet = workbook.addWorksheet(sheetName);

    // Activate the created sheet
    newSheet.activate();

    console.log(`Created a new sheet named ${sheetName}`);
}

When executed from Power Automate, a sheet with the specified name will be created.

Renaming a Sheet

To rename a sheet, use the “setName” function of the Worksheet.

function main(workbook: ExcelScript.Workbook, currentSheetName: string, newSheetName: string) {
  // Get the target sheet
  const worksheet = workbook.getWorksheet(currentSheetName);

  // If the target sheet does not exist
  if (!worksheet) {
    console.log(`Sheet named ${ currentSheetName } not found`);
    return;
  }

  // If the new name already exists
  if (workbook.getWorksheet(newSheetName)) {
    console.log(`A sheet named ${ newSheetName } already exists`);
    return;
  }

  // Rename the sheet
  worksheet.setName(newSheetName);

  console.log(`Sheet name changed from ${ currentSheetName } to ${ newSheetName }`);
}

This is how it looks when executed from Power Automate.

Copying a Sheet

To copy a sheet, use the “copy” function of the Worksheet object.
※ By adding “?” to a parameter, you can make it optional.

function main(workbook: ExcelScript.Workbook, sourceSheetName: string, newSheetName?: string) {
  // Get the source sheet
  const sourceSheet = workbook.getWorksheet(sourceSheetName);
  
  // If the source sheet does not exist
  if (!sourceSheet) {
    console.log(`Sheet named ${sourceSheetName} not found`);
    return;
  }
  
  // Copy the sheet (by default, placed at the end)
  const copiedSheet = sourceSheet.copy(ExcelScript.WorksheetPositionType.end);
  
  // Set the new sheet name
  if (newSheetName) {
    // Check if the specified sheet name already exists (only if it's different from the copied sheet's name)
    if (newSheetName !== copiedSheet.getName() && workbook.getWorksheet(newSheetName)) {
      console.log(`A sheet named ${newSheetName} already exists`);
      return;
    }
    // Rename the copied sheet
    copiedSheet.setName(newSheetName);
  }
  
  console.log(`Duplicated ${sourceSheetName} and created ${copiedSheet.getName()}`);
}

If you execute this action from Power Automate without specifying a new sheet name, Excel will automatically assign a name to the duplicated sheet.

Moving a Sheet

To move a sheet, use the setPosition function of the Worksheet.

Moving to the Beginning

To move a sheet to the beginning, specify 0 as the argument.

function main(workbook: ExcelScript.Workbook, sheetName: string) {
    // Get the target sheet
    const sheet = workbook.getWorksheet(sheetName);

    // If the sheet does not exist
    if (!sheet) {
        console.log(`Sheet named ${sheetName} not found`);
        return;
    }

    // Move the sheet to the top (position 0 is the first)
    sheet.setPosition(0);

    console.log(`Moved ${sheetName} to the top`);
}

Execution result.

Moving to the End

To move a sheet to the end, get the total number of sheets and specify that number minus 1.

function main(workbook: ExcelScript.Workbook, sheetName: string) {
  // Get the target sheet
  const sheet = workbook.getWorksheet(sheetName);
  
  // If the sheet does not exist
  if (!sheet) {
    console.log(`Sheet named ${sheetName} not found`);
    return;
  }
  
  // Get the number of sheets in the workbook
  const worksheetCount = workbook.getWorksheets().length;
  
  // Move the sheet to the end (last position is sheet count - 1)
  sheet.setPosition(worksheetCount - 1);
  
  console.log(`Moved ${sheetName} to the end`);
}

Moving Before a Specific Sheet

To move a sheet before a specific sheet, get the position (integer) of the target sheet, and specify that position as an argument.

function main(workbook: ExcelScript.Workbook, targetSheetName: string, referenceSheetName: string) {
  // Get the sheet to be moved
  const targetSheet = workbook.getWorksheet(targetSheetName);
  
  // Get the reference sheet
  const referenceSheet = workbook.getWorksheet(referenceSheetName);
  
  // If the target sheet does not exist
  if (!targetSheet) {
    console.log(`Sheet named ${targetSheetName} not found`);
    return;
  }
  
  // If the reference sheet does not exist
  if (!referenceSheet) {
    console.log(`Sheet named ${referenceSheetName} not found`);
    return;
  }
  
  // Get the position of the reference sheet
  const referencePosition = referenceSheet.getPosition();
  
  // Move the target sheet before the reference sheet
  targetSheet.setPosition(referencePosition);
  
  console.log(`Moved ${targetSheetName} before ${referenceSheetName}`);
}

Execution result.

Deleting a Sheet

To delete a sheet, use the delete function of the Worksheet.

function main(workbook: ExcelScript.Workbook, sheetName: string) {
    // Get the sheet to be deleted
    const sheet = workbook.getWorksheet(sheetName);

    // If the sheet does not exist
    if (!sheet) {
        console.log(`Sheet named ${sheetName} not found`);
        return;
    }

    // Delete the sheet
    sheet.delete();

    console.log(`Deleted ${sheetName}`);
}

Execution result.

Bonus: How to Detect Errors

In Office Script, you can pack return values into a result object, allowing you to write error handling processes on the Power Automate side.

For example, the following code declares returning a boolean “success” and a string “message (error message)” to Power Automate.

function main(workbook: ExcelScript.Workbook, sheetName: string): { success: boolean, message: string } {
    try {
        // Check if the sheet name is empty
        if (!sheetName || sheetName.trim() === "") {
            return { 
                success: false, 
                message: "Sheet name is not specified" 
            };
        }
        
        // Check if a sheet with the same name already exists
        if (workbook.getWorksheet(sheetName)) {
            return { 
                success: false, 
                message: `A sheet named ${sheetName} already exists` 
            };
        }

        // Create a new sheet
        const newSheet = workbook.addWorksheet(sheetName);

        // Activate the newly created sheet
        newSheet.activate();

        // Return success message
        return { 
            success: true, 
            message: `Created a new sheet named ${sheetName}` 
        };
    } catch (error) {
        // Handle unexpected errors
        return { 
            success: false, 
            message: `An error occurred: ${error.message || error}` 
        };
    }
}

You can reference these return values in Power Automate’s “Condition” action to build error handling behaviors.