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.
Contents
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");
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];
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.
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);
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);
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.
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.
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(); }
Delete worksheets
To delete a worksheet, we use the deleteWorksheet method.
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.
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());
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");
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());
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);
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());
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);
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());
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");
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
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.
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();
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match 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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- 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.
- 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:
