CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
drunkenmonkey44
drunkenmonkey44

Reputation: 51

Google Sheets Script Keep Leading 0's in a sku after incrementing by 1

I am trying to increment a SKU by one using a button on Google Sheets and I am unable to keep leading 0's after incrementing the SKU by 1. The code below will increment the SKU by 1 and keep formatting except for leading 0's. The SKU format is: CBP-2022-00001. Any help with this would be much appreciated. I am new to the language, so let me know if you need more information.

function incrementSku() {

  const ss = SpreadsheetApp.getActive();

  const sh = ss.getSheetByName('cheat_sheet');

  var sku = sh.getRange("B15").getValue();

  var skuEnd = sku.slice(-5);

  var skuBegin = sku.slice(0,9);

  var skuNum = Number(skuEnd) + 1;

  var skuFinal;

  skuEnd = skuNum.toString();

  skuFinal = skuBegin + skuEnd;

  sh.getRange("B15").setNumberFormat('@STRING@').setValue(skuFinal);

}

Upvotes: 0

Views: 534

Answers (1)

TheWizEd
TheWizEd

Reputation: 8606

Description

You can use the Utilities.formatString() to pad the left with zeros.

Edit script

skuFinal = skuBegin + Utilities.formatString("%05d",skuNum);
sh.getRange("A2").setValue(skuFinal);

Reference

Upvotes: 2

Related Questions