Google Spreadsheet: Script to Change Row Background Color on Cell Edit

Now for something a little different! Google Spreadsheets!

Here is a little script I cooked up today for changing a row color in Google Spreadsheet when you change the contents of a drop down menu.

When you change the contents of a cell in the designated "status" column the entire row will change it's background color to match whatever rule you have defined in the switch statement.

A useful addition to this would be the ability to automatically find the status column in whatever sheet you have open, but it was beyond the scope of what the script was meant for, so here is it in the raw!

/**
Google Sheets Script to modify a row's color based on the 'Status' entry. 

This script uses the onEdit() method, which is called every time a cell is edited.

The statusColumn variable's cells are assumed to be a drop-down with the following options:
Complete
In Progress
Not Started
Revisions Req
Blocked

The script modifies a row's color based on the contents- leaving the color white if it's not matched by an expected type.
*/

function onEdit() {

    var sSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var statusColumn = 8; // Status column index

    // As this is called onEdit() we don't want to perform the entire script every time a cell is
    // edited- only when a status cell is mofified. 
    // To ensure this, before anything else we check to see if the modified cell is actually in the status column.
    if (sSheet.getActiveCell().getColumn() == statusColumn) {
        var row = sSheet.getActiveRange().getRow();
        var value = sSheet.getActiveCell().getValue();
        var col = "white"; // Default background color
        var colLimit = 15; // Number of columns across to affect

        switch (value) {
            case "Complete":
                col = "MediumSeaGreen";
                break;
            case "In Progress":
                col = "Orange";
                break;
            case "Not Started":
                col = "DarkGray";
                break;
            case "Revisions Req":
                col = "Gold";
                break;
            case "Blocked":
                col = "LightCoral";
                break;
            default:
                break;
        }

        sSheet.getRange(row, 1, 1, colLimit).setBackground(col);
    }
};

No comments:

Post a Comment

Comments?