Google Script数据验证函数开发求助：列内容校验与弹窗提示

Hey there! Let's get your Google Script validation function sorted out. I'll walk you through a complete, working version that fits your requirements, plus explain how to tweak it for your specific columns and rules.

Here's the full, functional code that checks your sheet as you described:

function Validation() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  // Grab all data rows (assuming row 1 is headers; adjust if your data starts elsewhere)
  var data = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).getValues();
  
  // Define your validation rules here – customize these to match your needs!
  // Each rule targets a column, defines what's required, and sets an error message
  var validationRules = [
    {columnIndex: 2, required: true, message: "Column C cannot be empty when L is 'Y'"},
    {columnIndex: 4, requiredText: "Campaign", message: "Column E must contain 'Campaign' when L is 'Y'"}
  ];
  
  var hasErrors = false;
  var errorMessages = [];
  
  // Loop through every row to check validation
  data.forEach(function(row, rowIndex) {
    var lColumnValue = row[11]; // L column is index 11 (columns are 0-based: A=0, B=1... L=11)
    if (lColumnValue === "Y") {
      // Check each rule for this row
      validationRules.forEach(function(rule) {
        var cellValue = row[rule.columnIndex];
        
        // Check if the cell is required and empty
        if (rule.required && (cellValue === "" || cellValue === undefined)) {
          hasErrors = true;
          errorMessages.push(`Row ${rowIndex + 2}: ${rule.message}`); // +2 because we start at row 2
        }
        
        // Check if the cell must contain specific text
        if (rule.requiredText && !cellValue.toString().includes(rule.requiredText)) {
          hasErrors = true;
          errorMessages.push(`Row ${rowIndex + 2}: ${rule.message}`);
        }
      });
    }
  });
  
  // Show feedback to the user
  if (hasErrors) {
    SpreadsheetApp.getUi().alert("Validation Errors:\n" + errorMessages.join("\n"));
  } else {
    SpreadsheetApp.getUi().alert("Campaign info's been added correctly!");
  }
}

How to Customize This for Your Sheet #

• Adjust Column Indices: Change the columnIndex numbers in the validationRules array to match your target columns. Remember, columns are 0-based (A=0, B=1, ..., L=11).
• Add/Modify Rules: You can add more rule objects to the validationRules array for additional columns. For example, if column G needs to be non-empty, add:

  {columnIndex: 6, required: true, message: "Column G cannot be empty when L is 'Y'"}
  

• Update Required Text: Replace "Campaign" in the example rule with the specific text your column needs to contain.
• Header Row Adjustment: If your data starts at row 1 (no headers), change getRange(2, 1, lastRow - 1, ...) to getRange(1, 1, lastRow, ...) and update the row number in error messages to rowIndex + 1.

Key Features Explained #

• Dynamic Range: Uses getLastRow() and getLastColumn() to automatically include all your sheet data, so you don't have to hardcode row/column numbers.
• Clear Error Feedback: Lists exactly which rows have issues, making it easy for users to fix mistakes.
• Flexible Rules: The rule-based setup lets you easily add or remove checks without rewriting the core logic.

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