google-apps-script - 谷歌表格 | ReferenceError: shUserForm 未定义
问题描述
我创建了一个用户表单来将属性提交到数据库中。用户将数据插入表单并单击提交以将新输入写入数据库。我还没有让它工作，因为我不断收到错误代码。
在 Google 表格中提交表单时出现错误“ReferenceError: shUserForm is not defined”。我已经尝试了一切。也许我缺少一些东西。
请帮忙！
//Function to validate the entry made by user in Property Form
function validateEntry() {
//declare a variable and set the reference of active google sheet
var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
var shUserForm = myGoogleSheet.getSheetByName("User Form");
var ui = SpreadsheetApp.getUi(); //to create the instance of the user interface to show the alert
shUserForm.getRange("C7").setBackground('#FFFFFF');
shUserForm.getRange("C9").setBackground('#FFFFFF');
shUserForm.getRange("C11").setBackground('#FFFFFF');
shUserForm.getRange("C13").setBackground('#FFFFFF');
shUserForm.getRange("C15").setBackground('#FFFFFF');
shUserForm.getRange("C17").setBackground('#FFFFFF');
shUserForm.getRange("C19").setBackground('#FFFFFF');
shUserForm.getRange("C21").setBackground('#FFFFFF');
shUserForm.getRange("C24").setBackground('#FFFFFF');
shUserForm.getRange("C26").setBackground('#FFFFFF');
shUserForm.getRange("C28").setBackground('#FFFFFF');
shUserForm.getRange("C30").setBackground('#FFFFFF');
shUserForm.getRange("C32").setBackground('#FFFFFF');
//Validating Street #
if (shUserForm.getRange("C7").isBlank() == true) {
ui.alert("Please enter Street #");
shUserForm.getRange("C7").activate();
shUserForm.getRange("C7").setBackground('#FF0000');
return false;
}
//Validating Prefix
if (shUserForm.getRange("C9").isBlank() == true) {
ui.alert("Please enter Prefix");
shUserForm.getRange("C9").activate();
shUserForm.getRange("C9").setBackground('#FF0000');
return false;
}
//Validating Street Name
if (shUserForm.getRange("C11").isBlank() == true) {
ui.alert("Please enter Street Name");
shUserForm.getRange("C11").activate();
shUserForm.getRange("C11").setBackground('#FF0000');
return false;
}
//Validating Suffix
if (shUserForm.getRange("C13").isBlank() == true) {
ui.alert("Please enter Street Suffix");
shUserForm.getRange("C13").activate();
shUserForm.getRange("C13").setBackground('#FF0000');
return false;
}
//Validating City
if (shUserForm.getRange("C15").isBlank() == true) {
ui.alert("Please enter City");
shUserForm.getRange("C15").activate();
shUserForm.getRange("C15").setBackground('#FF0000');
return false;
}
//Validating State
if (shUserForm.getRange("C17").isBlank() == true) {
ui.alert("Please enter State");
shUserForm.getRange("C17").activate();
shUserForm.getRange("C17").setBackground('#FF0000');
return false;
}
//Validating County
if (shUserForm.getRange("C19").isBlank() == true) {
ui.alert("Please enter County");
shUserForm.getRange("C19").activate();
shUserForm.getRange("C19").setBackground('#FF0000');
return false;
}
//Validating Zip Code
if (shUserForm.getRange("C21").isBlank() == true) {
ui.alert("Please enter Zip Code");
shUserForm.getRange("C21").activate();
shUserForm.getRange("C21").setBackground('#FF0000');
return false;
}
//Validating Property Type
if (shUserForm.getRange("C24").isBlank() == true) {
ui.alert("Please enter Property Type");
shUserForm.getRange("C24").activate();
shUserForm.getRange("C24").setBackground('#FF0000');
return false;
}
//Validating Year Built
if (shUserForm.getRange("C26").isBlank() == true) {
ui.alert("Please enter Year Built");
shUserForm.getRange("C26").activate();
shUserForm.getRange("C26").setBackground('#FF0000');
return false;
}
//Validating SQFT
if (shUserForm.getRange("C28").isBlank() == true) {
ui.alert("Please enter Property Sq ft");
shUserForm.getRange("C28").activate();
shUserForm.getRange("C28").setBackground('#FF0000');
return false;
}
//Validating Bed
if (shUserForm.getRange("C30").isBlank() == true) {
ui.alert("Please enter Bedroom count");
shUserForm.getRange("C30").activate();
shUserForm.getRange("C30").setBackground('#FF0000');
return false;
}
//Validating Bath
if (shUserForm.getRange("C32").isBlank() == true) {
ui.alert("Please enter Bathroom count");
shUserForm.getRange("C32").activate();
shUserForm.getRange("C32").setBackground('#FF0000');
return false;
}
return true;
}
//Function to submit the data to the Database sheet
function submitData() {
//declare a variable and set the reference of active google sheet
var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
var shUserForm = myGoogleSheet.getSheetByName("User Form");
var datasheet = myGoogleSheet.getSheetByName("Database");
//to create the instance of the UI environmentto use the alert features
var ui = SpreadsheetApp.getUi();
var response = ui.alert("Submit", "Do you want to submit the data?", ui.ButtonSet.YES_NO);
//checking the 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 database
datasheet.getRange(blankRow, 3).setValue(shUserform.getRange("C7").getValue()); //Street #
datasheet.getRange(blankRow, 4).setValue(shUserform.getRange("C9").getValue()); //Prefix
datasheet.getRange(blankRow, 5).setValue(shUserform.getRange("C11").getValue()); //Street Name
datasheet.getRange(blankRow, 6).setValue(shUserform.getRange("C13").getValue()); //suffix
datasheet.getRange(blankRow, 7).setValue(shUserform.getRange("C15").getValue()); //city
datasheet.getRange(blankRow, 8).setValue(shUserform.getRange("C17").getValue()); //state
datasheet.getRange(blankRow, 9).setValue(shUserform.getRange("C19").getValue()); //County
datasheet.getRange(blankRow, 10).setValue(shUserform.getRange("C21").getValue()); //Zip
datasheet.getRange(blankRow, 2).setValue(shUserform.getRange("C24").getValue()); //Property Type
datasheet.getRange(blankRow, 11).setValue(shUserform.getRange("C26").getValue()); //Year Built
datasheet.getRange(blankRow, 12).setValue(shUserform.getRange("C28").getValue()); //Sqft
datasheet.getRange(blankRow, 13).setValue(shUserform.getRange("C30").getValue()); //bed
datasheet.getRange(blankRow, 14).setValue(shUserform.getRange("C32").getValue()); //bath
//Code to update the date and time
datasheet.getRange(blankRow, 15).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm');
//Submitted By
datasheet.getRange(blankRow, 16).setValue(Session.getActiveUser().getEmail());
ui.alert(' "New Data Saved - Property ' + shUserForm.getRange("C11").getValue() + '"');
shUserForm.getRange("C7").clear();
shUserForm.getRange("C9").clear();
shUserForm.getRange("C11").clear();
shUserForm.getRange("C13").clear();
shUserForm.getRange("C15").clear();
shUserForm.getRange("C17").clear();
shUserForm.getRange("C19").clear();
shUserForm.getRange("C21").clear();
shUserForm.getRange("C24").clear();
shUserForm.getRange("C26").clear();
shUserForm.getRange("C28").clear();
shUserForm.getRange("C30").clear();
shUserForm.getRange("C32").clear();
}
}解决方案
发现：
看起来错误是由于&函数
shUserForm中存在的变量的 2 个冗余实例造成的。当我更改函数内部的名称时，脚本运行良好。validateEntrysubmitDatashUserFormsubmitData
解决方案：
我已将您的脚本调整为仅对shUserForm两个函数使用一个变量。然后，我重命名shUserForm为userForm（由于某种原因，ReferenceError: shUserForm is not defined如果使用原始名称，仍然会出现错误）。
只需运行该validateEntry函数，它应该首先运行验证然后运行该submitData函数（如果所有必需的单元格都不为空）。
调整脚本：
//Function to validate the entry made by user in Property Form
function validateEntry() {
//declare a variable and set the reference of active google sheet
var myGoogleSheet = SpreadsheetApp.getActive();
var userForm = myGoogleSheet.getSheetByName("User Form");
var datasheet = myGoogleSheet.getSheetByName("Database");
var ui = SpreadsheetApp.getUi(); //to create the instance of the user interface to show the alert
userForm.getRange("C7").setBackground('#FFFFFF');
userForm.getRange("C9").setBackground('#FFFFFF');
userForm.getRange("C11").setBackground('#FFFFFF');
userForm.getRange("C13").setBackground('#FFFFFF');
userForm.getRange("C15").setBackground('#FFFFFF');
userForm.getRange("C17").setBackground('#FFFFFF');
userForm.getRange("C19").setBackground('#FFFFFF');
userForm.getRange("C21").setBackground('#FFFFFF');
userForm.getRange("C24").setBackground('#FFFFFF');
userForm.getRange("C26").setBackground('#FFFFFF');
userForm.getRange("C28").setBackground('#FFFFFF');
userForm.getRange("C30").setBackground('#FFFFFF');
userForm.getRange("C32").setBackground('#FFFFFF');
//Validating Street #
if (userForm.getRange("C7").isBlank() == true) {
ui.alert("Please enter Street #");
userForm.getRange("C7").activate();
userForm.getRange("C7").setBackground('#FF0000');
return;
}
//Validating Prefix
if (userForm.getRange("C9").isBlank() == true) {
ui.alert("Please enter Prefix");
userForm.getRange("C9").activate();
userForm.getRange("C9").setBackground('#FF0000');
return;
}
//Validating Street Name
if (userForm.getRange("C11").isBlank() == true) {
ui.alert("Please enter Street Name");
userForm.getRange("C11").activate();
userForm.getRange("C11").setBackground('#FF0000');
return;
}
//Validating Suffix
if (userForm.getRange("C13").isBlank() == true) {
ui.alert("Please enter Street Suffix");
userForm.getRange("C13").activate();
userForm.getRange("C13").setBackground('#FF0000');
return;
}
//Validating City
if (userForm.getRange("C15").isBlank() == true) {
ui.alert("Please enter City");
userForm.getRange("C15").activate();
userForm.getRange("C15").setBackground('#FF0000');
return;
}
//Validating State
if (userForm.getRange("C17").isBlank() == true) {
ui.alert("Please enter State");
userForm.getRange("C17").activate();
userForm.getRange("C17").setBackground('#FF0000');
return;
}
//Validating County
if (userForm.getRange("C19").isBlank() == true) {
ui.alert("Please enter County");
userForm.getRange("C19").activate();
userForm.getRange("C19").setBackground('#FF0000');
return;
}
//Validating Zip Code
if (userForm.getRange("C21").isBlank() == true) {
ui.alert("Please enter Zip Code");
userForm.getRange("C21").activate();
userForm.getRange("C21").setBackground('#FF0000');
return;
}
//Validating Property Type
if (userForm.getRange("C24").isBlank() == true) {
ui.alert("Please enter Property Type");
userForm.getRange("C24").activate();
userForm.getRange("C24").setBackground('#FF0000');
return;
}
//Validating Year Built
if (userForm.getRange("C26").isBlank() == true) {
ui.alert("Please enter Year Built");
userForm.getRange("C26").activate();
userForm.getRange("C26").setBackground('#FF0000');
return;
}
//Validating SQFT
if (userForm.getRange("C28").isBlank() == true) {
ui.alert("Please enter Property Sq ft");
userForm.getRange("C28").activate();
userForm.getRange("C28").setBackground('#FF0000');
return;
}
//Validating Bed
if (userForm.getRange("C30").isBlank() == true) {
ui.alert("Please enter Bedroom count");
userForm.getRange("C30").activate();
userForm.getRange("C30").setBackground('#FF0000');
return;
}
//Validating Bath
if (userForm.getRange("C32").isBlank() == true) {
ui.alert("Please enter Bathroom count");
userForm.getRange("C32").activate();
userForm.getRange("C32").setBackground('#FF0000');
return;
}
//Run submitData() if all required cells are not empty
submitData(true, userForm, datasheet);
}
//Function to submit the data to the Database sheet
function submitData(booleanValue, userForm, datasheet) {
//to create the instance of the UI environmentto use the alert features
var ui = SpreadsheetApp.getUi();
var response = ui.alert("Submit", "Do you want to submit the data?", ui.ButtonSet.YES_NO);
//checking the user response
if (response == ui.Button.NO) {
return; //to exit from this function
}
if (booleanValue == true) {
var blankRow = datasheet.getLastRow() + 1; //identify the next blank row
//code to update database
datasheet.getRange(blankRow, 3).setValue(userForm.getRange("C7").getValue()); //Street #
datasheet.getRange(blankRow, 4).setValue(userForm.getRange("C9").getValue()); //Prefix
datasheet.getRange(blankRow, 5).setValue(userForm.getRange("C11").getValue()); //Street Name
datasheet.getRange(blankRow, 6).setValue(userForm.getRange("C13").getValue()); //suffix
datasheet.getRange(blankRow, 7).setValue(userForm.getRange("C15").getValue()); //city
datasheet.getRange(blankRow, 8).setValue(userForm.getRange("C17").getValue()); //state
datasheet.getRange(blankRow, 9).setValue(userForm.getRange("C19").getValue()); //County
datasheet.getRange(blankRow, 10).setValue(userForm.getRange("C21").getValue()); //Zip
datasheet.getRange(blankRow, 2).setValue(userForm.getRange("C24").getValue()); //Property Type
datasheet.getRange(blankRow, 11).setValue(userForm.getRange("C26").getValue()); //Year Built
datasheet.getRange(blankRow, 12).setValue(userForm.getRange("C28").getValue()); //Sqft
datasheet.getRange(blankRow, 13).setValue(userForm.getRange("C30").getValue()); //bed
datasheet.getRange(blankRow, 14).setValue(userForm.getRange("C32").getValue()); //bath
//Code to update the date and time
datasheet.getRange(blankRow, 15).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm');
//Submitted By
datasheet.getRange(blankRow, 16).setValue(Session.getActiveUser().getEmail());
ui.alert(' "New Data Saved - Property ' + userForm.getRange("C11").getValue() + '"');
userForm.getRange("C7").clear();
userForm.getRange("C9").clear();
userForm.getRange("C11").clear();
userForm.getRange("C13").clear();
userForm.getRange("C15").clear();
userForm.getRange("C17").clear();
userForm.getRange("C19").clear();
userForm.getRange("C21").clear();
userForm.getRange("C24").clear();
userForm.getRange("C26").clear();
userForm.getRange("C28").clear();
userForm.getRange("C30").clear();
userForm.getRange("C32").clear();
}
}
样本结果：
带有示例数据的示例用户表单表：
运行函数后的数据库表：
validateEntry
推荐阅读
- javascript - 下载的 html 上的 unicode 字符串被破坏
- python - 执行Python多处理时的图像路径问题
- html - 如何在角材料表中显示选定的值？
- react-native - flex:1 让我的视图在 react-native 中消失
- java - Resteasy微配置客户端隐藏响应错误
- macos - macOS Core Location desiredAccuracy = kCLLocationAccuracyReduced，这行得通吗？
- java - 当我将 JPA(hibernate) 与本机查询一起使用时，表 'database_name.CTE' 不存在
- cryptocurrency - 尝试使用 remix 部署合约时出错
- python-3.x - TypeError：不支持的格式字符串传递给 NoneType.__format__ python
- python - 附加到上传的 PyPi 包的运行脚本 (.sh)