setting alternate columns number format

Question

I have a sheet where we have a product name in column A, row 3. In cells B1 and C1 we have the date and then in B2 stock and C2 price

           26/3/21         26/3/21

Thingymabob 3 5

I want to step along the columns C, E, G etc. until the last column formatting them to $#.##

I can't find a way to format column (index) so I've found an old bit of code on here that converts the index to a letter then I've tried to use that letter to say

"var column = sheet.getRange("C2:C")" "column.setNumberFormat("[$€]#,##0.00");"

No joy though. Here's the function, please help!

function formatPriceColumns() {
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = spreadsheet.getSheetByName("PriceHistory");
 var temp = ""
 var letter = "";
 var lastCol = sheet.getLastColumn();
 var lastRow = sheet.getLastRow();

 for(i = 3; i <= lastCol; i = i + 2) { 
    temp = (i - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    i = (i - temp - 1) / 26;

    var needString = "\"" + letter + "3" + ":" + letter + lastRow + "\""

    var column = sheet.getRange(needString) 
    column.setNumberFormat("[$€]#,##0.00");
  }
}

Chad Valentine Chad Valentine · Accepted Answer · 2021-03-26T22:20:29

Well there were a number of issues!

Using i twice was the least among them. Also the scope fo the variables and most importantly, not passing a string.

The corrected code looks like this:

function formatColumns() {
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = spreadsheet.getSheetByName("PriceHistory");
 var lastCol = sheet.getLastColumn(); 
 var lastRow = sheet.getLastRow();

for(i = 5; i <= lastCol; i = i + 2) {
 var temp = ""
 var letter = ""
 var column = 0
 column = i
 temp = (column - 1) % 26;
 letter = String.fromCharCode(temp + 65) + letter;
 var column = sheet.getRange(letter + "3:" + letter + lastRow)
 column.setNumberFormat("[$€]#,##0.00");
 }
}

setting alternate columns number format

1 Answers