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();
var menu2 = ui.createMenu("螢光'文字'")
.addItem('Red Font', 'Highlight_FontRed')
.addItem('Orange Font', 'Highlight_FontOrange')
.addItem('Yellow Font', 'Highlight_FontYellow')
.addItem('Green Font', 'Highlight_FontGreen')
.addItem('Aqua Font', 'Highlight_FontAqua')
.addItem('Blue Font', 'Highlight_FontBlue')
.addItem('Purple Font', 'Highlight_FontPurple')
.addItem('Pink Font', 'Highlight_FontPink')
.addItem('Flesh Font', 'Highlight_FontFlesh')
.addItem('Magenta Font', 'Highlight_FontMagenta')
.addItem('Brown Font', 'Highlight_FontBrown')
.addItem('Grey Font', 'Highlight_FontGrey')
.addItem('Black Font', 'Highlight_FontBlack')
menu2.addToUi();
}
function Highlight_FontBrown () {
set_Font_Colour("brown")
}
function Highlight_FontGreen () {
set_Font_Colour("green")
}
function Highlight_FontPurple () {
set_Font_Colour("purple")
}
function Highlight_FontYellow () {
set_Font_Colour("#999900")
}
function Highlight_FontPink () {
set_Font_Colour("#ff33ff")
}
function Highlight_FontAqua () {
set_Font_Colour("#29a3a3")
}
function Highlight_FontRed () {
set_Font_Colour("red")
}
function Highlight_FontOrange () {
set_Font_Colour("orange")
}
function Highlight_FontBlack () {
set_Font_Colour("black")
}
function Highlight_FontBlue () {
set_Font_Colour("blue")
}
function Highlight_FontFlesh () {
set_Font_Colour("#ffb3b3")
}
function Highlight_FontMagenta () {
set_Font_Colour("#cc0052")
}
function Highlight_FontGrey () {
set_Font_Colour("#737373")
}
function set_Font_Colour(colorCode) {
var word = SpreadsheetApp.getUi().prompt("Please enter the find word").getResponseText()
if(!word.trim().length)
return; ;
var start_time = parseInt(new Date().getTime())
var regWord = word.split("").map(item => /[\\`~!@#$%^*"'()_\-+?:{},.\/;[\]]/.test(item)? "\\"+item : item).join("")
var reg = new RegExp(regWord,"i")
var colorCodeArray = new Array(word.length).fill(colorCode, 0, this.length)
var lastRow = sheet.getLastRow()
var lastColumn = sheet.getLastColumn()
var range = sheet.getRange(1, 1, lastRow, lastColumn)
var allData = range.getDisplayValues()
var original_format = range.getNumberFormats()
var string_format = original_format.map(row => row.map(cell => '@'))
range.setNumberFormats(string_format);
var richTextValues = range.getRichTextValues()
//range.setNumberFormats(original_format)
//SpreadsheetApp.flush()
var Index = []
for(var column = 0; column < lastColumn; column++) {
for(var row = 0; row < lastRow; row++) {
if(reg.test(allData[row][column])){
Index.push([row, column])
}
}
}
data = Index.map(item => getTextStyle_in_cell(allData, richTextValues, item[0], item[1]))
var newTextStyle = data.map(style => findWordsAndCombineTextStyle(style, word, regWord, colorCodeArray))
var total = setTextStyle_in_cell(newTextStyle)
range.setNumberFormats(original_format)
var TimeUsed = (parseInt(new Date().getTime()- start_time)/1000).toString()
SpreadsheetApp.getUi().alert("Total: "+total+' for "'+word+'"\n時間用了'+time_used+"秒。")
}
function getTextStyle_in_cell(allData, richTextValues, row, column){
var obj = {}
obj.row = row
obj.column = column
obj.cellValue = allData[row][column]
obj.textlength = obj.cellValue.length
obj.color = new Array(obj.textlength)
obj.isBold = new Array(obj.textlength)
obj.isUnderline = new Array(obj.textlength)
obj.isItalic = new Array(obj.textlength)
obj.isStrikethrough = new Array(obj.textlength)
obj.fontFamily = new Array(obj.textlength)
obj.fontSize = new Array(obj.textlength)
var runs = richTextValues[row][column].getRuns();
for(var i=0; i<runs.length; i++) {
var run = runs[i]
var style = run.getTextStyle();
var start = run.getStartIndex()
var end = run.getEndIndex()
obj.color.fill(style.getForegroundColor(), start, end)
obj.isBold.fill(style.isBold(), start, end)
obj.isUnderline.fill(style.isUnderline(), start, end)
obj.isItalic.fill(style.isItalic(), start, end)
obj.isStrikethrough.fill(style.isStrikethrough(), start, end)
obj.fontFamily.fill(style.getFontFamily(), start, end)
obj.fontSize.fill(style.getFontSize(), start, end)
}
return obj;
}
function findWordsAndCombineTextStyle(obj, word, regWord, colorCodeArray) {
obj.newIndex = []
obj.count = 0
var reg = new RegExp(regWord,"ig")
var start;
while(start = reg.exec(obj.cellValue)){
obj.count++
obj.color.splice(start.index, word.length, ...colorCodeArray)
}
var count = []
for(var j=0; j<obj.textlength;) {
count.push(j)
if(isTextStyleEqual(j, obj, "color", "isBold", "isUnderline", "isItalic", "isStrikethrough", "fontFamily", "fontSize")) {
j++
continue
}
obj.newIndex.push([count[0], count.pop()+1])
j++
count.length = 0;
}
return obj;
}
function isTextStyleEqual(...args) {
var index = args[0]
var information = args[1]
for(var i=2; i<args.length; i++) {
if(information[args[i]][index] != information[args[i]][index+1]) {
return false
}
}
return true
}
function setTextStyle_in_cell(data) {
var rich = data.map(item => SpreadsheetApp.newRichTextValue().setText(item.cellValue))
data.forEach((information, i) => {
information.newIndex.forEach(item => {
var start = item[0]
var end = item[1]
var style = SpreadsheetApp.newTextStyle()
.setForegroundColor(information.color[start])
.setBold(information.isBold[start])
.setUnderline(information.isUnderline[start])
.setItalic(information.isItalic[start])
.setStrikethrough(information.isStrikethrough[start])
.setFontFamily(information.fontFamily[start])
.setFontSize(information.fontSize[start])
.build();
rich[i].setTextStyle(start, end, style)
})
})
data.forEach((item, j) => sheet.getRange(item.row+1, item.column+1).setRichTextValue(rich[j].build()))
return data.map(item => item.count).reduce((prev, curr) => prev + curr);
}