CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsleading-zeroautonumber
Melanie Moore
Melanie Moore

Reputation: 13

Autonumbering in Google Sheets Using Apps Script

I'm currently working with Google Forms and Appsheet to create a complaint tracking app for my company. This project requires that each complaint that is submitted via Google Form has a ComplaintID. The format for this ComplaintID is the current year and then the complaint number (e.g. 2020-001 would be the first complaint received in 2020). According to the specs on this project, the leading zeroes are required. The timestamp captures the year, so all of the necessary data is present.

Here's a script I found on howtogoogleapps.com that seems to almost work:

function addAutoNumber() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Form responses 1");
  // Get the last row that has content.
  var LastRow = sheet.getLastRow();
  // Set the first Auto Number
  var AutoNumberStart="2020-001";
 
  //---- First run ------------
  
  //Check if the first column is Timestamp
  if (sheet.getRange(1, 1).getValue() == "Timestamp") {
    // insert a column to the left and the text "ComplaintID" in the first row
    sheet.insertColumnBefore(1);
    sheet.getRange(1, 1).setValue("ComplaintID");
    // Fix for (probably) a bug that formats the new column 
    // with the same format of the column used to insert it,
    // in this case the column gets the date format like the Timestamp column.
    // So we set the format to number
    sheet.getRange("A2:A").setNumberFormat(0); 
    // check if there are already form responses and add numbers for them
    if (LastRow>1) {
      for(var ii=2; ii <= LastRow; ii++) {
        sheet.getRange(ii, 1).setValue(AutoNumberStart);
        AutoNumberStart="2020-"+ (parseInt(AutoNumberStart.substr(5,3)+1));
      }
    }
  }
  
  // ---- Add new Auto Number ----------
  
  // Check if there is a Number in the AutoNumber column 
  // for the last Form Submission
  if (sheet.getRange(LastRow, 1).isBlank()) {
  // Check if it is the first Form submission and set the AutoNumberStart
    if (LastRow == 2) {
      sheet.getRange(LastRow, 1).setValue(AutoNumberStart);
    } else {
      // Get the Last AutoNumber from the previous row
      var LastAutoNumber = sheet.getRange(LastRow-1, 1).getValue();
      // Set the next AutoNumber
      sheet.getRange(LastRow, 1).setValue("2020-"+ (parseInt(LastAutoNumber.substr(5,3)+1)));
    }
  }
}

However, what is returned is 2020-001, 2020-11, 2020-111, 2020-1111, 2020-1111, etc. Not exactly what I need. If you can help me out with this, I will be ETERNALLY grateful!

Upvotes: 1

Views: 1761

Answers (1)

Tanaike
Tanaike

Reputation: 201553

In this case, I think that parseInt is required to use to AutoNumberStart.substr(5, 3). Because AutoNumberStart.substr(5, 3) is the string type. So when parseInt(AutoNumberStart.substr(5,3)+1) is used, 1 is added to AutoNumberStart.substr(5,3) as the string. I think that this is the reason of your issue. So in order to avoid this, I would like to propose the following modification.

From:

AutoNumberStart="2020-"+ (parseInt(AutoNumberStart.substr(5,3)+1));

To:

AutoNumberStart = "2020-"+ ("00" + (parseInt(AutoNumberStart.substr(5, 3), 10) + 1)).slice(-3);
  • When AutoNumberStart is 2020-001, in this modification, parseInt(AutoNumberStart.substr(5, 3), 10) is 1 of number. And ("00" + (parseInt(AutoNumberStart.substr(5, 3), 10) + 1)).slice(-3) is 002.

Testing:

const AutoNumberStart1 = "2020-001";
const res1 = "2020-"+ ("00" + (parseInt(AutoNumberStart1.substr(5, 3), 10) + 1)).slice(-3);
console.log(res1)

const AutoNumberStart2 = "2020-099";
const res2 = "2020-"+ ("00" + (parseInt(AutoNumberStart2.substr(5, 3), 10) + 1)).slice(-3);
console.log(res2)

Note:

  • When you want to use the number over 1000 (more than 4 digits), how about the following modification? In this case, 2020-1000 is returned.

      const AutoNumberStart = "2020-999";
  const value = (parseInt(AutoNumberStart.substr(5, 3), 10) + 1).toString();
  const res = "2020-"+ ("00" + value).slice(-(value.length > 3 ? value.length : 3));
  console.log(res)

References:

Upvotes: 1

Related Questions