Search code examples
google-apps-scriptgoogle-sheetsnumber-formatting

What is VBA's format function equivalent in Google Scripts/Sheets?

I'm having some trouble to achieve next VBA code's goal in Google Scripts (changing the number format of a variable adding zeros at the left up to figures)

'VBA CODE
sub addingZeros()
  dim num1 as double
 'Cell A1's value is 1, so I get 0001 
  num1 = format(thisWorkbook.Sheets("MySheet").range("A1"),"0000")
end sub

When I was researching, everything pointed out to use the setNumberFormat function, which is not a solution for me, because makes me to change the number in the sheet and I need only to change the variable value for further purposes... There was only one different solution in those I found, which uses a text function but I couldn't make it work (link for that solution)

I'd like a code similar to this:

//Google Scripts CODE
function myFunction() {
  var ss = SpreadsheetApp.openById(`XXXXXXXXXXXXX`);
  var sheet = ss.getSheetByName('MySheet');
  var num1 = format(sheet.getRange('a1').getValue(),"0000");
  //Cell A1's value is 1, so I'm expecting to get 0001 
  Logger.log(num1);
};

All the help will be much appreciated. Antonio

Solution

  • I believe your goal as follows.

    • You want to retrieve a value from a cell and you want to convert the format from 1 to 0001.
    • You want to achieve this using Google Apps Script.

    Pattern 1:

    In this pattern, Utilities.formatString is used.

    Sample script:

    function myFunction() {
  var ss = SpreadsheetApp.openById(`XXXXXXXXXXXXX`);
  var sheet = ss.getSheetByName('MySheet');

  var res = Utilities.formatString("%04d", sheet.getRange("A2").getValue()); // Modified

  Logger.log(res)
}
    • In this modification, the value of 1 is retrieved from the cell "A1" and the format is converted to 0001 using Utilities.formatString.

    Pattern 2:

    In this pattern, setNumberFormat is used.

    Sample script:

    function myFunction() {
  var ss = SpreadsheetApp.openById(`XXXXXXXXXXXXX`);
  var sheet = ss.getSheetByName('MySheet');

  var res = sheet.getRange("A1").setNumberFormat("0000").getDisplayValue(); // Modified

  Logger.log(res)
}
    • In this modification, the format of cell "A1" is changed using setNumberFormat and retrieved the display value using getDisplayValue.

    Note:

    • These modified scripts are the simple modification. So please modify them for your actual situation.

    References: