英文:
How to Increment number per Item and ID in a data entry form?
问题
以下是您提供的代码的翻译部分:
我对使用Google表格和应用脚本非常陌生,我正在尝试增加ID并每个输入的项目。
数据输入表格:
[![enter image description here][1]][1]
以下是我的代码。
function btn_StockAvailableSubmit(){
var ui = SpreadsheetApp.getUi();
var response = ui.alert('您确定要继续吗?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {
//-----------声明
const id_Num = formSettings.getRange(2,13)
const next_IDNum = id_Num.getValue()
const fld_FundClusterGrpCode = formMain.getRange("H6").getValue() //主表字段RIS组ID代码
const fld_FundCluster = formMain.getRange("H7").getValue() //集群基金名称
const fld_Year = formSettings.getRange("F3").getValue()
const fld_ClusterCodeRegular = formSettings.getRange("G3").getValue()
const fld_ClusterCodeSeatbelt = formSettings.getRange("G4").getValue()
const fld_ClusterCodeInfra = formSettings.getRange("G5").getValue()
//----------
//-----------常规基金
//获取值
const id_RegularFundCluster = formSettings.getRange("H3") //设置表常规字段RIS组ID代码
const id_RegularFundClusterPerItem = formSettings.getRange("J3") //设置表常规字段RIS每个项目代码
const nextID_RegularFundCluster = id_RegularFundCluster.getValue()
const nextID_RegularFundClusterPerItem = id_RegularFundClusterPerItem.getValue()
var ssLastRow = formMain.getLastRow();
var ssLastColumn = formMain.getLastColumn()-1
var ssData = formMain.getRange(11, 2, ssLastRow - 10, ssLastColumn).getValues()
//需要找到TRUE来计算所有TRUE!
for(var i=nextID_RegularFundClusterPerItem;i > ssData.length;i++){
//
}
if(fld_FundCluster == "Regular Fund"){
//console.log(ssData[i][4])
ssData.map(x => {
if(x[4] == true){
var zeros = "00000000000000000000000"
var concatenateRegularFundIDPerItem = fld_Year +"-"+ fld_ClusterCodeRegular +"-"+ [zeros.substring(-1, 5 - i.toString().length) + i++]
var id = i - 1
x.unshift(id, deDate, deEntityName, deOffice); x.push(dateApproved,fld_FundClusterGrpCode,fld_Year,fld_ClusterCodeRegular,nextID_RegularFundCluster,concatenateRegularFundIDPerItem)
db_Requisition.appendRow(x); //传输数据
}
});
}
//--------增加组ID
id_Num.setValue(i)
if(fld_FundCluster == "Regular Fund"){
id_RegularFundCluster.setValue(nextID_RegularFundCluster+1)
id_RegularFundClusterPerItem.setValue(i)
}
结果(db_Requisition表单):
“ID”列和“RIS ID(每个项目和集群)”中的输出是正确的,但下次我提交另一个表单时,它不会停止运行。然后过了几分钟,出现“最大错误时间”错误。
请谅解我的代码
提前感谢您。
<details>
<summary>英文:</summary>
I am very new with Google sheets with app script, I am trying to increment the ID and per item entered using data entry.
Data entry form:
[![enter image description here][1]][1]
below is my code.
function btn_StockAvailableSubmit(){
var ui = SpreadsheetApp.getUi();
var response = ui.alert('Are you sure you want to continue?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {
//-----------Declarations
const id_Num = formSettings.getRange(2,13)
const next_IDNum = id_Num.getValue()
const fld_FundClusterGrpCode = formMain.getRange("H6").getValue() //Main sheet field RIS Group ID Code
const fld_FundCluster = formMain.getRange("H7").getValue() //The Cluster Fund Name
const fld_Year = formSettings.getRange("F3").getValue()
const fld_ClusterCodeRegular = formSettings.getRange("G3").getValue()
const fld_ClusterCodeSeatbelt = formSettings.getRange("G4").getValue()
const fld_ClusterCodeInfra = formSettings.getRange("G5").getValue()
//----------
//-----------Regular Fund
//Get values
const id_RegularFundCluster = formSettings.getRange("H3") //Settings sheet Regular field RIS Group ID Code
const id_RegularFundClusterPerItem = formSettings.getRange("J3") //Settings sheet Regular field RIS Per Item Code
const nextID_RegularFundCluster = id_RegularFundCluster.getValue()
const nextID_RegularFundClusterPerItem = id_RegularFundClusterPerItem.getValue()
var ssLastRow = formMain.getLastRow();
var ssLastColumn = formMain.getLastColumn()-1
var ssData = formMain.getRange(11, 2, ssLastRow - 10, ssLastColumn).getValues()
//need to find TRUE to count all TRUE!
for(var i=nextID_RegularFundClusterPerItem;i > ssData.length;i++){
//
}
if(fld_FundCluster == "Regular Fund"){
//console.log(ssData[i][4])
ssData.map(x => {
if(x[4] == true){
var zeros = "00000000000000000000000"
var concatenateRegularFundIDPerItem = fld_Year +"-"+ fld_ClusterCodeRegular +"-"+ [zeros.substring(-1, 5 - i.toString().length) + i++]
var id = i - 1
x.unshift(id, deDate, deEntityName, deOffice); x.push(dateApproved,fld_FundClusterGrpCode,fld_Year,fld_ClusterCodeRegular,nextID_RegularFundCluster,concatenateRegularFundIDPerItem)
db_Requisition.appendRow(x); //Transfer data
}
});
}
//--------Increment Group IDs
id_Num.setValue(i)
if(fld_FundCluster == "Regular Fund"){
id_RegularFundCluster.setValue(nextID_RegularFundCluster+1)
id_RegularFundClusterPerItem.setValue(i)
}
RESULT (db_Requisition form):
[![enter image description here][2]][2]
[1]: https://i.stack.imgur.com/DT7LY.png
[2]: https://i.stack.imgur.com/ai4lW.png
The output in "ID" Column and "RIS ID
(PER ITEM AND CLUSTER)" is correct but, the next time I submit another form, it does not stop running. Then after a few minutes, the error "maximum error time".
*Please bear with my codes*
Thank you in advance.
</details>
# 答案1
**得分**: 1
我删除了for循环,只是分配了一个变量并递增它,然后它起作用了。
我在`if`语句外声明了变量`z`和`i`。
以下是我的代码:
```javascript
var z = next_IDNum
var i = nextID_RegularFundClusterPerItem
if(fld_FundCluster == "Regular Fund"){
ssData.map(x => {
if(x[4] == true){
var zeros = "00000000000000000000000"
var concatenateRegularFundIDPerItem = fld_Year + "-" + fld_ClusterCodeRegular + "-" + [zeros.substring(-1, 5 - i.toString().length) + i++]
var id = z++
x.unshift(id, deDate, deEntityName, deOffice); x.push(dateApproved, fld_FundClusterGrpCode, fld_Year, fld_ClusterCodeRegular, nextID_RegularFundCluster, concatenateRegularFundIDPerItem)
db_Requisition.appendRow(x); //Transfer data
}
});
}
//--------Increment Group IDs
if(fld_FundCluster == "Regular Fund"){
id_Num.setValue(i)
id_RegularFundCluster.setValue(nextID_RegularFundCluster+1)
id_RegularFundClusterPerItem.setValue(i)
}
英文:
I deleted the for loop and just assigned a variable and incremented it and it worked.
I declared the var z
and i
outside the if
statement.
Here's my code:
var z = next_IDNum
var i = nextID_RegularFundClusterPerItem
if(fld_FundCluster == "Regular Fund"){
ssData.map(x => {
if(x[4] == true){
var zeros = "00000000000000000000000"
var concatenateRegularFundIDPerItem = fld_Year +"-"+ fld_ClusterCodeRegular +"-"+ [zeros.substring(-1, 5 - i.toString().length) + i++]
var id = z++
x.unshift(id, deDate, deEntityName, deOffice); x.push(dateApproved,fld_FundClusterGrpCode,fld_Year,fld_ClusterCodeRegular,nextID_RegularFundCluster,concatenateRegularFundIDPerItem)
db_Requisition.appendRow(x); //Transfer data
}
});
}
//--------Increment Group IDs
if(fld_FundCluster == "Regular Fund"){
id_Num.setValue(i)
id_RegularFundCluster.setValue(nextID_RegularFundCluster+1)
id_RegularFundClusterPerItem.setValue(i)
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论