function getCoatingComponent() { //generate coating 第一個方法
writeCoatingComponent(0)
}
function getCoatingComponentAndJoinLocation() { //generate coating 第二個方法, will join component for same color coating on same
var duplicateIndex = writeCoatingComponent(1) //substrate (same style)
JoinLocationForSameColorCoating(duplicateIndex)
}
function JoinLocationForSameColorCoating(duplicateIndex) {
var LastRow = findLastRow() //取得component last Row,而非 sheet.getLastRow(),因為有可能不同
var coatingIndex = findCoatingIndex(LastRow) //find the Initial index of coating
var ColumnJ = getEachColumnData(10,coatingIndex+1,LastRow) //get all coating data location
JoinLocation(duplicateIndex,coatingIndex+1,ColumnJ) //join component for same color coating on same substrate (style)
DeleteRowArr(duplicateIndex,coatingIndex) // delete duplicate components
ColumnJ.splice(0,duplicateIndex.length) //Court total number component input, will be used in next step
WriteComponentNumber(ColumnJ,coatingIndex+1,1) //writing component number,總長度就是 ColumnJ的長度 減去 duplicate Index 的長度
}
//====================================================================================================================================================================================================
function JoinLocation(Index,row,column) { //generate coating 第二個方法先要用到
for(var i=0;i<Index.length;i++) {
var string ="";
for(I in Index[i]) {
var string = string + column[Index[i][I]-1] +"," + " "; //將重覆coating的Location延長成文字屬性
};
var string = string.trim().replace(/[,]$/,"") //replace 最後的空格和,號
sheet.getRange(row+Index[i][0]-1,10).setValue(string) //重覆第一項的值延長location
};
};
function DeleteRowArr(Index,row) {
var arr = []; //將重覆的index由第二項開始推入數組,之後刪除
for(var i=0;i<Index.length;i++) {
for(I=1;I<Index[i].length;I++) { //不刪除重覆第一項的值
arr.push(row+Index[i][I]);
};
};
arr.sort((a,b) => b-a); //將duplicate Index 數字由大至小排列,方便刪除列不走位
arr.forEach(item => sheet.deleteRow(item)) //利用 for 循環將重覆index逐個刪除
}
//====================================================================================================================================================================================================
function writeCoatingComponent(select) {
var LastRow = findLastRow() //取得component last Row,而非 sheet.getLastRow(),因為有可能不同
var coatingIndex = findCoatingIndex(LastRow) //find the Initial index of coating
if(coatingIndex!=-1) { //如果compoennt coating存在,就不generate coating,避免發生錯誤
return
}
var coatingIndex = LastRow //設coatingIndex = lastRow
var ColumnD = getEachColumnData(4,2,coatingIndex) //取得Runnning color columnD data (check有冇4c coating)
var ColumnF = getEachColumnData(6,2,coatingIndex) //取得Runnning color columnF data (Running color material)
var ColumnJ = getEachColumnData(10,2,coatingIndex) //取得Runnning color columnJ data (Running color location)
var ColumnO = getEachColumnData(15,2,coatingIndex) //取得Runnning color columnO data (Running color style)
var allData = getAllColumnData(2,5,coatingIndex,9).map(arr => arr.join(" ")) //將component description 文字合併,方便處理同分析
var coatingIndexArr = allData.map((element,i)=> (element.indexOf("coating")!=-1 || element.indexOf("lacquer")!=-1) ? i:null) //取得有coating,lacquer字眼component description 的 index
.filter(i => i !== null) //方便之後做處理同分析
var coatingInfo = allData.filter(str => str.toString().indexOf("lacquer")!==-1 || str.toString().indexOf("coating")!==-1) //取得有coating,lacquer字眼component description
var coatingData = coatingInfo.map(str => str.toString().match(/(?:.*(?:and|with))(.*)(coatings?|lacquer)/)[1] //將有coating,lacquer字眼component description中,匹配coating的顏色
.replace(/[\(\)]/g,"").trim()).map(arr => arr.split(",").map(str => str.toString().trim())) //除空格,除(),以及將取得coating顏色的文字屬性轉成數組,方便處理
var writingPart = CoatingAnalysis(coatingIndexArr,ColumnD,coatingData) //分析有沒有4c coating,有就炒一個出,沒有就逐個顏色分
var newCoatingIndex = getNewCoatingIndex(coatingIndexArr,ColumnD,coatingData) //制造新的coating Index,寫data果陣要用到
var coatingsWord = allData.map(arr => RegExp(/coatings?|lacquer/).exec(arr)? //處理完coating顏色問題後,到coating字眼,應該寫coating,coatings or lacquer?
arr.toString().match(/(?:.*)(coatings?|lacquer)/)[1].trim() : "") //匹配 coating, coatings 和lacquer 字眼。
var revisedCoatingInfo = RevisedCoatingsWord(coatingsWord,newCoatingIndex) //Revised coating 字眼,因為coating 逐個分 就由原本 coatings 變成 coating
var ColumnF = ColumnF.map((item,index) => RegExp(/plastic(.*)film/).exec(allData[index].toString())? //將原本帶有plastic film 的 component descrption 轉變成 coating on
item.replace(item,"plastic film"): item) //plastic film, 因為通常 plastic film 係上面
WriteValueData(writingPart,coatingIndex+1,5) //寫入coating 顏色data 係 lastRow +1列,第5欄
WriteValueData(revisedCoatingInfo,coatingIndex+1,6) //寫入coating 字眼data 係 lastRow +1列,第6欄
WriteWord(newCoatingIndex,coatingIndex+1,"on",7) //將"on"文字寫入係lastRow +1列,第7欄,寫入長度係newCoatingIndex總長度
WriteWord(newCoatingIndex,coatingIndex+1,"???",12) //將"???"文字寫入係lastRow +1列,第12欄,寫入長度係newCoatingIndex總長度
WriteWord(newCoatingIndex,coatingIndex+1,"-1",16) //將"-1"文字寫入係lastRow +1列,第16欄,寫入長度係newCoatingIndex總長度
WriteValueArr(ColumnD,newCoatingIndex,coatingIndex+1,4) //寫入ColumnD[newCoatingIndex]的remark data資料係 lastRow +1列,第4欄
WriteValueArr(ColumnF,newCoatingIndex,coatingIndex+1,9) //寫入ColumnF[newCoatingIndex]的物料 data資料係 lastRow +1列,第6欄
WriteValueArr(ColumnJ,newCoatingIndex,coatingIndex+1,10) //寫入ColumnJ[newCoatingIndex]的location data資料係 lastRow +1列,第10欄
WriteValueArr(ColumnO,newCoatingIndex,coatingIndex+1,15) //寫入ColumnJ[newCoatingIndex]的style no. data資料係 lastRow +1列,第15欄
setCoatingBackgroundTransparent()
setFontBlack()
var analyzingDuplicateData = analyzingDuplicate(writingPart,ColumnF,newCoatingIndex,ColumnO) //將coating component description 整合,方便找duplicate
var Index = findDuplicates(analyzingDuplicateData) //找出所有 same color coating on same substrate (same style)
var LastRow = findLastRow() //取得component last Row,而非 sheet.getLastRow(),因為有可能不同
var ColumnI = getEachColumnData(9,coatingIndex+1,LastRow)
SetFontRedForPlasticFilm(coatingIndex+1,ColumnI) //將coating on plastic film 的 plastic film 文字轉成紅色
if(select!=0) { //如果是選擇了join location for same color coating就 return index
return Index; //就不會highlight 形式顯示
};
WriteComponentNumber(newCoatingIndex,coatingIndex+1,1) //寫component number,總長度就是 newCoaitngIndex的長度
setBackgroundColour(Index,5,coatingIndex)
}
//====================================================================================================================================================================================================
function findLastRow() { //取得component last Row,而非 sheet.getLastRow(),因為有可能不同
return sheet.getRange(1,6,sheet.getLastRow()).getValues().reduce((array1,array2) => array1.concat(array2),[]) //透過 ColumnF 的data判斷實際 component 數目
.map(str => str.toString().trim()).filter(value => value!=="").length
}
function findCoatingIndex(lastRow) { //透過 ColumnG 存在"on" 的data判斷實際 coating 起始 index, coating on ...
return sheet.getRange(1,7,lastRow).getValues().reduce((array1,array2) => array1.concat(array2),[]) //也可以用[].concat.apply([],arr)將雙[]變成單[]
.map(str => str.toString().trim()).indexOf("on")
}
//====================================================================================================================================================================================================
function getEachColumnData(column,rowInitial,rowFinal) { //取得column data,將雙[]變成單[],數組中的資料trim()
return sheet.getRange(rowInitial,column,rowFinal-rowInitial+1).getValues().reduce((array1,array2) => array1.concat(array2),[])
.map(str => str.toString().trim())
}
function getAllColumnData(rowInitial,columnInitial,rowFinal,columnFinal) { //取得指定range 的row and column data,且將數組中的資料trim()
return sheet.getRange(rowInitial,columnInitial,rowFinal-rowInitial+1,columnFinal-columnInitial+1).getValues()
.map((arr1) => arr1.map(str => str.toString().trim()))
}
//====================================================================================================================================================================================================
function CoatingAnalysis(coatingIndexArr,ColumnD,coatingData) { //分析有沒有4c coating,有就炒一個出,沒有就逐個顏色分
var arr = []
coatingIndexArr.forEach((value,index) => RegExp(/4C/i).exec(ColumnD[value])? //判斷ColumnD中 remark有沒有4c,4C字眼
arr.push(coatingData[index].toString().replace(/[,]/g,", ").trim()): //有就炒一個出
coatingData[index].forEach(item => arr.push(item.toString().trim())) //沒有就逐個顏色出
)
return arr
}
function getNewCoatingIndex(coatingIndexArr,ColumnD,coatingData) { //制造出新的coating的指標,定義為coating顏色會出compoent的數目。
var arr = [];
coatingIndexArr.forEach((item,index) => RegExp(/4C/i).exec(ColumnD[parseInt(item)])? //判斷ColumnD中 remark有沒有4c,4C字眼
arr.push(item): coatingData[index].forEach(value => arr.push(item))) //有就推1次(出1個component),沒有就推coating顏色總長度咁多次
return arr; //因為會出(coating顏色總長度)的component 數目
}
function RevisedCoatingsWord(coatingsWord,newCoatingIndex) { //改寫coating字眼,因為如果coating逐個分,就要將coatings變成coating
var repeatIndex = newCoatingIndex.map((element,index,arr) => arr.indexOf(element) !== arr.lastIndexOf(element) ? index: null)//判斷newCoatingIndex有沒有重覆index,因為coatingIndex遂個分就會有重覆
.filter(index => index!== null) //filter出coating要遂個分的index,這些coating字眼都需要處理
return newCoatingIndex.map((item,index) => repeatIndex.indexOf(index)!==-1? //filter出index會入落repetIndex數組,如果repetIndex數組存在這個index時
item.toString().replace(item,coatingsWord[parseInt(item)]).replace("s",""): //就要將coatings字眼轉成coating,除去s
item.toString().replace(item,coatingsWord[parseInt(item)])) //沒有就直接將newCoatingIndex中的數字轉換成coating字眼就可以
}
//====================================================================================================================================================================================================
function analyzingDuplicate(writingPart,ColumnF,newCoatingIndex,ColumnO) { //將coating component description 整合
var arr = [];
newCoatingIndex.forEach((item,index) => //coating component description長度就是newCoatingIndex.length
arr.push(writingPart[index]+ColumnF[parseInt(item)]+ColumnO[parseInt(item)])) // ??? color coating on ??? substrate,(style ???)
return arr;
}
//====================================================================================================================================================================================================
function SetFontRedForPlasticFilm(row,ColumnI) { //將coating on plastic film 的 plastic film 文字轉成紅色
var index = ColumnI.map((item,i) => item.toString() === "plastic film"? i:null) //先filter出帶有plastic film字眼的 index
.filter(i => i!==null)
index.forEach(item => sheet.getRange(row+item,9).setFontColor("red")) //再用for 循環逐個 coating on plastic film 的 plastic film 變成紅字
}
function setBackgroundColour(Index,column,LastRow) { //highlight same color coating on same color substrate (same style)
var colorArr = getRandomColour(Index.length) //getRandomColourArr
Index.forEach((value,index) => value.forEach(item => {
sheet.getRange(item+LastRow,column).setBackground(colorArr[index].toString()) //setColor on Row: index+LastRow, Column: 5
sheet.getRange(item+LastRow,column+4).setBackground(colorArr[index].toString()) //setColor on Row: index+LastRow, Column: 9
sheet.getRange(item+LastRow,column+10).setBackground(colorArr[index].toString()) //setColor on Row: index+LastRow, Column: 15
})
)
}
//====================================================================================================================================================================================================
function WriteValueData(data,row,column) { //Write data arr
sheet.getRange(row,column,data.length).setValues(data.map(arr => [arr]))
};
function WriteWord(newCoatingIndex,row,word,column) { //Write word
sheet.getRange(row,column,newCoatingIndex.length).setValue([word])
};
function WriteValueArr(columnData,newCoatingIndex,row,column) { //Write column[index] arr
sheet.getRange(row,column,newCoatingIndex.length).setValues(newCoatingIndex.map((item) => [columnData[parseInt(item)]]))
};
function WriteComponentNumber(Arr,row,column) { //Write component no.;length of component no. equal to Arr.length
sheet.getRange(row,column,Arr.length).setValues(Arr.map((item,i) => [item.toString().replace(item,(row-1+i))]))
};
//====================================================================================================================================================================================================
function findDuplicates(Arr) {
var duplicates = getRepeatData(Arr) //找重覆data
var Index = getRepeatDataIndex(duplicates,Arr) //找重覆data 的 index
return Index;
};
function getRepeatData(Arr) {
var repeat = Arr.filter(function(element,index,arr){
return element !=="" && arr.indexOf(element) !== index //沒有重覆的話arr.indexOf(element)會等於index,重覆就不等於了。
});
var result = repeat.filter(function(element,index,arr){
return arr.indexOf(element) === index; //移除重覆的repeat data,因為如果重覆的值有3次或以上,repeat會出現這個值超過一次。
});
return result //返回就是取得repeat data,而且repeat data element只會出現一次。
}
function getRepeatDataIndex(repeatData,Arr) {
var arr = []
for(I in repeatData) { //從原本data數組判斷data 數組中element 能否在repeat data數組找到?
arr.push(Arr.map((duplicate,i) => duplicate === repeatData[I] ? (i+1) : null) //找到就(i+1),找不到就null,再filter走null的值就是 repartDataIndex
.filter(i => i !== null))
}
return arr
}
//====================================================================================================================================================================================================
function setCoatingBackgroundTransparent() { //setCoatingBackgroundTransparent
var lastRow = findLastRow()
var CoatingIndex = findCoatingIndex(lastRow)
var lastColumn = sheet.getLastColumn()
sheet.getRange(CoatingIndex+1,1,lastRow,lastColumn).setBackgroundColor("white")
}
function setFontBlack() { //setFontBlack
var lastRow = sheet.getLastRow()
var lastColumn = sheet.getLastColumn()
sheet.getRange(1,1,lastRow,lastColumn).setFontColor("black")
}
//====================================================================================================================================================================================================
function getRandomColour(num) { //getRandomColour
var arr =[]
var R = getRandomNumber(156,num)
var G = getRandomNumber(156,num)
var B = getRandomNumber(156,num)
for(var i=0;i<num;i++) {
var r = R[i].toString(16)
var g = G[i].toString(16)
var b = B[i].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;
arr.push(code) //colour code
}
return arr;
};
function getRandomNumber(max,num) { //max range: 0-256,number increase => colour more deeper
var arr =[]
for(var i=0;i<num;i++) {
var randomNum = Math.floor(Math.random() * Math.floor(max)+256-max)
var p = 0
for(var j=0;j<arr.length;j++) {
if(Math.pow(parseInt(arr[j])-randomNum,2)<=(Math.pow(Math.floor(max/num*1/2),2))) { //To avoid similar colour appears,not necessary
var p = 1
break
}
}
if(p==0) {
arr.push(randomNum)
} else {
i--
}
}
return arr
}
//====================================================================================================================================================================================================