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 coln = SpreadsheetApp.getUi().prompt("Read duplicate from a column e.g. A = 1, B = 2 ...").getResponseText();
var column = coln;
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] != ""){
duplicates.push(sortedData[i]);
}
}
//Logger.log(duplicates)
return duplicates;
}
//============================get location indexes of duplicated values=====================================
function getIndexes(data,duplicates){
var col = SpreadsheetApp.getUi().prompt("Highlight a column, e.g. A = 1, B = 2 ...").getResponseText();
var column = col;
var indexes = [];
i = -1;
for (var n = 0; n < duplicates.length; n++){
while ((i = data.indexOf(duplicates[n], i + 1)) != -1){
indexes.push(i);
}
}
//Logger.log("indexes:" + indexes);
for (var n = 0; n < indexes.length; n++){
sheet.getRange(indexes[n] + 1, column).setBackground("#858383")
}
}
//=========================================main=================================================
function main() {
var data = readData();
var duplicates = findDuplicates(data);
getIndexes(data,duplicates);
}
//=============================================split============================================
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
var cellRange = sheet.getActiveCell();
console.log(cellRange)
function splitc(cellRange) {
var output = [];
for(var i in cellRange) {
var split = cellRange.split(",");
output.push([split[i]]);
}
return output;
}
//==============================================================================================
Highlight Duplicate of a column based on column number
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
function readData() {
var coln = SpreadsheetApp.getUi().prompt("Read duplicate from a column number, e.g. A = 1, B = 2 ...").getResponseText();
var column = coln;
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] != ""){
duplicates.push(sortedData[i]);
}
}
//Logger.log(duplicates)
return duplicates;
}
//================================get location indexes of duplicated values=====================================
function getIndexes(data,duplicates){
var col = SpreadsheetApp.getUi().prompt("Highlight a column number, e.g. A = 1, B = 2 ...").getResponseText();
var column = col;
var indexes = [];
i = -1;
for (var n = 0; n < duplicates.length; n++){
while ((i = data.indexOf(duplicates[n], i + 1)) != -1){
indexes.push(i);
}
}
//Logger.log("indexes:" + indexes);
for (var n = 0; n < indexes.length; n++){
sheet.getRange(indexes[n] + 1, column).setBackground("violet")
}
}
//=========================================main=================================================
function main() {
var data = readData();
var duplicates = findDuplicates(data);
getIndexes(data,duplicates);
}