VBA equivalent in JavaScript for use in Google Apps Script for Google Sheets

Question

I am working on converting the Excel VBA code below to JavaScript for use in Google Apps Script for Google Sheets. Unfortunately, I am new enough to JavaScript that coming up with the correct syntax in JavaScript is like throwing a dart at a dartboard blind. Rather than spending hours attempting to make guesses work, I wanted to check if anyone can help me convert it. I am mostly stuck on coming up with an equivalent for the... If(Asc()...End If ...part of the function.

Public Function cleanPhoneNumber(thisNumber As String) As String
    ' this function aspires to clean any phone number format
    ' to standard format (+9999) 999-999-9999 or 999-999-9999
    ' works with almost all phone number formats stored in text

Dim retNumber As String

For i = 1 To Len(thisNumber)
    If Asc(Mid(thisNumber, i, 1)) >= Asc("0") And Asc(Mid(thisNumber, i, 1)) <= Asc("9") Then
        retNumber = retNumber + Mid(thisNumber, i, 1)
    End If
Next
If Len(retNumber) > 10 Then
    ' format for country code as well
    cleanPhoneNumber = Format(retNumber, "(+#) 000-000-0000")
Else
    cleanPhoneNumber = Format(retNumber, "000-000-0000")
End If
End Function

Amit Agarwal · Accepted Answer · 2014-12-01 19:36:55Z

1

It looks like your function is essential extracting the digits from the string and formatting the result. This can be done in JavaScript that you can paste inside the Apps Script editor.

function cleanNumber(number) {

  // Remove everything but digits
  number = number.replace(/\D*/g, "");

  // Format the number and return
  return number.substring(0, 3) + "-" + number.substr(4,3) + "-" + number.substr(7);

}

answered Dec 1, 2014 at 19:36

Amit Agarwal

11.3k1 gold badge35 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA equivalent in JavaScript for use in Google Apps Script for Google Sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related