Convert VBA EXCEL code to Google App Script

[Andrea Chávez]

Hello everyone , I need help in converting this VBA code into Google App Script language. 

Please!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Isect As Range

    Set Isect = Application.Intersect(Target, Range("N3:N300"))

    If Not Isect Is Nothing Then

        If Isect = "APROBADO" Then

            ActiveSheet.Unprotect "123"

            Isect.Offset(0, -13).Resize(1, 14).Locked = True

            Call ProtejeHoja

            ElseIf Isect = "NO APROBADO" Then

            ActiveSheet.Unprotect "123"

            Isect.Offset(0, -13).Resize(1, 14).Locked = True

            Call ProtejeHoja

        Else

            MsgBox "Solo puede indicar APROBADO o NO APROBADO"

        End If

    End If

    End Sub

[Clark Lind]

Hello, I am assuming that you want to see if column N has changed, and make sure it is one of two values ("APROBADO" or "NO APROBADO") (approved or not approved), and then lock the row ?

Much of what you are trying to do can be done with Data Validation:

Then a simple script to lock the row:

function onEdit() {

  const ss = SpreadsheetApp.getActiveSpreadsheet()

  const ws = ss.getActiveSheet()

  const mySheet = ss.getSheetByName("YOUR SHEET NAME")  //enter the actual sheet name
  

     if (ws.getName() !== mySheet.getName() ) {       //only run if the edit was on the correct sheet

        return

      }

  const targetRow = ws.getActiveCell().getRowIndex()

  const range = ws.getRange(targetRow,1,1,14)

  const protection = range.protect().setDescription('Protected Row');

 // Ensure the current user is an editor before removing others. Otherwise, if the user's edit

// permission comes from a group, the script throws an exception upon removing the group.

  const me = Session.getEffectiveUser();

  protection.addEditor(me);

  protection.removeEditors(protection.getEditors());

    if (protection.canDomainEdit()) {

        protection.setDomainEdit(false);

        }

}

Something like that should do what you want. 

[Martin Hawksey]

...also if you have or know someone with a Google Workspace Enterprise account Google have a VBA Macro Converter https://developers.google.com/apps-script/guides/macro-converter/overview

[Andrea Chávez]

Thank you so much, it worked !!

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/Gj_-camt-s8/unsubscribe.
To unsubscribe from this group and all its topics, 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/5f378600-ff8d-4ea7-a6aa-a1c3517fa22en%40googlegroups.com.

[Clark Lind]

I am glad it helped you! After I sent that, you may want to also check to make sure it only runs when column N is the column that is edited. To do that,

you can add the following (in bold). Also see the bottom option for coloring the row to show it has been locked. This can be any color name or hex value.

Just delete it if you don't want it, or comment it out like this:   //range.setBackground("LightGray") 

function onEdit() {

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getActiveSheet()
  const mySheet = ss.getSheetByName("YOUR SHEET NAME")  //enter the actual sheet name
 

//only run if the edit was on the correct sheet  
     if (ws.getName() !== mySheet.getName() ) {      

        return
      }
//only run if col N (14) was edited  

  const targetCol = ws.getActiveCell().getColumnIndex()   

     if ( targetCol !== 14) {

         return

     } 
  const targetRow = ws.getActiveCell().getRowIndex()
  const range = ws.getRange(targetRow,1,1,14)
  const protection = range.protect().setDescription('Protected Row');

 // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
// permission comes from a group, the script throws an exception upon removing the group.
  const me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
        }

  //as an option, you can also color the row:
   range.setBackground("LightGray")
}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CA%2BobnrFfG0p_E0vaZzuYcj_kv%3DSccuzCj89qh_yHx0HKpnK7Rw%40mail.gmail.com.

[Vincent Wilkens]

Sub Select_All_Cells_with_Data()

Set rng = ActiveSheet.UsedRange

rng.Cells(1, 13).Select

For i = 1 To rng.Rows.Count
    For j = 13 To rng.Columns.Count
        If rng.Cells(i, j) <> "" Then
            Union(Selection, rng.Cells(i, j)).Select
        End If
    Next j
Next i

Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

End Sub

can somebody convert this for me into google sheets? let me know, thank you =))

Op woensdag 21 april 2021 om 12:38:58 UTC+2 schreef cwl...@gmail.com:

[cwl...@gmail.com]

