0

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

  • How are you calling the function? Have you already tried getSheetByName? – Rubén Jun 7 at 13:56
0

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');


}
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.