最后一行Google Apps脚本的增量ID [英] Increment ID from last row google apps script
问题描述
我为Google Apps脚本编写了一些代码.它是根据最后一行递增ID +1.到目前为止,除新ID的编号外,其他所有功能都可以正常工作,而不是显示为数字.
I coded something for google apps script. It is to increment ID +1 based on the last row. Everything is working so far except for the numbering of the new ID, instead of appearing as a number.
结果显示为R-NaN ,而不是 R-002
或类似的
您认为我应该在代码中修改什么?谢谢.
What do you think should I revise in my code? Thank you.
function new_item() {
// Get current spreadsheet
var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();
var mysheet = ss.getActiveSheet();
// Set date to today in update field at the top of the sheet
var now = new Date();
mysheet.getRange(1,4).setValue(now);
// Last non-empty row
var rlast = mysheet.getLastRow();
Logger.log("Last row = " + rlast);
// Insert Row below
mysheet.insertRows(rlast+1);
var r = rlast+1;
// Copy format from row above
var sourcerange = mysheet.getRange(rlast + ":" + rlast);
var targetrange = mysheet.getRange(r + ":" + r);
sourcerange.copyTo(targetrange, {formatOnly:true});
// Col. 2 : Risk identity
var riskid = mysheet.getRange(rlast,2).getValue();
if (riskid.length > 3){
// Extract number ex. 3
var riskidnb = riskid.substring(1,riskid.length);
// Increase risk number +1
riskidnb++
// Convert to string "0004"
var s = "000" + riskidnb
// Write risk nb i.e. "R004"
mysheet.getRange(r,2).setValue("R-"+ s.substring(s.length-4))
}
``´
推荐答案
说明/问题:
您的代码确实取决于B列最后一行中单元格的值:
Explanation / Issue:
Your code really depends on the value of the cell in column B last row:
var riskid = mysheet.getRange(rlast,2).getValue();
有两种情况,但我认为第二种情况适用于您的问题:
There are two scenarios but I believe the second applies to your issue:
-
如果单元格中的值为数字(例如35233),则
riskid
将为整数,因此为riskid.length
>将返回null
,结果,if
条件的结果将为false
.在这种情况下,您可以使用getDisplayValue
或toString()
来获取数字作为字符串,然后可以对其应用.length
:
If the value in the cell is a number (e.g. 35233) then
riskid
will be an integer and thereforeriskid.length
will returnnull
and as a result theif
condition will evaluate tofalse
. In this case, you can either usegetDisplayValue
ortoString()
instead to get the number as string and then you can apply.length
to it:
var riskid = mysheet.getRange(rlast,2).getValue();
如果单元格中的值为字符串(例如 R112
),则 if
条件的结果将为 true
.如果您这样做:
If the value in the cell is a string (e.g. R112
) then the if
condition will evaluate to true
. If you do that:
var riskidnb = riskid.substring(1,riskid.length);
riskidnb
将为 112
,但这仍然是字符串,因此,如果您执行 riskidnb ++
,您将获得 NAN
就像您当前遇到的问题.为了解决此问题,请将 riskidnb
转换为整数:
riskidnb
will be 112
but this is still a string and therefore if you do riskidnb++
you will get NAN
like the issue you have right now. In order to fix that, convert riskidnb
to integer:
var riskidnb = parseInt(riskid.substring(1,riskid.length));
然后您可以执行 riskidnb ++
,最后将其转换回字符串:
then you can do riskidnb++
and finally convert it back to string:
var s = "000" + riskidnb.toString();
解决方案:
function new_item() {
// Get current spreadsheet
var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();
var mysheet = ss.getActiveSheet();
// Set date to today in update field at the top of the sheet
var now = new Date();
mysheet.getRange(1,4).setValue(now);
// Last non-empty row
var rlast = mysheet.getLastRow();
Logger.log("Last row = " + rlast);
// Insert Row below
mysheet.insertRows(rlast+1);
var r = rlast+1;
// Copy format from row above
var sourcerange = mysheet.getRange(rlast + ":" + rlast);
var targetrange = mysheet.getRange(r + ":" + r);
sourcerange.copyTo(targetrange, {formatOnly:true});
// Col. 2 : Risk identity
var riskid = mysheet.getRange(rlast,2).getValue();
if (riskid.length > 3){
// Extract number ex. 3
var riskidnb = parseInt(riskid.substring(1,riskid.length));
// Increase risk number +1
riskidnb++
// Convert to string "0004"
var s = "000" + riskidnb.toString();
// Write risk nb i.e. "R004"
mysheet.getRange(r,2).setValue("R-"+ s.substring(s.length-4))
}
}
输出:
这篇关于最后一行Google Apps脚本的增量ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!