Transferring Excel VBA to Google Sheets Apps Script for cell highlighting based on special characters

Other Questions Google Sheets
1

I have a working VBA script in Excel that colors cells in a defined range based on their content. This script checks the cells from C1 to E10000 and highlights them in red if they contain any characters outside of lowercase a-z, numbers, and hyphens. Otherwise, it colors them yellow.

Here’s the VBA code I’m currently using:

Sub HighlightSpecialCharacters()
    Dim checkRange As Range, currentCell As Range
    
    Set checkRange = Range("C1:E10000")
    For Each currentCell In checkRange
        If Len(currentCell.Text) > 0 Then
            If currentCell.Text Like "*[!.a-z0-9\-]*" Then
                currentCell.Interior.Color = vbRed
            Else
                currentCell.Interior.Color = vbYellow
            End If
        End If
    Next currentCell
End Sub

I need assistance in adapting this for Google Sheets. I understand that Google Sheets employs JavaScript in Apps Script instead of VBA. How can I modify my code to achieve the same functionality in Google Sheets?

2

Performance issues can arise when moving from VBA to Apps Script due to Google Sheets’ execution limits and API restrictions that aren’t present in Excel. To optimize your approach, avoid looping through individual cells. Instead, leverage batch operations using getValues() and setBackgrounds(), which are more efficient. Remember that Apps Script works with 2D arrays starting at index 0, unlike VBA that starts at 1, so be mindful of this difference. For character matching, replace VBA’s Like operator with JavaScript’s test() method and regex, such as ‘/[1]*$/’, to validate allowed characters effectively. Additionally, always include a trim() function to manage potential issues with empty cells and unexpected whitespace.

  1. a-z0-9- ↩︎

3

Google Sheets Apps Script handles regex totally differently than VBA’s Like operator. JavaScript regex patterns work way better for character validation. Here’s what I used for a similar conversion:

function highlightSpecialCharacters() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange('C1:E10000');
  const values = range.getValues();
  
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      const cellValue = values[i][j].toString();
      if (cellValue.length > 0) {
        const cell = sheet.getRange(i + 1, j + 3);
        if (/[^a-z0-9\-]/.test(cellValue)) {
          cell.setBackground('#FF0000');
        } else {
          cell.setBackground('#FFFF00');
        }
      }
    }
  }
}

The magic is /[^a-z0-9\-]/ - it matches any character that’s not in your allowed set. Way more efficient than checking cells one by one.

4

Skip the Apps Script headaches - I’d use Latenode for this. Had the same data validation nightmare before.

Latenode watches your Google Sheets live and applies conditional formatting with regex patterns. No coding needed. Just create a workflow that monitors C1:E10000, runs your character checks, and updates colors automatically.

Best part? You can expand beyond highlighting. Get alerts for invalid characters, log changes to another sheet, or trigger cleanup workflows. Zero Apps Script required.

I’ve run this on multiple sheets with thousands of cells - no timeouts or API limits.

The visual builder makes tweaking validation rules dead simple. Need new allowed characters? Update the regex in the workflow instead of digging through code.