Add leading zeroes in custom function in Google Apps Script

Question

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;
 }

SputnikDrunk2 · Accepted Answer · 2021-04-01T21:35:31.513

1

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:

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

edited Apr 01 '21 at 21:35

answered Apr 01 '21 at 17:58

SputnikDrunk2

3,398
1
5
17

Thank you! This works. I have a question though, if I wanted to modify to change the number of leading zeroes, would I just have to set var format to the number of zeroes I want? – Geo Apr 01 '21 at 18:02
Awesome, you're very welcome. Yup, just change the "Format" variable to any number of zeros you want, it will work. – SputnikDrunk2 Apr 01 '21 at 18:05
Sorry for the newb questions, but how could I get the return to format the input as "000" if var length is 1 or 2? – Geo Apr 01 '21 at 18:53
No problem. Just to clarify, do you mean if the format is "000" and then you type an input 1 or 2? If it is, it will still work and will show you the result 001 or 002. That's when you change the var format on the code to 3 (to indicate 000 format). – SputnikDrunk2 Apr 01 '21 at 19:03
I've updated my answer above for more reference. Just let me know if you have any additional clarifications. – SputnikDrunk2 Apr 01 '21 at 19:15
I understand that part. What I'm asking is for the formula to return "000" if the length of the input is 1 or 2, but return "0000000" if the length of the input is anything from 3 to 7. e.g. 1 = 001, 11 = 011, 111 = 0000111, 1111 = 0001111, etc. Thanks in advance for the help, it's greatly appreciated. – Geo Apr 01 '21 at 19:21
Geo, I've updated my code above. It will format the number based on the length of the input number. – SputnikDrunk2 Apr 01 '21 at 21:29
Works perfect! Thanks so much! I'd upvote your post if I could, unfortunately I'm only at a 13 reputation level:( – Geo Apr 01 '21 at 22:11
Ran into another issue, I can't seem to use any substring method in my custom function, I get an error. If you don't want to answer, I can just ask community, since it's off topic of my tags. – Geo Apr 01 '21 at 22:53
My apologies. Since it's another issue, please create a new post for it instead. This is to ensure every specific issues are properly addressed. – SputnikDrunk2 Apr 01 '21 at 23:22

score 0 · Answer 2 · answered Apr 01 '21 at 17:27

0

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:

answered Apr 01 '21 at 17:27

Jason E.

1,201
1
3
10

Not quite what I'm looking for, I want to be able to use it in a custom function like a formula. I can't get the.setNumberFormat to work in my custom function. – Geo Apr 01 '21 at 17:58

Add leading zeroes in custom function in Google Apps Script

2 Answers2