Last active
October 20, 2020 21:24
-
-
Save psidex/d19c5a166d8538491f7cf4b364ec6813 to your computer and use it in GitHub Desktop.
Custom Google sheets script for highlighting my & my friends movie ratings
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// The columns that will contain opinions (emoji). | |
const opinionColumns = [16, 17, 18, 19, 20]; | |
// The columns to change the colour of. | |
const toColourColumns = [15, 16, 17, 18, 19, 20]; | |
// The weights given to each individual opinion. | |
const opinionWeights = { | |
"😍": 6, | |
"👍": 3, | |
"🤔": 1, | |
"😤": 0 | |
}; | |
// The colours to use. | |
// The number of colours must match up to all possible opinion averages (the opinion avg is rounded). | |
const opinionAvgColours = [ | |
"#ffffff", // 0 | |
"#ffffff", // 1 | |
"#ffffff", // 2 | |
"#c7f0c7", // 3 | |
"#87e187", // 4 | |
"#228B22", // 5 | |
"#39ff14" // 6 | |
]; | |
// The row that the reviews start on. | |
const startingRow = 7; | |
// Update the colour for a specific row. | |
// Returns the opinion average for the row. | |
function updateRowColour(sheet, rowNum) { | |
const opinionCells = sheet.getRange(rowNum, opinionColumns[0], 1, opinionColumns.length); | |
let opinionTotal = 0; | |
let opinionCount = 0; | |
const values = opinionCells.getValues()[0]; | |
values.forEach((val) => { | |
if (val in opinionWeights) { | |
opinionCount += 1; | |
opinionTotal += opinionWeights[val]; | |
} | |
}); | |
const toColourCells = sheet.getRange(rowNum, toColourColumns[0], 1, toColourColumns.length); | |
const opinionAvg = Math.round(opinionTotal / opinionCount); | |
toColourCells.setBackground(opinionAvgColours[opinionAvg]); | |
return opinionTotal; | |
} | |
// Run whenever an edit is made to the sheet. | |
function onEdit(e) { | |
// Will only edit colours if the left most edited cell is within the toColourColumns range. | |
if (e.range.getRow() >= startingRow && toColourColumns.includes(e.range.getColumn())) { | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
updateRowColour(sheet, e.range.getRow()); | |
} | |
} | |
// Iterates over all rows and updates the colours. | |
// If 3 rows with an opinion avg of 0 occur one after the other it stops (prevents it going on forever). | |
function updateAllColours() { | |
const sheet = SpreadsheetApp.getActiveSheet(); | |
blankRows = 0; | |
currentRow = startingRow; | |
while (blankRows <= 3) { | |
const rowOpinionAvg = updateRowColour(sheet, currentRow); | |
if (rowOpinionAvg == 0) { | |
blankRows += 1 | |
} else { | |
blankRows = 0 | |
} | |
currentRow += 1; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment