Google Scripts App – 验证单元格颜色,提交数据并保存到数据库

huangapple go评论56阅读模式
英文:

Google Scripts App - Validating cell color, submitting data and saving to database

问题

I am trying to code where if cell color is white return False otherwise return true. when I submit the data if the cell color is other than white, save to a database as this would be considered true. However, I have been testing the validation of the color and changing the code a number of times but it doesn't seem to work. It would either accept both scenarios (cell color white or green) or doesn't save the data. If the validation is true then I get a ui.alert message "New Data Save". Any help would truly be appreciated.

//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');

}
}
英文:

I am trying to code where if cell color is white return False otherwise return true. when I submit the data if the cell color is other than white, save to a database as this would be considered true. However, I have been testing the validation of the color and changing the code a number of times but it doesn't seem to work. It would either accept both scenarios (cell color white or green) or doesn't save the data. If the validation is true then I get a ui.alert message "New Data Save". Any help would truly be appreciated.

//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

得分: 1

JavaScript是一种区分大小写的语言。在你的函数validateEntry()中尝试使用Logger.log(currentColor)(你在帖子中忘记声明它了:D)。你会看到它是一个小写字符串(#ffffff)。所以,你只需要将以下部分从:

if (currentColor == "#FFFFFF")

更改为:

if (currentColor == "#ffffff")

字符串

英文:

JavaScript is a case-sensitive language. Try Logger.log(currentColor) in your function validateEntry() (that you forgot to declare in your post :D). You'll see that is a string in lower case (#ffffff). So, you only need to change from:

if (currentColor == "#FFFFFF")

to:

if (currentColor == "#ffffff")

String

huangapple
  • 本文由 发表于 2023年5月30日 03:01:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76359771.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定