Office Scripts – Working with worksheets

Office Scripts - Worksheets

Some of the most common Excel automation actions involve managing worksheets.  Now that we have Office Scripts as an automation tool within Excel Online, it’s time to learn how to use worksheets within this new environment.

If you have written VBA Macros before, many of the principles will transfer directly to Office Scripts.  There is a new syntax to learn, but this reference guide will help jump-start the transition.

If this is your first time automating Excel, then this reference guide be will a good start for understanding the worksheet document object model.

While it is not essential to read this post from start to finish, doing so will give you a solid base.  It may also help to answer other questions which will arise later on.


Using this post

This post is intended to be a reference guide to help you build your own Office Scripts.  The Worksheet object is huge; there are so many things we could do that we just can’t cover every option.  Using and adapting the code in this post should cover 99% of the actions you would ever need to use.

For most examples, the code has been structured in a standard way.  First, variables are declared worksheets assigned to them; then those variables are used.  This consistency should help with adapting the code for your own needs.

Some examples use the console log to output a result.  In the real world, you would be unlikely to use this approach.  It is used here as a simple way of providing a result for code that would otherwise not provide any visual output.


Referring to worksheets

In this section, we look at how to reference worksheets and assign them to a variable.  Referencing worksheets doesn’t provide any visible output; however, it is the start point for most of the examples in the remainder of the post.  So, what we learn here will be used over and over again.

All variables in this section are declared with the name of ws and can be used in the place of any other ws variables created in the remainder of the post.

Reference a single worksheet by name

The following code assigns a single worksheet called Sheet1 to the ws variable using the getWorksheet method.  The parameter within getWorksheet must be the text string containing the worksheet name.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");
About getWorksheet
Purpose: Gets a worksheet using its name or ID.

Syntax: ExcelScript.workbook.getWorksheet();
Parameters: None
Returns: ExcelScript.Worksheet

Reference a single worksheet by position

To reference a worksheet by position, we use its number within the worksheets collection.  getWorksheets is the method used to obtain the collection of all worksheets within the workbook.

As Office Scripts has a zero-base, the first worksheet is [0], the second worksheet is [1], and so on.

//Assign the first worksheet to the ws variable
let ws = workbook.getWorksheets()[0];

//Assign the second worksheet to the ws variable
let ws = workbook.getWorksheets()[1];
About getWorksheets
Purpose: Represents the collection of worksheets associated with the workbook.
Syntax: ExcelScript.workbook.getWorksheets();
Parameters: None
Returns: ExcelScript.Worksheet[]

Reference the active worksheet

The active worksheet is the sheet currently selected in the Excel interface.  getActiveWorksheet is the method used to reference the active worksheet.

//Assign the active worksheet to the ws variable
let ws = workbook.getActiveWorksheet();

At the time of writing, it is not possible to select more than one worksheet within the Excel online interface; therefore, the active worksheet, is always the same as the selected worksheet.  This is different to the desktop version of Excel, where it is possible to select multiple sheets at the same time.

About getActiveWorksheet
Purpose: Gets the currently active worksheet in the workbook.
Syntax: ExcelScript.workbook.getActiveWorksheet();
Parameters: None
Returns: ExcelScript.Worksheet

Reference the first worksheet

As shown in the code below, we can refer to the first worksheet using the [0] position within the worksheet collection.

//Assign the first worksheet to the ws variable
let ws = workbook.getWorksheets()[0];

However, there is a better way, which is to use the getFirstWorksheet method.

//Assign the first worksheet to the ws variable
let ws = workbook.getFirstWorksheet();

The getFirstWorksheet method has an optional parameter to only include the first visible worksheet.  The code below demonstrates this parameter.

//Assign the first visible worksheet to the ws variable
let ws = workbook.getFirstWorksheet(true);
About getFirstWorksheet
Purpose: Gets the first worksheet in the worksheets collection.
Syntax: ExcelScript.Workbook.getFirstWorksheet(visibleonly?);
Parameters: visibleonly: true/false – an optional parameter to determine whether to reference the first visible worksheet (true) or from all worksheets (false).

