Google Apps Script问题：单元格颜色验证与数据提交存储故障求助

问题描述 #

我正在编写Google Apps Script代码，需求为：若单元格颜色为白色则返回False，否则返回True；提交数据时，仅当单元格颜色非白色时将数据存入数据库（视为有效状态）。但经过多次测试与代码调整，颜色验证逻辑仍无法正常工作：要么白色和绿色单元格均被允许提交，要么完全无法存储数据。验证通过时应弹出ui.alert提示“New Data Saved”，恳请提供技术帮助。

原代码 #

//Validating Cell Color #00FF40 (green)
   
   var cell = "B6";
   //var colors = ["#00FF40"];
   var ss = SpreadsheetApp.getActive();
   var sheet = ss.getActiveSheet();
   var range = sheet.getRange(cell);
   var currentColor = range.getBackground();
  if (currentColor == "#FFFFFF") {
    
    return false;
  }
  return true;
  

}
    
//Function to submit the data to Database Sheet

function SubmitData(){

  //declare a variable and set the reference of action google sheet

  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();

  var shUserForm = myGoogleSheet.getSheetByName("UserForm");

  var datasheet = myGoogleSheet.getSheetByName("Database");

  // to create the instance of the user-interface environment to user the alert feature

  var ui=SpreadsheetApp.getUi();

  var response=ui.alert("Submit", "Do you want to submit the data?",ui.ButtonSet.YES_NO);

  //checking user response
  if(response==ui.Button.NO){

  return;//to exit from this function

 }

 if(validateEntry()==true){

  var blankRow=datasheet.getLastRow()+1; //identify the next blank row

  //code to update the data in the database

  datasheet.getRange(blankRow,1).setValue(shUserForm.getRange("B6").getValue());//Happy value in cell
  
  
  // code to update the created date and time

  datasheet.getRange(blankRow,4).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm');

  // submitted by

  //datasheet.getRange(blankRow,5).setValue(Session.getActiveUser().getEmail());

  ui.alert('"New Data Saved ' + shUserForm.getRange("B6").getValue()+ '"');

  shUserForm.getRange("B6").setBackground('#FFFFFF');
  
  }
}

问题分析与修复方案 #

核心问题 #

1. 验证函数未正确声明：原代码缺少function validateEntry()的函数定义开头，导致SubmitData调用时找不到该函数，逻辑直接跳过或报错。
2. 工作表引用不一致：验证函数使用getActiveSheet()，但提交函数明确指定了UserForm工作表，若当前激活的不是该工作表，会取错单元格的颜色值。
3. 缺乏验证失败提示：用户无法知晓验证未通过的原因，不利于问题排查。

修复后的完整代码 #

// 验证单元格颜色：白色返回False，其他颜色返回True
function validateEntry() {
  var cell = "B6";
  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shUserForm = myGoogleSheet.getSheetByName("UserForm");
  var range = shUserForm.getRange(cell);
  var currentColor = range.getBackground().toLowerCase(); // 统一转小写避免大小写问题
  
  return currentColor !== "#ffffff";
}
    
// 提交数据到数据库工作表
function SubmitData(){
  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shUserForm = myGoogleSheet.getSheetByName("UserForm");
  var datasheet = myGoogleSheet.getSheetByName("Database");
  var ui = SpreadsheetApp.getUi();

  var response = ui.alert("Submit", "Do you want to submit the data?", ui.ButtonSet.YES_NO);
  if(response === ui.Button.NO){
    return;
  }

  if(validateEntry()){
    var blankRow = datasheet.getLastRow() + 1;
    
    // 写入数据到数据库
    datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("B6").getValue());
    // 写入日期时间
    datasheet.getRange(blankRow, 4).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm');
    
    ui.alert(`New Data Saved: ${shUserForm.getRange("B6").getValue()}`);
    // 重置单元格为白色
    shUserForm.getRange("B6").setBackground('#FFFFFF');
  } else {
    // 验证失败时提示用户
    ui.alert("提交失败", "单元格颜色为白色，无法提交数据", ui.ButtonSet.OK);
  }
}

关键修改点 #

• 补全validateEntry函数的完整声明，确保能被正确调用。
• 验证函数中明确使用UserForm工作表，避免激活工作表切换导致的错误。
• 将颜色值转成小写，避免Google Sheets可能返回的大小写不一致问题。
• 简化返回逻辑，直接用!==判断白色，代码更简洁。
• 增加验证失败时的弹窗提示，告知用户原因。
• 优化成功提示的字符串格式，去掉多余的引号。

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