How do I convert Macro to Office Scripts? Can anyone help me?

Question

Sub AssignUniqueIDsToExistingRows() 

    Dim ws As Worksheet 

    Dim cell As Range 

    Dim lastRow As Long 

    Dim sheetNames As Variant 

    Dim i As Integer 

 

    ' Define the sheets where Unique IDs should be created 

    sheetNames = Array("CapEx Tracking", "OpEx Tracking") 

 

    ' Loop through both sheets 

    For i = LBound(sheetNames) To UBound(sheetNames) 

        ' Set the worksheet 

        Set ws = ThisWorkbook.Sheets(sheetNames(i)) 

 

        ' Find last row with data in Column B (assuming B is always filled if data exists) 

        lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 

 

        ' Loop through rows starting from Row 3 to assign Unique IDs where missing 

        For Each cell In ws.Range("A3:A" & lastRow)  ' Start from Row 3 

            If cell.Value = "" And ws.Cells(cell.Row, "B").Value <> "" Then 

                cell.Value = "UID-" & Format(Now, "YYYYMMDDHHMMSS") & "-" & Int(Rnd() * 1000) 

            End If 

        Next cell 

    Next i 

 

    MsgBox "Unique IDs have been assigned to all existing rows on 'CapEx Tracking' and 'OpEx Tracking'.", vbInformation, "Process Complete" 

End Sub

Answer 1

There is no automated way to do this. Ultimately you just need to convert from VB syntax to Typescript. The Fundamentals documentation is a good start for understanding the syntax.

Without any testing, this would be a starting point for you.

function padDigits(value: number, width: number){
   return String(value).padStart(width, '0');
}

function dateAsFormatted(value: Date) {
   //Without using ITL, custom formatting dates requires doing it by hand   
   var year = padDigits(value.getFullYear(), 4);
   var month = padDigits(value.getMonth(), 2);
   var day = padDigits(value.getDay(), 2);

   var hour = padDigits(value.getHours(), 2);
   var minutes = padDigits(value.getMinutes(), 2);
   var seconds = padDigits(value.getSeconds(), 2);

   // "YYYYMMDDHHMMSS"
   return `{year}{month}{day}{hour}{minutes}{seconds}`;
}

function main(workbook: ExcelScript.Workbook) {    
   // Define the sheets where Unique IDs should be created 
   var sheetNames = ['CapEx Tracking', 'OpEx Tracking'];
   var sheets = workbook.getWorksheets();

   var nowFormat = dateAsFormatted(new Date(Date.now()));   
   
   // Loop through both sheets 
   for (let sheet of sheetNames) {
      // Set the worksheet 
      var ws = sheets[sheet];
      
      //Simplifying this down to simply use the getUsedRange function instead...
      // Find last row with data in Column B (assuming B is always filled if data exists) 
      //let lastRowIndex = ws.getRange("B:B").getUsedRange().getLastRow().getRowIndex()
      var usedRows = ws.getUsedRange();

      // Loop through rows starting from Row 3 to assign Unique IDs where missing, Starting from Row 3
      for (let rowIndex = 3; rowIndex < usedRows.getRowCount(); ++rowIndex) {         
         let cellA = usedRows.getCell(rowIndex, 0); 
         let cellB = usedRows.getCell(rowIndex, 1); 
         if (cellA.getValue() == "" && cellB != "") {
            var rnd = Math.random() * 1000;
            cellA.setValue(`UID-{nowFormat}-{rnd}`); 
         }
      }
   }

   console.log("Unique IDs have been assigned to all existing rows on 'CapEx Tracking' and 'OpEx Tracking'.");
}```