Returns: ExcelScript.Worksheet

Reference the last worksheet

Just like referencing the first worksheet, we could use the position within the worksheet collection.  However, as the number of worksheets can vary, using the collection method becomes more complicated because we need to count the worksheets first.

The example below shows how to count the worksheets and then reference the last.

//Count the worksheets
let wsCount = workbook.getWorksheets().length;

//Assign the last worksheet to the ws variable
let ws = workbook.getWorksheets()[wsCount - 1];

If using this approach, remember that Office Scripts start numbering collection elements at 0, but the length property starts counting at 1.  Therefore, we must use minus 1 from the worksheet count to get the correct worksheet position number.

To reference the last worksheet, it is much easier to use the getLastWorksheet method, as shown below.

//Assign the last worksheet to the ws variable
let ws = workbook.getLastWorksheet();

Much like getFirstWorksheet, the getLastWorksheet method also has an optional parameter to only include visible worksheets.  The code below demonstrates how to refer to the last visible worksheet.

//Assign the last visible worksheet to the ws variable
let ws = workbook.getLastWorksheet(true);
About getLastWorksheet
Purpose: Gets the last worksheet in the collection.
Syntax: ExcelScript.Workbook.getLastWorksheet(visibleonly?);
Parameters: visibleonly: true/false – an optional parameter to determine whether to reference the last visible worksheet (true) or from all worksheets (false).

Returns: ExcelScript.Worksheet

Counting worksheets

In the examples above, we have already seen there are times when we may need to count the number of worksheets.  For this, we can use the length property of the worksheets collection.

Count worksheets

The following code displays the number of worksheets in the console log.

//Assign the worksheet collection to the wsArr variable
let wsArr = workbook.getWorksheets();

//Displays the number of items in the wsArr variable in the console log
console.log(wsArr.length);

Collections start their position number with a zero base, but the length property starts counting from zero.  Therefore, if using the length to refer to a position within the collection, remember to minus 1.

About length
Purpose: Gets the number of items in the worksheet collection/array.

Syntax: Array<ExcelScript.Worksheet>.length;
Parameters: None
Returns: number

Loop through worksheets

Most actions can only be completed one sheet at a time.  Therefore, to perform the same action on multiple sheets, we need to loop through and run the same code on each.

In this section, we look at the For loop and forEach loop as two options.

For loop through all worksheets

The code below starts by allocating the worksheet collection to a variable called wsArr.  Then we use a for loop to go through each worksheet in the collection by using its position.

The code between the two curly brackets is the action applied for every worksheet.  In this example, the name of each worksheet is displayed in the console log.

//Assign the worksheet collection to the wsArr variable
let wsArr = workbook.getWorksheets();

//Loop through all worksheets in the wsArr collection
for (let i = 0 ; i < wsArr.length ; i++) {
  
  //Display the worksheet name in the console log
  console.log(wsArr[i].getName());
  
  };

The for loop consists of three parts, which are separated by semi-colons ( ; ).

  • i = 0 : The first parameter is the initializer.  i is used as a number variable.
  • i < wsArr.length : The loop is executed while this condition continues to be true.
  • i++ : The final argument increments through the i variable at specific intervals. i++ adds 1 to the i variable each time.

Because we can control each of the three parameters, the for loop gives us a lot of flexibility.

forEach loop through all worksheets

The forEach loop performs the action contained within the curly brackets on each item in the worksheet collection.  It does not matter how many items are in the collection, they are all covered within the scope of the loop.

In this example, the name of each worksheet is displayed in the console.log.

//Assign the worksheet collection to the wsArr variable
let wsArr = workbook.getWorksheets();

//Loop through all worksheets in the wsArr worksheet collection
wsArr.forEach(ws => {

  //Display the worksheet name in the console log
  console.log(ws.getName());

  });

