How do I uppercase an entire sheet in Office Scripts Excel (Typescript)?-CodePudding

I was able to do the below in VBA and need to transfer my script to Office Scripts in Excel 365. I can't figure out how to do the whole sheet and not just a string.

This is what I used in VBA:

Dim rng As Range
Set rng = Selection
For Each cell In rng
cell.Value = UCase(cell)
Next cell

CodePudding user response：

This worked for me, could be a little cumbersome with a lot of cells but should still work.

function main(workbook: ExcelScript.Workbook, worksheetName: string)
{
  let worksheet = workbook.getWorksheet(worksheetName);
  
  // Ge tthe Used Range on the given worksheet and all values.
  let usedRange = worksheet.getUsedRange(false);
  var values = usedRange.getValues();

  // Now loop through each cell and upper case it.
  for (var row = 0; row < values.length; row  ) {
    for (var col = 0; col < values[row].length; col  ) {
      values[row][col] = values[row][col].toString().toUpperCase();
    }
  }

  usedRange.setValues(values);
}

Note: There is a parameter to the function so it is expected to be used via PowerAutomate/LogicApps but if you don't want that then you should be able to change it up to use the active sheet with relative ease.

e.g. ...

function main(workbook: ExcelScript.Workbook)
{
  let worksheet = workbook.getActiveWorksheet();

  ... continue the rest of the code here ...
}