如何在数据录入表单中按项目和ID递增编号?

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

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递增编号?

“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(&#39;Are you sure you want to continue?&#39;, 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(&quot;H6&quot;).getValue() //Main sheet field RIS Group ID Code
const fld_FundCluster = formMain.getRange(&quot;H7&quot;).getValue() //The Cluster Fund Name
const fld_Year = formSettings.getRange(&quot;F3&quot;).getValue()
const fld_ClusterCodeRegular = formSettings.getRange(&quot;G3&quot;).getValue()
const fld_ClusterCodeSeatbelt = formSettings.getRange(&quot;G4&quot;).getValue()
const fld_ClusterCodeInfra = formSettings.getRange(&quot;G5&quot;).getValue()
//----------
//-----------Regular Fund
//Get values
const id_RegularFundCluster = formSettings.getRange(&quot;H3&quot;) //Settings sheet Regular field RIS Group ID Code
const id_RegularFundClusterPerItem = formSettings.getRange(&quot;J3&quot;) //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 &gt; ssData.length;i++){
//
}
if(fld_FundCluster == &quot;Regular Fund&quot;){
//console.log(ssData[i][4])
ssData.map(x =&gt; {
if(x[4] == true){
var zeros = &quot;00000000000000000000000&quot;
var concatenateRegularFundIDPerItem = fld_Year +&quot;-&quot;+ fld_ClusterCodeRegular +&quot;-&quot;+ [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 == &quot;Regular Fund&quot;){     
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 &quot;ID&quot; Column and &quot;RIS ID
(PER ITEM AND CLUSTER)&quot; is correct but, the next time I submit another form, it does not stop running. Then after a few minutes, the error &quot;maximum error time&quot;.
*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 == &quot;Regular Fund&quot;){
ssData.map(x =&gt; {
if(x[4] == true){
var zeros = &quot;00000000000000000000000&quot;
var concatenateRegularFundIDPerItem = fld_Year +&quot;-&quot;+ fld_ClusterCodeRegular +&quot;-&quot;+ [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 == &quot;Regular Fund&quot;){
id_Num.setValue(i)
id_RegularFundCluster.setValue(nextID_RegularFundCluster+1)
id_RegularFundClusterPerItem.setValue(i)
}

huangapple
  • 本文由 发表于 2023年3月7日 14:03:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/75658506.html
匿名

发表评论

匿名网友

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

确定