See if this works... (courtesy of, https://chat.openai.com/chat)

Q:   Please convert this code from VBA to google apps script.

Sub Select_All_Cells_with_Data() 

 Set rng = ActiveSheet.Used

Range rng.Cells(1, 13).Select 

 For i = 1 To rng.Rows.Count 

 For j = 13 To rng.Columns.Count 

 If rng.Cells(i, j) <> "" Then 

 Union(Selection, rng.Cells(i, j)).Select 

 End If 

 Next j 

Next i

Selection.Copy 

 Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ 

 xlNone, SkipBlanks:=False, Transpose:=False 

 End Sub

A:  To convert the VBA code you provided to Google Apps Script, you can use the following code:

function selectAllCellsWithData() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var cell = range.getCell(1, 13);
  cell.activate();
  for (var i = 1; i <= range.getNumRows(); i++) {
    for (var j = 13; j <= range.getNumColumns(); j++) {
      var currentCell = range.getCell(i, j);
      if (currentCell.getValue() != "") {
        var newRange = sheet.getRange(cell.getA1Notation() + ":" + currentCell.getA1Notation());
        newRange.activate();
      }
    }
  }
  var selectedRange = sheet.getActiveRange();
  selectedRange.copyTo(selectedRange, {contentsOnly: true});
}

This code uses the SpreadsheetApp class to access the active sheet and the data range within that sheet. It then loops through the rows and columns of the data range and selects the cells that have data. Finally, it copies the selected cells and pastes their values and number formats back into the same cells.

Note that this code is not a direct translation of the original VBA code, as the concepts and methods used in Google Apps Script are slightly different from those used in VBA. However, it should produce the same result as the original code.

[Tarun Gaukavard]

Can someone please convert this VBA macro to a google app script?

Sub Update_Current_Time()

Dim sh As Worksheet
Set sh = ActiveSheet

'''' Validations''''''''''
Dim rng As Range
Set rng = Selection

If Application.WorksheetFunction.CountBlank(rng) <> 1 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If

If rng.Row < 5 Or rng.Row > 35 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If

If rng.Column < 2 Or rng.Column > 7 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If

If sh.Cells(rng.Row, 1).Value <> Int(Now) Then
MsgBox "You can update the time for today only", vbCritical
Exit Sub
End If

On Monday, 26 December 2022 at 05:44:45 UTC+5:30 Tarun Gaukavard wrote:

Can someone please convert this VBA macro to google app script please

Sub Update_Current_Time() Dim sh As Worksheet Set sh = ActiveSheet '''' Validations'''''''''' Dim rng As Range Set rng = Selection If Application.WorksheetFunction.CountBlank(rng) <> 1 Then MsgBox "Incorrect range selection", vbCritical Exit Sub End If If rng.Row < 5 Or rng.Row > 35 Then MsgBox "Incorrect range selection", vbCritical Exit Sub End If If rng.Column < 2 Or rng.Column > 7 Then MsgBox "Incorrect range selection", vbCritical Exit Sub End If If sh.Cells(rng.Row, 1).Value <> Int(Now) Then MsgBox "You can update the time for today only", vbCritical Exit Sub End If

[Matthew Cotter]

hi there, i'm just looking for a conversion for a really short VBA to Apps Script, can anyone help?

Private Sub Workbook_Open() 

Worksheets("Mastersheet").Activate 

End Sub

just looking to fix the tab you open each sheet with

[Clark Lind]

This should do the same as the VBA you posted:

function onOpen() {

  const tab = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Mastersheet")

  tab.activate()

} Or even more simple:

function onOpen() {

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Mastersheet").activate()

}

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/38f85b3a-04aa-4901-a0db-73121cb79fa3n%40googlegroups.com.

[Tarun Gaukavard]

Can someone please convert this VBA macro to a google app script?

Sub Update_Current_Time()

Dim sh As Worksheet
Set sh = ActiveSheet

'''' Validations''''''''''
Dim rng As Range
Set rng = Selection

If Application.WorksheetFunction.CountBlank(rng) <> 1 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If

If rng.Row < 5 Or rng.Row > 35 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If

If rng.Column < 2 Or rng.Column > 7 Then
MsgBox "Incorrect range selection", vbCritical
Exit Sub
End If

If sh.Cells(rng.Row, 1).Value <> Int(Now) Then
MsgBox "You can update the time for today only", vbCritical
Exit Sub
End If

[Matthew Cotter]

Thank you so much. Really appreciate it

[cwl...@gmail.com]

