Google Sheets is a powerful tool commonly used for data analysis, reporting, and collaboration. Similar to Microsoft Excel, Google Sheets has its own unique set of functions, formulas, and features that can help you streamline your work and improve your productivity.

One such feature is formatting a range, which is essential when it comes to data visualization and presentation. In this blog post, we’ll show you how to format a range in Google Sheets using Apps Script and Google Functions.

Step 1: Create a new sheet and add some data

Before we can start formatting a range in Google Sheets, we need some data to work with. For this tutorial, we’ll use a sample data set that contains the names of four different fruits and their respective quantities.

To get started, create a new Google Sheets file, then add the following data to it:

FruitQuantity
Apple10
Banana20
Kiwi15
Orange25

Step 2: Format the values in the Quantity column

In order to format a range in Google Sheets, we need to use the setNumberFormat() function. This function is used to set the number formatting options for a particular range, cell, or column.

To format the values in the Quantity column as numbers with no decimal places, follow these steps:

  1. Select the Quantity column by clicking on the top cell and dragging down to the bottom cell.
  2. On the toolbar, click on ‘Format’ and then ‘Number’.
  3. In the ‘Number’ sidebar, choose ‘Number’ as the Category and set the Decimal places to 0.
  4. Click ‘Apply’ to format the selected cells.

Alternatively, you can use Apps Script to format the selected range programmatically. Follow these steps:

  1. Open the script editor by clicking on ‘Tools’ and then ‘Script editor…’.
  2. In the script editor, copy and paste the following code:
    function formatQuantity() {
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
        var range = sheet.getRange('B2:B5');
        range.setNumberFormat('0');
    }
  3. Save the script by clicking on ‘File’ and then ‘Save’.
  4. Run the script by clicking on the ‘Run’ button. This should format the Quantity column as numbers with no decimal places.

Step 3: Format the values in the Fruit column

Next, we’ll format the values in the Fruit column by applying a background color and changing the font style. To do this, we’ll use a combination of setFontColor(), setBackgroundColor(), and setFontWeight() functions.

Follow these steps to format the values in the Fruit column:

  1. Select the Fruit column by clicking on the top cell and dragging down to the bottom cell.
  2. On the toolbar, click on ‘Format’ and then ‘Conditional formatting’.
  3. In the ‘Conditional formatting’ sidebar, choose ‘Custom formula is’ as the Format cells if option.
  4. In the ‘Value or formula’ field, enter the following formula: =A2="Apple" Note: The formula should reference the top cell in the selected range (i.e., A2 in this case).
  5. Click on the color swatch next to ‘Background color’ and choose a color.
  6. In the ‘Font style’ section, choose a font color and font weight (e.g., bold).
  7. Click ‘Done’ to apply the formatting.

Alternatively, you can use Apps Script to format the selected range programmatically. Follow these steps:

  1. Open the script editor by clicking on ‘Tools’ and then ‘Script editor…’.
  2. In the script editor, copy and paste the following code:
    function formatFruit() {
        var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
        var range = sheet.getRange('A2:A5');
        var values = range.getValues();
    
        for (var i = 0; i < values.length; i++) {
            if (values[i][0] === 'Apple') {
                range.getCell(i + 1, 1).setFontColor('white').setBackgroundColor('red').setFontWeight('bold');
            }
        }
    }
  3. Save the script by clicking on ‘File’ and then ‘Save’.
  4. Run the script by clicking on the ‘Run’ button. This should apply the custom formatting to the Fruit column.