Google-sheets – How to apply formatting on specific Google Sheets and columns

google sheetsgoogle-apps-script

I have a Google Spreadsheet with many sheets. I want to apply (through code) formatting to specific columns to the sheets I specify.

I have tried this but my code applies the formatting to every sheet in my spreadsheet and its causing problems. I have a file named FormatColumns.gs Here are the contents of the file

function formatDate(){
  
 var ss = SpreadsheetApp.getActive();
 var sheet = ss.getActiveSheet();
  
  sheet.getRange("B2:B").setNumberFormat('M/dd/yyyy');
  sheet.getRange("A2:A").setNumberFormat("$#,##0;$(#,##0)");
  
 };

How can I specify which sheets get the above formatting?

For example:

sheet.getRange("B2:B").setNumberFormat('M/dd/yyyy'); gets applied to ONLY sheet1
(or whichever sheets I specify)

and

sheet.getRange("A2:A").setNumberFormat("$#,##0;$(#,##0)"); gets applied to ONLY sheet2

I THINK I need to use Boolean logic and or use getSheetByName("Sheet1"); …..

**Here is an example page with permissions everyone should be able to use **
https://docs.google.com/spreadsheets/d/1Cn5tEJHNF07n25g0yc4d0I-9J_hLUceeeIMIyULYgAo/edit?usp=sharing

Best Answer

Just sharing what I got to work for me. 

function FormatSheetColumns (){

 var as= SpreadsheetApp.getActiveSpreadsheet();
 var data1= as.getSheetByName("Sheet1");
 var data2= as.getSheetByName("Sheet2");

// Sheet 1  
 var range=data1.getRange("B2:B").setNumberFormat('M/dd/yyyy');
 var range=data1.getRange("E2:E").setNumberFormat("$#,##0;$(#,##0)");
 var range=data1.getRange("A2:A").setNumberFormat("$#,##0;$(#,##0)");

 //Sheet 2
 var range=data2.getRange("A2:A").setNumberFormat("$#,##0;$(#,##0)");
 var range=data2.getRange("B2:B").setNumberFormat('M/dd/yyyy');


}
Related Topic