update the VBA code and the same in office scripts

Question

I have following code in VBA to achieve this in Excel.

there is column with header and values. in some cells there could be multiple values separated by comma,semicolon or pipe. I wany to split such cells and the new values exept the 1st one paste in the same column so inserting new rows is required. It works but not at 100% ,the if condition checks for delimiters but how to split by the required delimiter as I mentioned above. now it splits just by semicolon. Next step would be if there are other columns too but without multivalues but they need to be copied accordingly. that means you have 3 columns. in the column A you have value a ,in the column B you have some multivalues and in the column C you have value s. so user select the range of cells,in this case in column B then it will be splitted as the code does now but it copies the values from column A and C from the original row as well.

the last requirement is to rewrite the VBA in office scripts to use it in excel online as well.

here is the code I have but no idea how to proceed further

Sub SplitCells()
    
        Dim rng As Range
        Set rng = Application.InputBox("Please select a range of cells:", Type:=8)
    
        Dim cell As Range
        For Each cell In rng
    
            If InStr(cell.Value, ",") > 0 Or InStr(cell.Value, ";") > 0 Or InStr(cell.Value, "|") > 0 Then
    
                Dim values() As String
                values = Split(cell.Value, ";")
    
                Dim numNewRows As Long
                numNewRows = UBound(values)
    
                Dim i As Long
                For i = numNewRows To 1 Step -1
    
                    values(i) = Trim(values(i)) ' Trim the values after splitting
                    cell.Offset(1).EntireRow.Insert shift:=xlDown
    
                Next i
    
                cell.Resize(numNewRows + 1).Value = Application.Transpose(values)
    
                cell.Resize(numNewRows + 1).EntireColumn.AutoFit ' Optional: adjust column width to fit data
    
                Set cell = cell.Offset(numNewRows) ' Move to last row with split values
    
            End If
        Next cell
End Sub

Answer 1

• Office Script is employed with Excel online, which means there's no user interaction involved (e.g., selecting a range).
• The code below splits all the data in column A.

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let columnA = sheet.getRange("A:A").getIntersection(sheet.getUsedRange(true));
  let numRows = columnA.getUsedRange().getRowCount();
  const columnIndex = 0  // Column A, modify as needed
  // Process data from the bottom
  for (let row = numRows - 1; row > -1; row--) {
    let cell = sheet.getCell(row, columnIndex);
    let cellValue = sheet.getCell(row, columnIndex).getText();
    // Check if the cell contains the delimiter
    if (cellValue.includes(",") || cellValue.includes(";") || cellValue.includes("|")) {
      let values = cellValue.split(/,|;|\|/);
      // Save the first part in the current cell
      cell.setValue(values[0]);
      // Insert new rows to save the other part of data
      for (let i = 1; i < values.length; i++) {
        sheet.getCell(row + i, columnIndex).insert(ExcelScript.InsertShiftDirection.down);
        sheet.getCell(row + i, columnIndex).setValue(values[i]);
      }
    }
  }
}