Setting Cell Format in Google Sheets using Apps Script

In previous posts I’ve discussed using conditional formatting to customize the format of cells. Sometimes, though, you may want to do this in code – perhaps as part of a larger and more complex routine.

You can change the font and background colours of cells in your spreadsheet using the setFontColor and setBackground methods acting on a given range in your sheet.

Here’s a simple example function to demonstrate their use:

function setCellColors(range, foregrd, backgrd) {
   var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the current spreadsheet.
   var sheet = ss.getSheets()[0];                  // Select the first sheet.
   var cell = sheet.getRange(range);               // Use supplied arguments
   cell.setFontColor(foregrd);                     // to set font and
   cell.setBackground(backgrd);                    // background colours.
 }

Let’s try that out with an example spreadsheet:

Click image to enlarge

Click image to enlarge

Here we want to set the colours of the six cells of the range B2:C4. Start by opening the script editor, clearing the contents, and adding our new function. If you’re unfamiliar with the script editor, you might want to first review this post.

In this example we’re going to use the opening of the spreadsheet to trigger our routine (see this post if that doesn’t mean very much to you). I’ve chosen to display the given range in white text on a red background:

function onOpen() {
   setCellColors("B2:C4", "white", "red");
}

Here’s the editor with all the code present:

Click image to enlarge

Click image to enlarge

With everything saved I can now re-open (or simply refresh) the sheet, and wait a moment for the script to run:

Click image to enlarge

Click image to enlarge

Of course, the font colour and background colour are just two of the many options you can set. Here are just a few of the properties of a given range that you can set in a similar manner (for a full list, see Google’s documentation):

setBorder(top, left, bottom, right, vertical, horizontal) Sets the border property.
setFontFamily(fontFamily) Sets the font family, such as “Arial” or “Verdana”.
setFontLine(fontLine) Sets the line style of the given range (“underline” or “line-through” or “none”).
setFontSize(size) Sets the font size in points.
setFontStyle(fontStyle) Set the font style for the given range (‘italic’ or ‘normal’).
setFontWeight(fontWeight) Set the font weight for the given range (“normal” or “bold”).
setHorizontalAlignment(alignment) Set the horizontal (left to right) alignment for the given range (“left” or “center” or “right”).
setNumberFormat(numberFormat) Sets the number or date format to the given formatting string.
setVerticalAlignment(alignment) Set the vertical (top to bottom) alignment for the given range (“top” or “middle” or “bottom”).