How to convert VBA macro to Javascript

March 9, 2022

I want to use an Excel VBA macro on google sheet but I don’t know how to convert it to Javascript as google spreadsheet macros work on Javascript. Code basically pushes a row on different sheet based on the value of a cell.
Link for better understanding
https://www.extendoffice.com/documents/excel/3723-excel-move-row-to-another-sheet-based-on-cell-value.html
Can someone help me out in converting the following code.
P.S I have tried macro converter but don’t have an enterprise account to access it.

Sub moverows()
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Sheet1").UsedRange.Rows.Count
    J = Worksheets("Sheet2").UsedRange.Rows.Count
    If J = 1 Then
       If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Sheet1").Range("C1:C" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "Done" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
            xRg(K).EntireRow.Delete
            If CStr(xRg(K).Value) = "Done" Then
                K = K - 1
            End If
            J = J + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

>Solution :

You can use

function moverows() {
  var sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sh2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  if (sh1.getLastRow()==1){return}
  var data = sh1.getRange(2,1,sh1.getLastRow()-1,sh1.getLastColumn()).getValues()
  var archiveData = []
  var lignes = []
  var ligne = 1
  var col = sh1.getLastColumn()-1
  try {
    data.forEach(function (row) {
      ligne++
      if (row[col]=='done') {
        archiveData.push(row)
        lignes.push(ligne)
      }
    })
    sh2.getRange(sh2.getLastRow() + 1, 1, archiveData.length, archiveData[0].length).setValues(archiveData)
    lignes.reverse().forEach(x => sh1.deleteRow(x));
  } catch (e) {  }
}