英文:
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")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论