Furthering Google Spreadsheet Validation through Scripting

In my last post, I provided a script for checking a Google Spreadsheet column in real time, when a given cell is edited.

This script provides the ability to check a set of pre-defined columns on an entire sheet quickly and programmatically. I have only tested it with a few dozen records, so I’m not sure how well it performs with thousands of records, but I have a feeling I’m going to find out in a few weeks from now!

Enjoy!

// function to validate necessary columns in current sheet
function validateThisSheet()
{
var sheet = SpreadsheetApp.getActiveSheet();
var sheet_name = sheet.getName();
var sheet_cols = [];
var column_description = [];
//this is just ridiculous...
var two = 2;
// Now set the appropriate columns for the given sheet /mrg
switch (sheet_name)
{
case "Computers":
sheet_cols = [1, 2, 3, 5];
column_description = ["int", "int", "string", "string"];
break;
case "Monitors":
sheet_cols = [1, 2, 3, 5];
column_description = ["int", "int", "string", "string"];
break;
case "Peripherals":
sheet_cols = [1, 2, 3, 4];
column_description = ["int", "int", "string", "string"];
break;
case "Printers":
sheet_cols = [1, 2, 3, 5];
column_description = ["int", "int", "string", "string"];
break;
case "Location":
sheet_cols = [1, 2, 3, 5];
column_description = ["int", "int"];
break;
}
Browser.msgBox("Begin Validation \r\n Please be patient, \r\n this can take a few minutes.");
// Loop through the columns /mrg
for( i=0; i 0 )
{
var current_cell = col_range.getCell(current_data_row*1-1 , 1);
current_cell.setBackground("orange");
errors_found = 0;
}
}
}
}
// Add sheet check to menu
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Validate This Sheet",
functionName : "validateThisSheet"
}];
sheet.addMenu("VALIDATION", entries);
};