Excel Visual Basic to Google Scripts ?

[Ashton Reagin]

I have an Excel spreadsheet that has VBA code on each sheet and I am trying to figure out the right script language to do the same thing on my current google sheet. It's really just a copy, paste, delete function based on certain conditions. Any help or guidance would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim i As Integer

Dim b As Integer

Dim Lastrow As Long

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Lastrowb = Sheets("Complete").Cells(Rows.Count, "A").End(xlUp).Row + 1

Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1

    Sheets("Active").Activate

    For i = 1 To Lastrow

        If Cells(i, 18).Value = "COMPLETE" Then

        Rows(i).Copy Destination:=Sheets("Complete").Rows(Lastrowb)

        Lastrowb = Lastrowb + 1

        End If

    Next

    For b = Lastrowc To 1 Step -1

            If Cells(b, 18).Value = "COMPLETE" Then

                Rows(b).EntireRow.Delete

            End If

    Next

Application.ScreenUpdating = True

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Lastrowb = Sheets("Cancelled").Cells(Rows.Count, "A").End(xlUp).Row + 1

Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1

    Sheets("Active").Activate

    For i = 1 To Lastrow

        If Cells(i, 18).Value = "CANCEL" Then

        Rows(i).Copy Destination:=Sheets("Cancelled").Rows(Lastrowb)

        Lastrowb = Lastrowb + 1

        End If

    Next

    For b = Lastrowc To 1 Step -1

            If Cells(b, 18).Value = "CANCEL" Then

                Rows(b).EntireRow.Delete

            End If

    Next

Application.ScreenUpdating = True

With Sheets("Active")

 Dim lr As Long

 Set sh = ActiveSheet

Application.ScreenUpdating = True

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Lastrowb = Sheets("Pending").Cells(Rows.Count, "A").End(xlUp).Row + 1

Lastrowc = Cells(Rows.Count, "A").End(xlUp).Row + 1

    Sheets("Active").Activate

    For i = 1 To Lastrow

        If Cells(i, 18).Value = "PENDING" Then

        Rows(i).Copy Destination:=Sheets("Pending").Rows(Lastrowb)

        Lastrowb = Lastrowb + 1

        End If

    Next

    For b = Lastrowc To 1 Step -1

            If Cells(b, 18).Value = "PENDING" Then

                Rows(b).EntireRow.Delete

            End If

    Next

Application.ScreenUpdating = True

End With

End Sub

[Andrew Apell]

Well, Apps Script is basically JavaScript so that is the right and only language to consider.

Most of the procedures in your code can actually be implemented in Apps Script... all you need is to do little research.

The official documentation page and stack overflow are good places to start. If there is a particular place you're stuck, I'm sure the gurus on this forum will be more than happy to help.

[Niall]

Before rewriting the subroutine in App Script, it's definitely worth questioning if it makes sense to replicate it exactly as is? Another way of implementing this is to use =QUERY() function on the Complete/Cancel/Pending sheets to provide a view of the data, and let people do all their editing/processing in the Active sheet. Just mentioning this as half the time I've worked on similar spreadsheets it's been more efficient to leave all the data on one sheet.

[Eva Lucero]

Try this code (looks more involved than it is!)

This is a standalone script that accesses the spreadsheet "offline."  I am assuming that you are just transferring row data across sheets based on a status column:

function rearrangeRows() {

   var workbook = SpreadsheetApp.open(DriveApp.getFilesByName("Test Workbook").next());
   var sheetActive = workbook.getSheetByName("Active");
   var sheetCancelled = workbook.getSheetByName("Cancelled");
   var sheetCompleted = workbook.getSheetByName("Completed");
   var sheetPending = workbook.getSheetByName("Pending");
   for (var i = 1; i <= sheetActive.getLastRow(); i ++) {
     if (sheetActive.getRange(i, 18).getValue() == "Cancelled") {
        sheetCancelled.getRange(sheetCancelled.getLastRow() + 1,1,1,sheetActive.getLastColumn()).setValues(sheetActive.getRange(i,1,1,sheetActive.getLastColumn()).getValues());
     }
     if (sheetActive.getRange(i, 18).getValue() == "Pending") {
        sheetPending.getRange(sheetPending.getLastRow() + 1,1,1,sheetActive.getLastColumn()).setValues(sheetActive.getRange(i,1,1,sheetActive.getLastColumn()).getValues());
     }
     if (sheetActive.getRange(i, 18).getValue() == "Completed") {
           sheetCompleted.getRange(sheetCompleted.getLastRow() + 1,1,1,sheetActive.getLastColumn()).setValues(sheetActive.getRange(i,1,1,sheetActive.getLastColumn()).getValues());
     }
   }
   var numRecs = sheetActive.getLastRow();
   for (var j = numRecs; j > 0; j--) {
     if ((sheetActive.getRange(j,18).getValue() == "Completed") || (sheetActive.getRange(j,18).getValue() == "Cancelled") || (sheetActive.getRange(j,18).getValue() == "Pending")) {
        sheetActive.deleteRow(j);
     }
   }
}
              

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3c47fd82-1c28-4c6c-8950-29d9bcd670c9n%40googlegroups.com.