Generate Reference Number to Google Sheets automatically using Google Apps Script

Question

I am trying to generate a reference number every time a new data in inserted automatically from Google Form. I would like to generate the format as TMS180001 until the maximum possible reference number which is TMS189999. I could not figure how to format the value/string to 0001 instead of 1 and when I ran below code nothing happened.

function onFormSubmit(e) {
var Time = e.values [0]; //column 1 timestamp
var TMSrefnum = [10]; //column 1 till 9 are other information while 
                      //column 10 is the reference number

if (Time = true); //if timestamp has value then add reference
                  //number to column 11
var i = 1 ; i < 9999 ; i++;
TMSrefnum = "TMS18" + toString(i);
}

Oh yeah I forgot to input the for statement but it still did not work. for (i = 1 ; i < 1000 ; i++); — Danial Asraf Norbee, Apr 08 '18 at 13:12
I've answered your specific question, but there are several other issues with your code. Is this script meant to write just the reference number or all of the form data? — Diego, Apr 08 '18 at 13:34
Possible duplicate of [How can I pad a value with leading zeros?](https://stackoverflow.com/questions/1267283/how-can-i-pad-a-value-with-leading-zeros) — Diego, Apr 08 '18 at 13:37

score 0 · Answer 1 · answered Apr 08 '18 at 13:32

See this answer to the question How to output integers with leading zeros in JavaScript.

You'll need to create an extra function outside the onFormSubmit() function (or include the Polyfill).

function pad(num, size) {
  var s = num+"";
  while (s.length < size) s = "0" + s;
  return s;
}

Then you would call

TMSrefnum = "TMS18" + pad(currentEntryNumber, 4);

score 0 · Accepted Answer · answered Apr 08 '18 at 13:35

Zero Padding

For zero padding use your own functions e.g.

Number.prototype.pad = function(size) {
    var s = String(this);
    while (s.length < (size || 2)) {s = "0" + s;}
    return s;
}


(9).pad();  //returns "09"

(7).pad(3);  //returns "007"

This will allow you to execute the .pad() method on any Number in your script.

Or write a method that looks something like this:

function pad(num, size) {
    var s = "000000000" + num;
    return s.substr(s.length-size);
}


pad(98);   // returns "000000098"

Sources:

score 0 · Answer 3 · answered Apr 09 '18 at 15:44

I managed to produce the reference number from below code.

var sheet1 = ss.getSheets()[0];

function pad(num, size) {
var s = num + "";
while (s.length < size) s = "0" + s;
return s;
}  

var valuerange = sheet1.getRange("A1:A").getValues();
var lastrownumber = valuerange.filter(String).length-1;
var TMSNUM = "TMS18" + pad(lastrownumber, 4);

Generate Reference Number to Google Sheets automatically using Google Apps Script

3 Answers3

Zero Padding

Sources: