CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsleading-zero
Geo
Geo

Reputation: 15

Add leading zeroes in custom function in Google Apps Script

I'd like to create a custom function that does the same thing as Text(A1,"0000000"), but I have to do it in Google Apps Script because it will be incorporated into a more complicated custom function. Could anybody show me the correct way? Here is what I've been trying, and some derivations thereof:

 `/**
 * Sets custom format as "0000000"
 *
 * @param {number} input The value to format.
 * @return The input set to the new custom format
 * @customfunction
 */
 function to_16ths(input) {
 var n = input.setNumberFormat("0000000");
 return n;
 }

Upvotes: 0

Views: 2771

Answers (2)

SputnikDrunk2
SputnikDrunk2

Reputation: 4048

I've tried creating a custom function using setNumberFormat() but got the error "you do not have permission to call setNumberFormat” on the Sheet. I was able to confirm that setNumberFormat() requires a user authorization request and it is not possible to be used in a custom function, according to an answer from this post.

Thus, I have created a code manually to format any input number as "0000000" if input number length is more than 2 and to format any input number as "000" if input number length is between 1 or 2. You may refer to this code below:

UPDATED

function to_16ths(input) {
  var n = input;//gets the number from cell range
  var format = 0;
  var length = n.toString().length;
  var result = "";
  if(length<=2 && length>0){//if input number length is 1 or 2
    format = 3;//formats number to 000
    var z = format - length;
    for(var x = 0; x < z; x++){
      result = result + "0";
    }
    return result+n.toString();
  }else if(length>2){//if input number length is more than 2
    format = 7;//formats number to 0000000
    var z = format - length;
    for(var x = 0; x < z; x++){
      result = result + "0";
    }
    return result+n.toString();
  }else{
    return "Empty cell detected!";//Shows a message when there's no number input
  }
}

When you input a number with a length of 1, the result of the custom function will be in "000" format as seen here:

enter image description here

If you input a length of more than 2, the result result will be in "0000000" format, as seen here:

enter image description here

Upvotes: 1

Jason E.
Jason E.

Reputation: 1221

I have come up with a code that might solve your problem. Please see below:

function myFunction() {
  sheet = SpreadsheetApp.getActiveSheet().getRange(1,1)
  sheet.setNumberFormat('000000')
  sheet.setValue('1')
}

What it does is pretty simple. It sets the number format for cell A1 and everytime you input a value in that cell, it will have the format that this code set for that cell. Please see below result:

enter image description here

Upvotes: 0

Related Questions