I'm looking to set a column to format 000, which will display the zeros at begenning.

So, if a cell displays "3", I want that the script will set it to display "003".

This column is located in BDD tab, 13th column starting from the second row.

function FormattingGpeTrait() {

  const sheet = SpreadsheetApp.getActiveSheet().getSheetByName("BDD").getRange(2,13)
  sheet.setNumberFormat('000')

CodePudding user response：

Modification points:

• The method of "getSheetByName" is for Class Spreadsheet. In your showing script, you try to use it to Class Sheet. By this, an error occurs. This has already been mentioned in the comment. Ref
• From 13th column starting from the second row., I thought that you might have wanted to set the number format of 000 to "M2:M". In your showing script, the number format is set to only a cell "M2".

If you want to set the number format to the cells "M2:M" of the sheet name of "BDD", how about the following modification?

Modified script:

function FormattingGpeTrait() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BDD");
  sheet.getRange("M2:M"   sheet.getLastRow()).setNumberFormat('000');
}

• When you run this script, the number format of "000" is set to the cells "M2:M" of "BDD" sheet.
• If you want to set the number format to the "M2:M", please modify getRange("M2:M" sheet.getLastRow()) to getRange("M2:M").

References:

• getActiveSpreadsheet()
• getSheetByName(name)

CodePudding user response：

The easiest way to get a range on a named sheet is to include the sheet name in the range reference, like this:

function formattingGpeTrait() {
  SpreadsheetApp.getActive().getRange('BDD!M3:M').setNumberFormat('000');
}

CodePudding user response：

I think that you cannt use the standard number formats as they all will only evaluate your value to a real number value where '003' in reality is equal to '3' from a numeric sense.

You have two real options which is to either store the value in a Text column as "003" or prepend the value with an apostrophe "'003" which is basically the same as storing it as Text but the column can remain numeric.

You can create a custom number format for a cell/column to also do this but I am not certain how to accomplish this programatically. Basically, this is still going to end up like the Text variations I mention above, only you have a named format you can call. The data will still be stored as Text.