Search code examples
google-apps-scriptgoogle-sheets-api

Stop the loop after the "code" is posted at the last row - google script

I am bit stuck here and trying to figure out the following:

  1. How increment the id (code) if it reaches Item-00010 or Item-00100..and so on? Is it better to start the value string to "Item-1000" rather than Item-00000? I don't know if it makes any difference.
  2. How to stop the code when it reaches the last row (with values) but somehow the value is still getting posted in an empty row (see screenshot an example)

I do have a check to skip if the code and time are blank, it appears that it does not work for row#5. I only want to post the new code when there is a value in column B.

How this code works (adopted from this) is to check if there is an existing code in column A. If yes, assign a new code by incrementing the last digit to 1.

TIA!

enter image description here

Here is the snippet of the code I am using:

function createCode() {
 var s = SpreadsheetApp.getActive();
 var sheet = ss.getSheetByName('FormResp');
 var LR = sheet.getLastRow();
 var sheet_Range = sheet.getRange(2, 1, LR, sheet.getLastColumn());
 var sheet_Values = sheet_Range.getValues();
    
 for (var x = 0; x < sheet_Values.length - 1; x++) {
        var code = sheet_Values[x][0];
        var time = sheet_Values[x][1];
        var status = sheet_Values[x][2];

    if (code && time == "") { continue; }

    if (((code == "" && time != "" && status == "") || (code && time != "" && status == ""))) {
      var prevRowData = sheet.getRange(x + 2, 1).getValue();

      if (prevRowData.length > 4) {
    
            var codeId = parseInt(prevRowData.toString().substring(prevRowData.indexOf('-') + 1, prevRowData.length));
    
            codeId++
            var newCodeId = '0000' + codeId.toString();
    
            var start = prevRowData.toString().substring(0, prevRowData.indexOf('-') + 1);
    
            sheet.getRange((x + 2) + 1, 1).setValue(start + newCodeId);
          }
        }
}
Solution

  • Modification points:

    • In your script, s, sheetResp, and timestamp are not declared.
    • About your 1st question of How increment the id (code) if it reaches Item-00010 or Item-00100..and so on? Is it better to start the value string to "Item-1000" rather than Item-00000? I don't know if it makes any difference., if you want to use the same length of the number value, how about using the zero padding?
    • About How to stop the code when it reaches the last row (with values) but somehow the value is still getting posted in an empty row (see screenshot an example), I think that the reason for this issue might be due to var sheet_Range = sheet.getRange(2, 1, LR, sheet.getLastColumn());. In the case of var sheet_Range = sheet.getRange(2, 1, LR, sheet.getLastColumn());, the values are retrieved from the 2nd row to the next row of the last row.

    When these points are reflected in your showing script, how about the following modification?

    Modified script 1:

    In this modification, your showing script was modified.

    function createCode() {
  var ss = SpreadsheetApp.getActive(); // Modified
  var sheet = ss.getSheetByName('FormResp');
  var LR = sheet.getLastRow(); // Modified
  var sheet_Range = sheet.getRange(2, 1, LR - 1, sheet.getLastColumn()); // Modified
  var sheet_Values = sheet_Range.getValues();
  for (var x = 0; x < sheet_Values.length - 1; x++) {
    var code = sheet_Values[x][0];
    var time = sheet_Values[x][1];
    var status = sheet_Values[x][2];
    if (code && time == "") { continue; }
    if (((code == "" && time != "" && status == "") || (code && time != "" && status == ""))) {
      var prevRowData = sheet.getRange(x + 2, 1).getValue();
      if (prevRowData.length > 4) {
        var codeId = parseInt(prevRowData.toString().substring(prevRowData.indexOf('-') + 1, prevRowData.length));
        codeId++
        var newCodeId = String(codeId).padStart(5, "0"); // Modiifed
        var start = prevRowData.toString().substring(0, prevRowData.indexOf('-') + 1);
        sheet.getRange((x + 2) + 1, 1).setValue(start + newCodeId);
      }
    }
  }
}

    Modified script 2:

    As another approach, when getValue and setValue are used in a loop, the process cost becomes high. Ref When this point is reflected, how about the following modification?

    function createCode2() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('FormResp');
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  var sheet_Values = range.getValues();
  sheet_Values.forEach((r, i, a) => {
    const [code, time, status] = r;
    if (code && time == "") return;
    if ((code == "" && time != "" && status == "") || (code && time != "" && status == "")) {
      if (i == 0) {
        a[i] = ["item-00001", time, status];
      } else {
        let [prefix, suffix] = a[i - 1][0].split("-");
        const newSuffix = String(Number(suffix) + 1).padStart(5, "0");
        a[i] = [`${prefix}-${newSuffix}`, time, status];
      }
    }
  });
  range.setValues(sheet_Values);

    Modified script 3:

    As another approach, in your situation, how about using the number format? When the number format is used, the actual value can be managed as a number value while the display value is like item-#####. When this point is reflected, how about the following modification?

    I'm not sure whether this approach is included in your expected result. If this was not included in your goal, please ignore this.

    function createCode3() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('FormResp');
  var lastRow = sheet.getLastRow();
  sheet.getRange("A2:A" + lastRow).setNumberFormat('"item"-00000');
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  var sheet_Values = range.getValues();
  sheet_Values.forEach((r, i, a) => {
    const [code, time, status] = r;
    if (code && time == "") return;
    if ((code == "" && time != "" && status == "") || (code && time != "" && status == "")) {
      a[i] = [i == 0 ? 1 : i + 1, time, status];
    }
  });
  range.setValues(sheet_Values);
}

    Testing:

    When the above scripts are used, the following result is obtained.

    enter image description here

    Note:

    • Unfortunately, I do not know your actual situation. So, when your actual situation is largely different from your showing image, the script might not be able to be used. Please be careful about this. If the above-modified scripts cannot be used in your actual situation, can you provide more information for correctly replicating your actual situation? By this, I would like to modify the script.

    References: