最后一行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:

1. 如果单元格中的值为数字(例如35233)，则 riskid 将为整数，因此为 riskid.length >将返回 null ，结果， if 条件的结果将为 false .在这种情况下，您可以使用 getDisplayValue 或 toString()来获取数字作为字符串，然后可以对其应用 .length :

1. If the value in the cell is a number (e.g. 35233) then riskid will be an integer and therefore riskid.length will return null and as a result the if condition will evaluate to false. In this case, you can either use getDisplayValue or toString() 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屋！