Select specific worksheets in office script

Question

there are sheets in my workbook that can either be titled "PL -Flat" or can be titled "FLAT"

this is the code that I have been using for the PL -Flat sheets, but I would prefer to use an if statement so I could select either of these sheets because the remaining code is the same between both sheets. There are also instances where both of these sheets are present. open minded to multiple ways to solve this problem

function main(workbook: ExcelScript.Workbook) {
        // Select the worksheet named "PL -FLAT"
        let flatSheet = workbook.getWorksheet('PL -FLAT');
        flatSheet.activate();

i've tried using the ? operator and writing an if statement unsuccessfully

also this is my first post, so any information on posting the code the way that it formats correctly would be appreciated. today i used CTRL+K — automationsplitsummary, May 09 '23 at 12:41
I've edited your code to be formatted properly. Basically just surround it with three tick marks, and write your code on a new line. Then return and three more tick marks. You can use single tick marks around inline code that doesn't need multiple lines. — Chris Barr, May 09 '23 at 12:49
Now that I'm re-reading your question you say you've tried a few different way of doing what you want. Can you edit your question and add in your failed attempts so we can get an idea of what exactly you were trying to do? — Chris Barr, May 09 '23 at 12:51

score 1 · Answer 1 · answered May 09 '23 at 13:12

According to documentation of getWorksheet() This should be working with if. Let me show you the code:

let flatSheet = workbook.getWorksheet('PL -Flat');
  
if (!flatSheet) {
  flatSheet = workbook.getWorksheet('FLAT');
}

if (flatSheet) {
  flatSheet.activate();
}

Another approach would be to use getWorksheets to get the list of all sheets and choosing the right one according to name you're looking for.

let allSheets = workBook.getWorksheets();
let flatSheet = allSheets.find(s => s.getName() === 'PL -Flat');
if (!flatSheet) {
  flatSheet = allSheets.find(s => s.getName() === 'Flat');
}

if (flatSheet) {
  flatSheet.activate();
}

taller_ExcelHome · Answer 2 · 2023-07-23T17:56:47.390

0

Since both sheets may (not) exist, using try/catch is a nice solution.

function main(workbook: ExcelScript.Workbook) {
  let shtNames = Array('PL -FLAT','FLAT')
  shtNames.forEach(sht => {
    try{
      let flatSheet = workbook.getWorksheet(sht);
      flatSheet.activate();
      // your code
    } catch(e) {
      console.log(`Sheet ${sht} is not exist.`);
    }
  })
}

edited Jul 23 '23 at 17:56

answered Jul 22 '23 at 20:45

taller_ExcelHome

2,232
1
2
12

Select specific worksheets in office script

2 Answers2