Converting VBA to Google Apps Script

Question

I have the VBA code below. I don't know how it works, but I want to convert it to Google Sheets.

I'm hoping someone can either:

• Explain to me what the VBA is doing so that I can, perhaps, reason it out enough to work on programming it as Google Apps script,

or

• Show me how the same VBA function would be achieved through Google Sheets.

Function getData(targetName As String, targetSheet As String, targetDate)
Application.Volatile True
Dim res As Double
Dim col As Integer
Dim cell As Range
Dim names As Range

Dim lastrow As Long

With ThisWorkbook.Worksheets(targetSheet)
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    col = Application.Match(targetDate, .Range("4:4"), 0)
    If col = 0 Then
        getData = CVErr(xlErrNA)
        Exit Function
    End If
    Set names = .Range(.Cells(4, "A"), .Cells(lastrow, "A"))

    For Each cell In names
        If cell = targetName Then
            res = res + cell.Offset(, col - 1)
        End If
    Next cell
End With

getData = res
End Function

Here's a link to an example excel file where the function is being used: https://www.dropbox.com/s/h5vcjv9tlh1vvg7/Resources%20and%20Projects%20Full%20Example.xlsm

Answer 1

Though I am not familiar with Google Apps scripting, I can help you with the first part.

The point of the function appears to be adding up all values where the name found in column A matches targetName passed in as a parameter and the date found in row 4 matches targetDate, which is also a parameter. (Row is determined by name and column is determined by date.) The total value is then returned as a double.

Here's line by line comments.

Function getData(targetName As String, targetSheet As String, targetDate)
Application.Volatile True 'I don't see a reason for this line
Dim res As Double
Dim col As Integer
Dim cell As Range
Dim names As Range

Dim lastrow As Long

With ThisWorkbook.Worksheets(targetSheet) 'All ranges start with ThisWorkbook.'targetSheet'
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Get the last row with data in column A to 'lastrow'
    col = Application.Match(targetDate, .Range("4:4"), 0) 'Find 'targetDate' in row 4 and set it to 'col'
    If col = 0 Then 'Couldn't find 'targetDate'
        getData = CVErr(xlErrNA) 'Function returns the appropriate error
        Exit Function 'Exit the function after setting the return value
    End If
    Set names = .Range(.Cells(4, "A"), .Cells(lastrow, "A")) 'Setting the range from A4 to A'lastrow' to 'names'

    For Each cell In names 'Looping through every 'cell' in the 'names' range
        If cell = targetName Then 'If the 'cell' value matches the 'targetName' passed in as a parameter
            res = res + cell.Offset(, col - 1) 'Add the value from the column with the 'targetDate' to 'res'
        End If
    Next cell
End With

getData = res 'Return the total
End Function