Check if worksheet exists

With Office Scripts, if we refer to a worksheet that does not exist, it will not trigger an error automatically.  Instead, an undefined value is returned.

The example below assigns a worksheet to a variable.  The code then uses logic to determine what to do if the value of the variable is undefined (i.e., the sheet does not exist).  There is a lot more that can be achieved with if logic in Office Scripts, but that is outside the scope of this post.  This example is to illustrate the concept.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Check if the ws variable is undefined.  If undefined worksheet does not exist.
//If defined a worksheet does exist
if (ws == undefined) {

    //Displays message if worksheet does not exist
    console.log("Worksheet does not exist");
    
    //use return; to stop any further code execution
    return;

} else {

  //Insert code if worksheet exists
  console.log("Worksheet exists");

};

Add worksheets

To add a new worksheet, we use the addWorksheet method.

About addWorksheet
Purpose: Adds a new worksheet to the workbook. The worksheet will be added at the end of the existing worksheets.

Syntax: ExcelScript.Workbook.addWorksheet(name?);
Parameters: name: string – an optional parameter used to set the name of the new worksheet.  If used, the name must be unique.  If not used, Excel will determine the name of the worksheet.

Returns: ExcelScript.Worksheet

Add a worksheet

The following are basic examples of adding a worksheet.

//Add a worksheet
workbook.addWorksheet();

//Add a worksheet and allocate to the ws variable
let ws = workbook.addWorksheet();

Add worksheet with a name

The code snippet below uses the optional parameter of the addWorksheet method.  The new worksheet is given the name of the string contained within the brackets.

//Add a worksheet with a name
workbook.addWorksheet("NewSheetName");

//Add a worksheet with a name and allocate to the ws variable 
let ws = workbook.addWorksheet("NewSheetName");

Create multiple worksheets

Worksheets are created one at a time, so to create multiple worksheets, we can use a for loop.  The example below creates 3 new worksheets.

//Using a for loop to create multiple worksheets
for (let i = 0; i <= 2; i++) {
    workbook.addWorksheet();
}

100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Delete worksheets

To delete a worksheet, we use the deleteWorksheet method.

About deleteWorksheet
Purpose: Deletes the worksheet from the workbook. Note that if the worksheet’s visibility is set to veryHidden, the delete operation will fail with an InvalidOperation exception. You should first change its visibility to hidden or visible before deleting it.

Syntax: ExcelScript.Worksheet.delete(position);
Parameters: None
Returns: void

Delete a worksheet

The following example deletes Sheet1 from the workbook.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Delete the ws worksheet
ws.delete();

Delete all except a specific sheet

The code snippet below deletes all worksheets except Sheet1.

The for loop is used to delete every worksheet.  Since very hidden worksheets cannot be deleted, the code below includes an action to make worksheets hidden prior to deletion.

//Assign the worksheet collection to the wsArr variable
let wsArr = workbook.getWorksheets();

//Assign the worksheet "Sheet1" to the wsToKeep variable
let wsToKeep = workbook.getWorksheet("Sheet1");

//Loop through all worksheets in the worksheet collection
for (let i = wsArr.length - 1; i >= 0; i--) {

  //Check if the item in the wsArr collection is equal to the wsToKeep variable
  //Only proceed if not equal to
  if (wsArr[i].getName() != wsToKeep.getName()) {

    //Make the worksheet hidden prior to deletion to remove error risk
    wsArr[i].setVisibility(ExcelScript.SheetVisibility.hidden) 

    //Delete the worksheet    
    wsArr[i].delete();
  };

}

Copy worksheets

In Excel online, copying worksheets is not as powerful as for the desktop version.  This is because Excel online has a workbook scope, meaning we can only copy worksheets within the same workbook.

To copy a worksheet, we use the copy method.

