Using Google SpreadSheets often? Install this Script to make repetitive tasks quicker
A “Handy Tools” menu will be added to your Menu (as per image above)
//Just a few handy scripts to make Google SpreadSheets nicer to work with. JaimeJCandau
// Add a custom menu to an spreadsheet when it opens
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('HandyTools')
.addItem('Hide Empty Rows', 'hideEmptyRows')
.addItem('Show All Rows', 'showAllRows')
.addItem('Make Top Row Heading', 'makeTopRowHeading')
.addItem('Zebra Selected Range', 'zebraColors')
.addToUi();
}
//Function to Hide All Empty Rows on the Active sheet JaimeJCandau
function hideEmptyRows(){
//Getting the active Sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(1, 1, sheet.getLastRow());
//Hide the Empty Rows in Column A from that sheet
var values = range.getValues();
//Reading row by row
for (var i=0; i<values.length; i++){
//IF Row value is Empty
if(values[i][0] === ""){
//THEN hide row
sheet.hideRows(i+1);
}
}
}
//Funciont ShowAllRows: UnHides all rows on the active sheet JaimeJCandau
function showAllRows(){
//Getting the active Sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange(1, 1, sheet.getLastRow());
//UnHide rows
sheet.showRows(1,sheet.getLastRow());
}
//Function MakeTopRowHeading: Set the top row as Black and White Font, handy for reports, etc... JaimeJCandau
function makeTopRowHeading(){
//Getting the Range
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange("1:1");
range.activate();
//Applying the new format
range.setBackground("black");
range.setFontColor("white");
range.setFontWeight("bold");
range.setVerticalAlignment("middle");
range.setWrap(true);
sheet.setFrozenRows(1);
sheet.setRowHeight(1, 40);
range.setHorizontalAlignment("center");
}
//Zebra coloring the selected range
function zebraColors() {
//Getting the range
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange("1:1");
range.activate();
var totalRows = SpreadsheetApp.getActiveRange().getNumRows();
var totalColumns = SpreadsheetApp.getActiveRange().getNumColumns();
var startRow = SpreadsheetApp.getActiveRange().getRow();
var startColumn = SpreadsheetApp.getActiveRange().getColumn();
var row = startRow;
//Recurring looking at range to define color
while (row < totalRows+startRow)
{
var column = startColumn
while (column < totalColumns+startColumn){
if(row%2 == 0){
sheet.getRange(row, column).setBackgroundColor("lightgrey"); //Change the color here, you can also use HEX codes
}
column++;
}
row++;
}
}
Pd – These are the ones I use frequently, if there is anything else you could think off, leave and comment, and I’ll add (provided my limited coding skills allow!!!!)
