Office script to get sheet names and populate them in a column-CodePudding

I've been using a VBA script to get all the sheet names in a worksheet and put them in a column. It works fine but we need to migrate to the online version and it doesn't support VBA at all. Could you help me out? Here's the original VBA script:

Function SHEET_NAMES()

Dim mainworkBook As Workbook
Set mainworkBook = ActiveWorkbook

Dim out As Variant

Dim x As Variant
x = mainworkBook.Sheets.Count - 1

ReDim out(x, 0)

Dim i As Variant
For i = 0 To x
    out(i, 0) = mainworkBook.Sheets(i   1).Name
Next i

SHEET_NAMES = out

End Function

Here's a Google Script that does the same if it helps:

/**
 * Returns the names of all sheets.
 * 
 * @return The name of all sheets in the Google Sheets spreadsheet.
 * @customfunction
 */
function ALLSHEETNAMES() {
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let sheetNames = [];
  sheets.forEach(function (sheet) {
    sheetNames.push(sheet.getName());
  });
  return sheetNames;
}

Also, can you tell me what can I use to get the name of the active sheet? Cell("filename") doesn't work in Online Excel.

Thanks a lot!

I've tried everything but it can't be done without a script. Even the named function method doesn't work online.

CodePudding user response：

User-defined functions are currently not supported on Office Scripts. So you won't be able to write =FUNCTION_NAMES() like you do with your VBA code. That said, you can still get the sheet names by running a macro. See the code below:

function main(workbook: ExcelScript.Workbook) {
  let sheetNames = workbook.getWorksheets().map(sh=>[sh.getName()])
  let output_sheet = workbook.getWorksheet("Sheet2")
  output_sheet.getRange("A1").getResizedRange(sheetNames.length-1, 0).setValues(sheetNames) 
}

You may need to update the Sheet2 value with the name of the output sheet you want. And you may need to update the A1 value with the range you want the names to be populated in.

One thing to note about the script is that it doesn't delete the old names. Deleting the old data isn't difficult. But it will depend on how the data in the output sheet is structured to delete correctly (e.g. can all the data in the output column where the sheet names are output be deleted?)

Also, can you tell me what can I use to get the name of the active sheet? Cell("filename") doesn't work in Online Excel.

To get the name of the activesheet, you can use:

workbook.getActiveWorksheet().getName()