about copy
Purpose: Copies a worksheet and places it at the specified position.
Syntax: ExcelScript.Worksheet.copy(positionType?, relativeTo?);
Parameters:

  • positionType: ExcelSript.WorksheetPositionType – an optional parameter to set the location of the newly created worksheet in the workbook.  The default is “none”, which inserts the worksheet at the beginning of the workbook.
  • relativeTo: ExcelScript.Worksheet – Requires a worksheet object which determines the newly created worksheet’s relative position. This is only needed if positionType is  set to before or after

Returns: ExcelScript.Worksheet

Please note, there is inconsistent treatment between the addWorksheet method and the copy method.  addWorksheet positions the new worksheet at the end of the collection, while copy creates the worksheet at the beginning of the collection.

Copy a worksheet

The code below copies Sheet1 and places it into the first position.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Copy a worksheet into the first position
let wsNew = ws.copy();

Copy a worksheet to a specific position

The code below copies Sheet1 and places the new sheets in all the available positions.  When using the before and after options, we require an additional argument: the worksheet for which the copied sheet should be placed before or after.  The code uses Sheet4 as the relative position when using before or after options.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Assign the "Sheet4" worksheet to the relativeWs variable
let relativeWs = workbook.getWorksheet("Sheet4"); 

//Copy the ws worksheet, with the copied version placed before 
//the relativeWs worksheet
let newWs = ws.copy(ExcelScript.WorksheetPositionType.before, relativeWs);

//Copy the ws worksheet, with the copied version placed after 
//the relativeWs worksheet
let newWs = ws.copy(ExcelScript.WorksheetPositionType.after, relativeWs);

//Copy the ws worksheet, with the copied version placed at the beginning
let newWs = ws.copy(ExcelScript.WorksheetPositionType.beginning);

//Copy the ws worksheet, with the copied version placed at the end
let newWs = ws.copy(ExcelScript.WorksheetPositionType.end);

//Copy the ws worksheet, with the copied version placed in the default position, 
//which is the beginning
let newWs = ws.copy(ExcelScript.WorksheetPositionType.none);

Naming worksheets

All worksheets have names.  To get the name of a worksheet, use the getName method; to change the name, use the setName method.

Get the name of a worksheet

The following script displays the name of the first worksheet in the console log.  Change the number in the square brackets to retrieve the names of other items from the worksheets collection.

//Assign the worksheet to the ws variable based on position
let ws = workbook.getWorksheets()[0];

//Display the name of the ws worksheet in the console log
console.log(ws.getName());
About getName
Purpose: Returns the name of the worksheet
Syntax: ExcelScript.Worksheet.getName();
Parameters: None
Returns: string

Name a worksheet

To name a worksheet, we use the setName method.  The example below changes a worksheet name from Sheet1 to Sheet4.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Change the name of the ws worksheet
ws.setName("Sheet4");
About setName
Purpose: Changes the display name of the worksheet.
Syntax: ExcelScript.Worksheet.setName(name);
Parameters: name: string – the name to use for the worksheet.  The normal sheet naming rules apply.

Returns: void

Change worksheet position

We’ve already seen that worksheets have a position within the worksheets collection.  To retrieve the position of a worksheet, use the getPosition method, and to change the position, use the setPosition method.

Get worksheet position

The code example below uses the getPosition method to display the worksheet position of Sheet1 in the console log.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Display the position of the ws worksheet in the console log
console.log(ws.getPosition());
About getPosition
Purpose: Retrieves the zero-based position of the worksheet within the workbook collection.

Syntax: ExcelScript.Worksheet.getPosition();
Parameters: None
Returns: number

Change worksheet position

The setPosition method is used for changing the position of a worksheet.  The code below moves Sheet1 to the second position.  The 1 contained within the square brackets is the second position because collection numbering starts at zero.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Set the ws worksheet to the second position
ws.setPosition(1);
About setPosition
Purpose: Moves a worksheet to the specified position within the workbook.
Syntax: ExcelScript.Worksheet.setPosition(position);
Parameters: position: number- the zero-based position of the worksheet

