Search code examples
google-apps-scriptgoogle-sheetsleading-zero

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

Solution

  • 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