Wednesday, 18 September 2013

How to apply a loop on a script for multiple/various sheet names in Google spreadsheet?

How to apply a loop on a script for multiple/various sheet names in Google
spreadsheet?

I'm still learning GoogleApp scripting. Can anyone guide me in the right
direction how to apply the same codes on a Google spreadsheet with
multiple sheets with different sheet names? Maybe I need a script for a
loop?
Thank you for your help in advance!
This is the script I have so far:
function MakeRowGray() {
var sheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var columnD = sheet.getRange(2, 2, sheet.getLastRow()-1, 1); // Row B
var dValues = columnD.getValues();
var columnE = sheet.getRange(2, 3, sheet.getLastRow()-1, 1); // Row C
var eValues = columnE.getValues();
for (var i = 2; i < dValues.length + 2; i++) {
if (dValues[i-2][0].toUpperCase() == 'Y' &&
eValues[i-2][0].toUpperCase() == 'Y') { // Checks for 'Y' in both D
and E columns (Participated & Received)
// If they're both yes, make them gray...
sheet.getRange(i, 1, 1, 7).setBackgroundColor("#CCCCCC"); // Make
A through H gray
}
else if (dValues[i-2][0].toUpperCase() == 'Y' &&
eValues[i-2][0].toUpperCase() != 'Y' &&
eValues[i-2][0].toUpperCase() != 'W' &&
eValues[i-2][0].toUpperCase() != 'W?') // IN PROGRESS CODE --
MAKE ROW BLUE??
{
sheet.getRange(i, 1, 1, 7).setBackgroundColor("#AAAAFF"); // Make A
through H blue
}
else if (dValues[i-2][0].toUpperCase() == 'Y' &&
eValues[i-2][0].toUpperCase() == 'W?') // Not sure if Waiting or not
(W?)
{
sheet.getRange(i, 1, 1, 7).setBackgroundColor("#FFBB00"); // Make A
through H slightly orange
}
else if (dValues[i-2][0].toUpperCase() == 'X' &&
eValues[i-2][0].toUpperCase() == 'X') {
sheet.getRange(i, 1, 1, 7).setBackgroundColor("#FF0000"); //
Red
}
else if (dValues[i-2][0].toUpperCase() == 'Y' &&
eValues[i-2][0].toUpperCase() == 'W') {
sheet.getRange(i, 1, 1, 7).setBackgroundColor("#FFFF00"); //
Yellow
}
else
{ // Reset...
sheet.getRange(i, 1, 1, 7).setBackgroundColor("#FFFFFF");
}
}
};

No comments:

Post a Comment