See if the following works for you. Courtesy of OpenAI ChatGPT (https://chat.openai.com/chat#)

function updateCurrentTime() {
  var sh = SpreadsheetApp.getActiveSheet();
  var rng = SpreadsheetApp.getActiveRange();
 
  if (rng.isBlank().length != 1) {
    SpreadsheetApp.getUi().alert("Incorrect range selection", SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }
 
  if (rng.getRow() < 5 || rng.getRow() > 35) {
    SpreadsheetApp.getUi().alert("Incorrect range selection", SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }
 
  if (rng.getColumn() < 2 || rng.getColumn() > 7) {
    SpreadsheetApp.getUi().alert("Incorrect range selection", SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }
 
  if (sh.getRange(rng.getRow(), 1).getValue() != Math.floor(new Date())) {
    SpreadsheetApp.getUi().alert("You can update the time for today only", SpreadsheetApp.getUi().ButtonSet.OK);
    return;

[Your Taxi Link]

Hi, I am trying to get this VBA converted to GAS, but having no luck. I basically want the price I put in numbers to come out as text. I have tried the numbertext and moneytext and other formulas, but it gives me the cents as point written out or it will say the currency. I am trying to get this for printing cheques... here is the code...hopefully someone can help me. I got this from englishnumber I found online

' Example created by techonthenet.com
Function EnglishNumber(ByVal N As Currency) As String

   Const Thousand = 1000@
   Const Million = Thousand * Thousand
   Const Billion = Thousand * Million
   Const Trillion = Thousand * Billion

   If (N = 0@) Then EnglishNumber = "zero": Exit Function

   Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
   Dim Frac As Currency: Frac = Abs(N - Fix(N))
   If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
   Dim AtLeastOne As Integer: AtLeastOne = N >= 1

   If (N >= Trillion) Then
      Buf = Buf & EnglishNumberDigitGroup(Int(N / Trillion)) & " trillion"
      N = N - Int(N / Trillion) * Trillion
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Billion) Then
      Buf = Buf & EnglishNumberDigitGroup(Int(N / Billion)) & " billion"
      N = N - Int(N / Billion) * Billion
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Million) Then
      Buf = Buf & EnglishNumberDigitGroup(N \ Million) & " million"
      N = N Mod Million
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Thousand) Then
      Buf = Buf & EnglishNumberDigitGroup(N \ Thousand) & " thousand"
      N = N Mod Thousand
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= 1@) Then
      Buf = Buf & EnglishNumberDigitGroup(N)
   End If

   EnglishNumber = Buf
End Function

Private Function EnglishNumberDigitGroup(ByVal N As Integer) As String

   Const Hundred = " hundred"
   Const One = "one"
   Const Two = "two"
   Const Three = "three"
   Const Four = "four"
   Const Five = "five"
   Const Six = "six"
   Const Seven = "seven"
   Const Eight = "eight"
   Const Nine = "nine"
   Dim Buf As String: Buf = ""
   Dim Flag As Integer: Flag = False

   Select Case (N \ 100)
      Case 0: Buf = "": Flag = False
      Case 1: Buf = One & Hundred: Flag = True
      Case 2: Buf = Two & Hundred: Flag = True
      Case 3: Buf = Three & Hundred: Flag = True
      Case 4: Buf = Four & Hundred: Flag = True
      Case 5: Buf = Five & Hundred: Flag = True
      Case 6: Buf = Six & Hundred: Flag = True
      Case 7: Buf = Seven & Hundred: Flag = True
      Case 8: Buf = Eight & Hundred: Flag = True
      Case 9: Buf = Nine & Hundred: Flag = True
   End Select

   If (Flag <> False) Then N = N Mod 100
   If (N > 0) Then
      If (Flag <> False) Then Buf = Buf & " "
   Else
      EnglishNumberDigitGroup = Buf
      Exit Function
   End If

   Select Case (N \ 10)
      Case 0, 1: Flag = False
      Case 2: Buf = Buf & "twenty": Flag = True
      Case 3: Buf = Buf & "thirty": Flag = True
      Case 4: Buf = Buf & "forty": Flag = True
      Case 5: Buf = Buf & "fifty": Flag = True
      Case 6: Buf = Buf & "sixty": Flag = True
      Case 7: Buf = Buf & "seventy": Flag = True
      Case 8: Buf = Buf & "eighty": Flag = True
      Case 9: Buf = Buf & "ninety": Flag = True
   End Select

   If (Flag <> False) Then N = N Mod 10
   If (N > 0) Then
      If (Flag <> False) Then Buf = Buf & "-"
   Else
      EnglishNumberDigitGroup = Buf
      Exit Function
   End If

   Select Case (N)
      Case 0:
      Case 1: Buf = Buf & One
      Case 2: Buf = Buf & Two
      Case 3: Buf = Buf & Three
      Case 4: Buf = Buf & Four
      Case 5: Buf = Buf & Five
      Case 6: Buf = Buf & Six
      Case 7: Buf = Buf & Seven
      Case 8: Buf = Buf & Eight
      Case 9: Buf = Buf & Nine
      Case 10: Buf = Buf & "ten"
      Case 11: Buf = Buf & "eleven"
      Case 12: Buf = Buf & "twelve"
      Case 13: Buf = Buf & "thirteen"
      Case 14: Buf = Buf & "fourteen"
      Case 15: Buf = Buf & "fifteen"
      Case 16: Buf = Buf & "sixteen"
      Case 17: Buf = Buf & "seventeen"
      Case 18: Buf = Buf & "eighteen"
      Case 19: Buf = Buf & "nineteen"
   End Select

   EnglishNumberDigitGroup = Buf

End Function