Returns: void

Worksheet visibility

Using the standard Excel interface, we can hide and unhide worksheets.  There is also a special option that isn’t available in the standard interface.  This special option allows us to make a worksheet veryHidden; this means the worksheet is hidden and not displayed in the unhide list.  Therefore, regular users do not know the worksheet even exists.  This option is great for hiding worksheets containing settings and backend data.

Get worksheet visibility

The example below displays the visibility setting  (visible, hidden, or veryHidden) as a text string in the console log.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Get the visibility of the ws worksheet
console.log(ws.getVisibility());
about getVisibility
Purpose: Returns the visibility status of the worksheet.
Syntax: ExcelScript.Worksheet.getVisibility();
Parameters: None
Returns: ExcelScript.SheetVisibility (string: “hidden” | “visible” | “veryHidden”)

Change worksheet visibility

The example below demonstrates the code for all 3 visibility options.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Set the visibility of the ws worksheet to hidden
ws.setVisibility(ExcelScript.SheetVisibility.hidden);

//Set the visibility of the ws worksheet to visible
ws.setVisibility(ExcelScript.SheetVisibility.visible);

//Set the visibility of the ws worksheet to veryHidden
ws.setVisibility(ExcelScript.SheetVisibility.veryHidden);
about setVisibility
Purpose: Sets the visibility of the worksheet.
Syntax: ExcelScript.Worksheet.setVisibility(visibility);
Parameters: visibility: ExcelScript.SheetVisibility (contains the three settings for setting a worksheet as hidden, visible, veryHidden.
Returns: void

Worksheet protection

Worksheet protection is to prevent users from accidentally changing formulas or other critical parts of our workbook.

There are a lot of protection options available, so this section only contains an overview of the basic settings.  For more protection examples check out my Office Scripts protection guide.

Check if worksheet is protected

The code below uses the getProtected method to check if a worksheet is protected.  The result displayed in the console log can have a value of true or false.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Get the protection status of the ws worksheet
console.log(ws.getProtection().getProtected());
about getProtected
Purpose: Returns the protection status of a worksheet.
Syntax: ExcelScript.WorksheetProtection.getProtected();
Parameters: None
Returns: boolean (true/false)

Protect and unprotect worksheets

To protect and unprotect a worksheet we use the protect and unprotect methods.  The following code snippets demonstrate how to use those methods with and without passwords.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Protect the ws worksheet without password
ws.getProtection().protect();

//Unprotect the ws worksheet without password
ws.getProtection().unprotect();

//Protect the ws worksheet with password
ws.getProtection().protect("myPassword");

//Unprotect the ws worksheet with password
ws.getProtection().unprotect("myPassword");
about protect
Purpose: Protects a worksheet. Fails if the worksheet has already been protected.
Syntax: ExcelScript.WorksheetProtection.protect(options?, password?);
Parameters:

  • options?: ExcelScript.WorksheetProtectionOptions – optional sheet protection settings
  • password?: string – optional parameter to set the password to use

Returns: void

about unprotect
Purpose: Unprotects a worksheet
Syntax: ExcelScript.WorksheetProtection.unprotect(password?);
Parameters:  password?: string – password required to unprotect the worksheet. This is an optional argument as it is only required if protection has been applied with a password.

Returns: void

Activate worksheets

It is rarely necessary to activate a worksheet before executing a script to change it.  However, following the execution of a script, we may want to send the user to a specific worksheet.  For this, we can use the activate method.

about activate
Purpose: Activates the worksheet in the Excel interface.
Syntax: ExcelScript.Worksheet.activate();
Parameters: None
Returns: void

Activate a worksheet

The code snippet below activates Sheet1.

//Assign the "Sheet1" worksheet to the ws variable
let ws = workbook.getWorksheet("Sheet1");

//Activate the ws worksheet
ws.activate();


Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

Your email address will not be published. Required fields are marked *