Format Cells with Apps Script

Apps Script provides the full Google Sheets formatting API through the Range object. You can set background colors, font styles, borders, alignment, and more — on individual cells or entire ranges at once.

Formatting the Header Row

function formatHeaderRow() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var header = sheet.getRange(1, 1, 1, 8); // Row 1, columns A–H

  header
    .setBackground("#1a73e8")
    .setFontColor("#ffffff")
    .setFontWeight("bold")
    .setFontSize(11)
    .setHorizontalAlignment("center")
    .setVerticalAlignment("middle");

  sheet.setRowHeight(1, 36);
  Logger.log("Header formatted.");
}

Color-Coding Rows by Deal Status

function colorCodeByStatus() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 8).getValues();

  data.forEach(function(row, i) {
    var status = row[6]; // Column G
    var rowRange = sheet.getRange(i + 2, 1, 1, 8);

    if (status === "Closed") {
      rowRange.setBackground("#c6efce"); // Green
    } else if (status === "In Progress") {
      rowRange.setBackground("#ffeb9c"); // Yellow
    } else if (status === "Pending") {
      rowRange.setBackground("#fce4d6"); // Orange
    } else {
      rowRange.setBackground(null);
    }
  });

  Logger.log("Rows color-coded by status.");
}

With the sample data, Sarah Johnson and Lisa Park get green rows, while Mark Chen gets yellow.

Setting Borders

function addBordersToDataRange() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var dataRange = sheet.getRange(1, 1, sheet.getLastRow(), 8);

  dataRange.setBorder(
    true, true, true, true, // top, left, bottom, right
    true, true,             // vertical, horizontal inner borders
    "#cccccc",
    SpreadsheetApp.BorderStyle.SOLID
  );

  Logger.log("Borders applied.");
}

Bolding and Resizing Specific Columns

function styleAmountColumn() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  // Bold the Amount column header
  sheet.getRange("D1").setFontWeight("bold");

  // Right-align and bold all Amount values
  var amountRange = sheet.getRange(2, 4, sheet.getLastRow() - 1, 1);
  amountRange
    .setHorizontalAlignment("right")
    .setFontWeight("bold")
    .setFontColor("#1a73e8");

  // Auto-resize all columns
  sheet.autoResizeColumns(1, 8);

  Logger.log("Amount column styled.");
}

Full Dashboard Formatting Function

A single function that applies all formatting to the Sales Tracker:

function applyDashboardFormatting() {
  var sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Sales Tracker");

  var lastRow = sheet.getLastRow();

  // Header
  sheet.getRange(1, 1, 1, 8)
    .setBackground("#1a73e8")
    .setFontColor("#ffffff")
    .setFontWeight("bold")
    .setFontSize(11)
    .setHorizontalAlignment("center");

  sheet.setRowHeight(1, 36);

  // Data rows
  var data = sheet.getRange(2, 1, lastRow - 1, 8).getValues();
  data.forEach(function(row, i) {
    var bg = row[6] === "Closed" ? "#c6efce"
           : row[6] === "In Progress" ? "#ffeb9c"
           : "#fce4d6";
    sheet.getRange(i + 2, 1, 1, 8).setBackground(bg);
  });

  // Borders
  sheet.getRange(1, 1, lastRow, 8).setBorder(
    true, true, true, true, true, true, "#cccccc", SpreadsheetApp.BorderStyle.SOLID
  );

  // Auto-resize
  sheet.autoResizeColumns(1, 8);

  // Freeze header row
  sheet.setFrozenRows(1);

  Logger.log("Dashboard formatting applied.");
}