var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
var searchRange = sheet.getRange(1,1, lastRow, lastColumn);
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Highlight')
.addItem('Brown', 'HighlightB')
.addItem('Green', 'HighlightG')
.addItem('Purple', 'HighlightP')
.addItem('Yellow', 'HighlightY')
.addItem('Pink', 'HighlightR')
.addItem('Aqua', 'HighlightAQ')
.addItem('Red', 'HighlightRe')
.addItem('Orange', 'HighlightOr')
.addItem('White', 'HighlightW')
.addItem('Split(C)', 'cellsplitting')
.addItem('Concat', 'cellconcat')
.addItem('Find_Col_Duplicate', 'main')
.addToUi();
};
function HighlightB() {
var ui = SpreadsheetApp.getUi();
// Get array of values in the search Range
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
var outpt = [];
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#cc9525");
outpt.push([sheet.getRange(j+1,i+1)]);
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
ui.alert("total:"+outpt.length);
};
function HighlightG() {
var ui = SpreadsheetApp.getUi();
// Get array of values in the search Range
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
var outpt = [];
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#25cc41");
outpt.push([sheet.getRange(j+1,i+1)]);
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
ui.alert("total:"+outpt.length);
};
function HighlightP() {
var ui = SpreadsheetApp.getUi();
// Get array of values in the search Range
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
var outpt = [];
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#caa5ff");
outpt.push([sheet.getRange(j+1,i+1)]);
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
ui.alert("total:"+outpt.length);
};
function HighlightY() {
var ui = SpreadsheetApp.getUi();
// Get array of values in the search Range
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
var outpt = [];
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#fff058");
outpt.push([sheet.getRange(j+1,i+1)]);
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
ui.alert("total:"+outpt.length);
};
function HighlightR() {
var ui = SpreadsheetApp.getUi();
// Get array of values in the search Range
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
var outpt = [];
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#ed9da3");
outpt.push([sheet.getRange(j+1,i+1)]);
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
ui.alert("total:"+outpt.length);
};
function HighlightAQ() {
var ui = SpreadsheetApp.getUi();
// Get array of values in the search Range
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
var outpt = [];
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#5ce1d7");
outpt.push([sheet.getRange(j+1,i+1)]);
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
ui.alert("total:"+outpt.length);
};
function HighlightRe() {
// Get array of values in the search Range
var ui = SpreadsheetApp.getUi();
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
var outpt = [];
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#d9313e");
outpt.push([sheet.getRange(j+1,i+1)]);
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
ui.alert("total:"+outpt.length);
};
function HighlightOr() {
var ui = SpreadsheetApp.getUi();
// Get array of values in the search Range
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
var outpt = [];
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#ff720b");
outpt.push([sheet.getRange(j+1,i+1)]);
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
ui.alert("total:"+outpt.length);
};
function HighlightW() {
// Get array of values in the search Range
var word = SpreadsheetApp.getUi().prompt("Please enter a word").getResponseText();
var rangeValues = searchRange.getValues();
// Loop through array and if condition met, add relevant
// background color.
for ( i = 0; i < lastColumn; i++){
for ( j = 0 ; j < lastRow; j++){
if(rangeValues[j][i].toString().includes(word)){
sheet.getRange(j+1,i+1).setBackground("#ffffff");
}//else if (rangeValues[j][i] === 0){
//sheet.getRange(j+1,i+1).setBackground("#e69138");
//};
};
};
};
//===============================================split=================================================================
function cellsplitting() {
var cellRange = sheet.getActiveCell();
var selectedColumn = cellRange.getColumn();
var selectedRow = cellRange.getRow();
var rg = SpreadsheetApp.getUi().prompt("Please type, e.g. A1...").getResponseText();
//Logger.log(`selectedColumn: ${selectedColumn}`);
//Logger.log(`selectedRow: ${selectedRow}`);
//Logger.log(`selected cell vale: ${cellRange.getValue()}`);
cellRange.setValue("=splitc("+rg+")");
}
//==============================================concat cells============================================================
function cellconcat() {
var cn = SpreadsheetApp.getUi().prompt("Please input a column e.g. A or B or ...").getResponseText();
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var cellRange = sheet.getActiveCell();
var to=('=textjoin(",",1,'+cn+'1:'+cn+')').toString()
cellRange.setValue(to);
}
//==============================================find duplicates from column===============================================================================
function readData() {
var lastRow = sheet.getLastRow();
var columnRange = sheet.getRange(1,column,lastRow);
var rangeArray = columnRange.getValues();
rangeArray = [].concat.apply([], rangeArray);
return rangeArray;
}
//====================sort data and find duplicate====================================================================
function findDuplicates(data){
var sortedData = data.slice().sort();
var duplicates = [];
for(var i = 0; i < sortedData.length - 1; i++){
if(sortedData[i + 1] == sortedData[i] && sortedData[i + 1] && sortedData[i] !== ""){
duplicates.push(sortedData[i]);
}
}
//Logger.log(duplicates)
return duplicates;
}
//============================get location indexes of duplicated values=========================================================
function getIndexes(data,duplicates,column){
var indexes = [];
i = -1;
for (var n = 0; n < duplicates.length; n++){
var color = getColor();
while ((i = data.indexOf(duplicates[n], i + 1)) != -1){
indexes.push(i);
sheet.getRange(i+ 1, column).setBackground(color)
}
}
//Logger.log("indexes:" + indexes);
//for (var n = 0; n < indexes.length; n++){
//var color = getColor();
//sheet.getRange(indexes[n] + 1, column).setBackground(color)
//}
}
//=========================================main=================================================================================
function main() {
column = getColumnLetter();
var data = readData(column);
var duplicates = findDuplicates(data);
getIndexes(data,duplicates,column);
}
//=======================================splitc===============================================================================
function splitc(cellRange) {
var output = [];
for(var i in cellRange) {
var split = cellRange.split(",");
output.push([split[i]]);
}
return output;
}
//========================================================================letterToInt=========================================================
function getColumnLetter() {
var coln = SpreadsheetApp.getUi().prompt("highlight duplicate from a column. For example, A, B, C").getResponseText();
let Letter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
let letter = "abcdefghijklmnopqrstuvwxyz"
var reg = RegExp(coln[0])
if(reg.exec(Letter)) {
var col = reg.exec(Letter).index + 1
}
if(reg.exec(letter)) {
var col = reg.exec(letter).index + 1
}
return col
}
//RGB 0-255
function getColor() {
var R = getRandomInt(128).toString(16);
var G = getRandomInt(128).toString(16);
var B = getRandomInt(128).toString(16);
if(R.length==1) {
var R = "0" + R;
};
if(G.length==1) {
var G = "0" + G;
};
if(B.length==1) {
var B = "0" + B;
};
var code = "#"+R+G+B;
return code;
};
function getRandomInt(max) {
return Math.floor(Math.random() * Math.floor(max)+128);
}