如何修改Google Sheets脚本，同时屏蔽B列与R列的非表头内容？

Hey there! Let's get that R column masking set up alongside your existing B column logic. Based on the code snippet you shared, here's exactly where and how to add the new functionality:

Step 1: Locate Your Existing B Column Handling Code #

First, find the section in your onOpen() function that deals with masking column B. It should look something like this (matching your mention of "B2:B"):

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet(); // Or use ss.getSheetByName("Your Form Responses Sheet") to target a specific tab

  // Existing B column masking logic
  var bColumnRange = sheet.getRange("B2:B");
  var bColumnValues = bColumnRange.getValues();
  var maskedBValues = bColumnValues.map(row => row[0] ? ["****"] : [""]);
  bColumnRange.setValues(maskedBValues);

  // ↓↓↓ This is where you'll add the R column code ↓↓↓
}

Step 2: Add the R Column Masking Logic #

Right after the code that handles column B, paste this nearly identical block tailored for column R:

// New R column masking logic
var rColumnRange = sheet.getRange("R2:R");
var rColumnValues = rColumnRange.getValues();
var maskedRValues = rColumnValues.map(row => row[0] ? ["****"] : [""]);
rColumnRange.setValues(maskedRValues);

Full Updated Code Example #

Putting it all together, your function will now mask both B and R columns:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Replace with your actual sheet name if needed
  var sheet = ss.getSheetByName("Form Responses 1"); 

  // Mask Column B
  var bColumnRange = sheet.getRange("B2:B");
  var bColumnValues = bColumnRange.getValues();
  var maskedBValues = bColumnValues.map(row => row[0] ? ["****"] : [""]);
  bColumnRange.setValues(maskedBValues);

  // Mask Column R (new code added here)
  var rColumnRange = sheet.getRange("R2:R");
  var rColumnValues = rColumnRange.getValues();
  var maskedRValues = rColumnValues.map(row => row[0] ? ["****"] : [""]);
  rColumnRange.setValues(maskedRValues);
}

Optional Optimization #

If your form adds new rows over time, processing the entire column every time can be inefficient. Instead, target only rows with data using getLastRow():

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 1");
  var lastRow = sheet.getLastRow();

  // Only run if there are data rows (row 2 or higher)
  if (lastRow >= 2) {
    // Mask Column B (rows 2 to lastRow; column 2 = B)
    var bColumnRange = sheet.getRange(2, 2, lastRow - 1);
    var bColumnValues = bColumnRange.getValues();
    var maskedBValues = bColumnValues.map(row => row[0] ? ["****"] : [""]);
    bColumnRange.setValues(maskedBValues);

    // Mask Column R (rows 2 to lastRow; column 18 = R)
    var rColumnRange = sheet.getRange(2, 18, lastRow - 1);
    var rColumnValues = rColumnRange.getValues();
    var maskedRValues = rColumnValues.map(row => row[0] ? ["****"] : [""]);
    rColumnRange.setValues(maskedRValues);
  }
}

This way, you're only processing rows that actually have form data, which speeds up the script.

内容的提问来源于stack exchange，提问作者Kristine M.