Google Apps Script - Set a fixed length of digit for making an auto-increment ID numbers in Google Sheets on every Form submission

Question

Google Apps Script - Please help me, I am having trouble with Google Apps Script. I was making an order entry ID in Google Sheets but to create a neat ID with uniform length of digits, I figured I need to repeat a string of '0' a number of times before an auto increment number in google sheets (set a fixed length of digits for the ID like 1,2...5...10 would be 0001,0002...0005...0010)

So far I have the auto-incrementing number function working fine already, it is just the repeating '0' function that doesn't

This is the code:

function onFormSubmit(e)
{
   var sheet = SpreadsheetApp.getActiveSheet();
   var row =  SpreadsheetApp.getActiveSheet().getLastRow();

   var tally = sheet.getRange("AG2").getValue();
   tally++;

   sheet.getRange(row,2).setValue(tally);
   sheet.getRange("AG2").setValue(tally);
*\\above is the code for the auto incrementing ID numbers*  

   if (tally.length<4) 
   {
     sheet.getRange(row,2).setValue('0'.repeat(4-tally.length), tally);
   }
*\\this is supposedly the code for setting up a fixed length of 4 digits for 
the ID but i was assuming that a javascript's: 'string'.repeat should work on 
google script.*
}

This, however, doesn't work so the output is still an incrementing ID number for every form submitted but the digits length is not fixed to 4 digits so I was getting 1,2,3...10,11 instead.

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) — tehhowch, Jul 03 '18 at 13:13
@tehhowch This may be a partial duplicate but I think the reason the person is getting the wrong result is formatting in Google Sheets. Calling setValue("0001") on a cell will output 1, but if you prefix the number with an apostrophe as in setValue(" '0001 "), the number will be converted to text with the leading zeros preserved — Anton Dementiev, Jul 03 '18 at 17:53

Anton Dementiev · Accepted Answer · 2018-07-03T17:55:19.867

Below is the example that might help you. Don't forget to prefix the leading zero with ' as it will prevent the sheet from formatting your output as a number. Otherwise, it'll just ignore the leading zeros.

      var zeros = "00000000000000000000000";      
      var values = [];          
      for (var i= 0; i < maxValues; i++) {

        values.push(["'" + zeros.substring(-1, 4 - i.toString().length) + i]);

      }

      var range = sheet.getRange(startRow, startColumn, values.length, 1);
      range.setValues(values);  

    }

Update Google Apps Script is based on the ECMAScript standard that doesn't support String.prototype.repeat() unfortunately.

Google Apps Script - Set a fixed length of digit for making an auto-increment ID numbers in Google Sheets on every Form submission

1 Answers1