Script to set default font size and style in google sheets

user_7754040 0
Viewed 4930

I have a shared google sheet where people constantly paste new data in however the data is always in different styling and restyling it is very time consuming. I was wondering if it it's possible with a script that auto correct styling and size in a document.

google-apps-script google-sheets
2 Answers

The following script will reset the formatting on the edited range. You can further validate the row/column/sheet before resetting the formatting (eg. to prevent header rows from getting affected) using the values of e.range or e.range.getSheet()

function onEdit(e) {
  e.range.setFontColor()  // Resets color
    .setFontSize(10)      // Sets size to 10 (has to be a number)
    .setFontFamily()      // Resets font family
    .setFontLine()        // Resets underline, strikethrough
    .setFontWeight()      // Resets bold
    .setFontStyle()       // Resets italics
}

Instead of onEdit, some other options include

  • making a timed trigger that routinely resets the formatting
  • running a script manually when you would like to reset formatting

Refer to Range class.

user_82362330

If you are looking at resetting it to the default theme then you can use this script:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var range = ss.getRange(1, 1, ss.getMaxRows(), ss.getMaxColumns());
  range.clearFormat();
}

This triggers every-time a user edits the sheet (including pasting some data) and clears all formats in the sheet. If this doesn't work for you in case you have specific formats for some cells, like data headers that you don't want to change, then you can customise the script using methods like setFontSize(size) etc.

Edit: Changed the answer for the specific spreadsheet.

Use this script

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSheet();  
  var range = ss.getRangeList(['B29:B42', 'B27']);
  range.setFontFamily("Arial")
       .setFontSize(11)
       .setFontColor("#000000")
       .setBackground("#ffffff");
}
user_46699050
Related