如何用Google Script实现类似VBA Clean的清除非打印字符功能？

Absolutely! You can build a custom Google Apps Script that replicates VBA's CLEAN function—this will strip out all non-printable ASCII characters (values 0–31 and 127), which is exactly what you need for that stubborn pasted data where find/replace falls short.

1. Custom Cell Function (Use Like a Formula) #

First, here's a reusable function you can call directly in your sheet to clean individual cells or ranges:

function cleanText(input) {
  // Leave non-string values (numbers, dates, booleans) untouched
  if (typeof input !== 'string') return input;
  
  // Regex targets non-printable ASCII chars (0-31, 127)
  return input.replace(/[\x00-\x1F\x7F]/g, '');
}

How to use: In any cell, type =cleanText(A1) (replace A1 with your target cell) to clean its content. Drag the fill handle to apply it to an entire range.

2. Sheet-Wide Clean Function (Matching Your Trim Script) #

If you want to clean an entire sheet in one go, just like your trimSpacesSHT function, use this:

function cleanNonPrintableSHT(shtName) {
  const sheet = SpreadsheetApp.getActive().getSheetByName(shtName);
  
  // Check if the specified sheet exists
  if (!sheet) {
    SpreadsheetApp.getUi().alert(`Sheet "${shtName}" not found!`);
    return;
  }
  
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  
  // Process every cell: clean strings, leave other data types as-is
  const cleanedValues = values.map(row => 
    row.map(cell => typeof cell === 'string' ? cell.replace(/[\x00-\x1F\x7F]/g, '') : cell)
  );
  
  // Write the cleaned data back to the sheet
  dataRange.setValues(cleanedValues);
  SpreadsheetApp.getUi().alert(`Successfully cleaned non-printable characters in sheet "${shtName}"!`);
}

How to use:

1. Open the Apps Script editor (Extensions > Apps Script).
2. Paste this code alongside your existing trimSpacesSHT function.
3. Save the project (name it something like "SheetDataCleaners").
4. Run cleanNonPrintableSHT, enter your sheet name when prompted, and authorize the script (required first time only).

Quick Notes #

• This script preserves all printable characters—if you want to keep tabs (which are non-printable by default), adjust the regex to exclude \x09.
• It won’t mess with numbers, dates, or checkboxes, so your non-text data stays intact.
• Unlike manual find/replace, the regex targets exactly the hidden characters VBA’s CLEAN removes, so nothing slips through the cracks.

内容的提问来源于stack exchange，提问作者xyz