App Script to change font in google sheets- All tabs.

[Game India]

Hi,

Can anyone help me with App Script to change font in google sheets- All tabs( etc: 5 to 10 tabs) at once.

Thanks in advance

[Clark Lind]

Hello,  this little snippet will do the job. 
-Get the active Spreadsheet
-Get all tabs in the sheet
-iterate over the data range of each tab, changing the font-family and font-size.  You will have to change these to your own needs. 

-----------------------------------

function changeGlobalFont() {

  var tabs = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  tabs.forEach( (tab) => { 
   //this will change all tabs to a specific font and size. 

        tab.getDataRange().setFontFamily('Calibri').setFontSize('12')    //change the font family and size to your own needs

   //or to completely remove all formatting, including any cell colors

        tab.getDataRange().clearFormat()

  })

}

[Game India]

Thank you very much. I'm curious to learn google app script. Can u pls suggest any course or sites to learn this from basic.

[Clark Lind]

Apps script is basically javascript. But with some differences and methods that allow you to access Google products from within the products (Sheets, Docs, Slides, etc..) or that allow you to access Google services (Gmail, Drive, Calendar, etc).
Here are some Youtube channels with some very good content for learning Apps script and what it can do.

https://www.youtube.com/c/JordanRhea
https://www.youtube.com/c/LearnGoogleSpreadsheets
https://www.youtube.com/c/LaurenceSvekisCourses
https://www.youtube.com/c/AshtonFei

[Game India]

Thank you very much it is helpful. 

[Rock]

Hi,

Can u please help me how to use below script(Change font) for specific url provided.

function changeGlobalFont() {

  var tabs = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  tabs.forEach( (tab) => { 
   //this will change all tabs to a specific font and size. 

        tab.getDataRange().setFontFamily('Calibri').setFontSize('12')    //change the font family and size to your own needs

   //or to completely remove all formatting, including any cell colors

        tab.getDataRange().clearFormat()

  })

}

Ex: 

Insert Url: https://docs.google.com/spreadsheets/d (Spreadsheet url)

Button: Change Font

(font should change in all the tab for Spreadsheet url provided)

Thanks in advance

[smail pouri]

Hi!

Thats great! 

Do you have a way to make this work only on specific tabs at once and a specific range? 

I manage to make my code work on specific tabs but it only runs if I trigger it manually on one TAB at time if launched from one of the TAB from selectedSheets

I want to run this on a Timed Trigger on All selectedSheets  on a specific range, since I dont want to included the first 3 row headers. 

In my example the range is Starts at A4:AQ and the last column should dynamically update with width since columns will be added.

function FixFont(){

var selectedSheets = ["Sheet1","Sheet3","Sheet6"]; // select the sheets you want to run the function for

var sheets = SpreadsheetApp.getActive().getSheets(); // get all sheets

var sheet = SpreadsheetApp.getActiveSheet();

var lastRow = sheet.getLastRow();

var width = sheet.getLastColumn();

//var headers = sheet.getRange(1, 1, 1, width).getValues()[0];

//Logger.log(headers);

if (selectedSheets.includes(sheet.getName() )){

var rangeData = sheet.getRange(4,1,lastRow-1,width);

var rangeA1 = rangeData.getA1Notation();

Logger.log(rangeA1);

var range = sheet.getRangeList([rangeA1]);

range.setFontFamily("Arial")

.setFontSize(8)

SpreadsheetApp.flush();

};

}

Any help is appreciated, I'm pretty novice on script writing. 

Thx!

[cbmserv...@gmail.com]

How about this approach? Make it a simple onEdit trigger and if anything is changed in the sheet, then set them to the right font and size right away.

 

Here is what the script would look like:

 

function onEdit(e)

{

  var selectedSheets = ["Sheet1","Sheet3","Sheet6"]; // select the sheets you want to run the function for

  var sheet = SpreadsheetApp.getActiveSheet();

  var row = e.range.getRow();

  var col = e.range.getColumn();

  var sheetName = sheet.getName();

  if ((selectedSheets.includes(sheetName)) && (row > 3))

  {

    sheet.getRange(row,col).setFontFamily("Arial").setFontSize(8)

  };

}

 

If spreadsheet entry happens from non-manual steps, then onEdit would not work and a timed based change would be ok. Here is how you would do it to force change all fonts on all sheets to the right one you want:

 

function timeTrig()

{

  var selectedSheets = ["Sheet1","Sheet3","Sheet6"]; // select the sheets you want to run the function for

  var ss = SpreadsheetApp.getActiveSpreadsheet();

 

  for (var i=0;i<selectedSheets.length;i++)

  {

    sheet = ss.getSheetByName(selectedSheets[i]);

    sheet.getRange(4,1,sheet.getLastRow(),sheet.getLastColumn()).setFontFamily("Arial").setFontSize(8)

  }; 

}

 

Setup the timeTrig function as a trigger and make it run at whatever frequency you choose. Daily, hourly, etc..

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/469c22f7-d17a-43e8-9d29-cf86148b43f9n%40googlegroups.com.

[smail pouri]

Thank you George,

Appreciate the time you spend on this! they both work. 

The idea of onEdit, I don't think this will work for my case. The sheet I want to run this on, for some odd reason is very slow. 

The sheet has a few tabs and formula's intertwined, seems like this make the sheet run slowly. (vlookup and query, conditional formatting ...)

I have other people entering data by means of manual entry and copy pasting from other places, so the onEdit is not the best option, as im afraid it would slow down the sheet even more. 

But I just ran the script timeTrig, its prefect! I can run in in the middle of the night when everyone is asleep!

NOTE:

Weirdly, it give me an error when I use the Debug, but when I run it, it just works. same with the onEdit(e) function, debugs returns an error, but the scripts works. 🤷‍♂️

Thx!

[CBMServices Web]

Up to you which works best for your situation. If the spreadsheet is already slow, I can see rationale for the nightly format instead. But you probably should look at speeding it up as well to make manual entry more efficient.

Yea, the onEdit function will not work when run under debug because it depends on the e variable that the trigger will pass it. The e object provides data on the trigger event such as which sheet, which cell was modified. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/79011c83-f919-4e0c-a012-346773698d06n%40googlegroups.com.

[cbmserv...@gmail.com]

One other comment on function timeTrig. It expects the listed sheets to be in the spreadsheet and named exactly as in the function (Sheet1, etc..).

 

If those sheets are not there, it will give you an error as it fails to find that sheet in the spreadsheet. A quick if statement can bypass that error if you want.

 

Just add (before the setFontStyle line)

 

If (sheet == null ) {continue;}

 This will just skip that sheet if it does not exist in the spreadsheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/79011c83-f919-4e0c-a012-346773698d06n%40googlegroups.com.

[smail pouri]

thank you!!

[game over]

how to find google sheets id