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('我叫shiny~')
.addItem('Generate coating', 'Coating')
.addItem('Material Group', 'grouping')
.addItem('Generate PVCcoating', 'PVCCoating')
.addItem('CL Testing Standard', 'Testingstandard')
.addItem('Highlight PU/PVC/Rubber', 'highlightPUPVCRubber')
.addItem('Highlight Running color components', 'highlightRunningcolorcomponents')
.addToUi();
//};
//===================================================================================================================================================================================================
function Duplicates(arr) {
var newArr = [];
for(var i=0;i<arr.length;i++){
if(arr[i]=="") {
continue;
};
var count = 0;
for(var j=0;j<arr.length;j++){
if(arr[i]==arr[j]){
count++;
};
};
if(count>1 && newArr.indexOf(arr[i])==-1){
newArr.push(arr[i]);
};
};
return newArr;
};
//===================================================================================================================================================================================================
function duplicatesIndex(duplicates,Arr) {
var arr = [];
var newArr = [];
for(var i=0; i<duplicates.length; i++) {
for(var j=0; j<Arr.length; j++) {
if(duplicates[i]==Arr[j]) {
arr.push(j+1);
};
};
while(arr.length) newArr.push(arr.splice(0,arr.length));
};
return newArr;
};
//==================================================================================================================================================================================================
function getColor() {
var R = getRandomInt(156).toString(16);
var G = getRandomInt(156).toString(16);
var B = getRandomInt(156).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)+100);
};
//===================================================================================================================================================================================================
function grouping() {
var Y = 0
var Initial = 0
var columnE = ReadData(5)
var columnF = ReadData(6)
var columnL = ReadData(12)
var columnI = ReadData(9)
var columnD = ReadData(4)
var columnO = ReadData(15)
var columnG = ReadData(7)
var lastRow = getLastRow(columnF)
var lasTRow = lastRow
Material_group_set_negative1(Initial,lastRow)
var PVCcoatingIndex= CheckPVCcoatingIndex(columnD)
if(PVCcoatingIndex!=-1) {
var lastRow = PVCcoatingIndex
}
var CoatingIndex = getCoatingIndex(lastRow,columnG)
//console.log(CoatingIndex)
if(CoatingIndex==-1) {
var CoatingIndex = lastRow
}
var ColumnE = getDataArr(columnE,0,CoatingIndex)
var ColumnF = getDataArr(columnF,0,CoatingIndex)
var ColumnL = getDataArr(columnL,0,CoatingIndex)
var NewData = getNewData(ColumnE,ColumnF,ColumnL)
var duplicates = Duplicates(NewData)
var Index = duplicatesIndex(duplicates,NewData);
Write_Material_group_Value(Index,Y,0)
var Y = Index.length
var CoatingE = getDataArr(columnE,CoatingIndex,lastRow)
var CoatingI = getDataArr(columnI,CoatingIndex,lastRow)
var CoatingL = getDataArr(columnL,CoatingIndex,lastRow)
var NewData = getNewData(CoatingE,CoatingI,CoatingL)
var duplicates = Duplicates(NewData)
var Index = duplicatesIndex(duplicates,NewData);
Write_Material_group_Value(Index,Y,CoatingIndex)
var PVCcolumnF = getDataArr(columnF,lastRow,lasTRow)
var PVCcolumnI = getDataArr(columnI,lastRow,lasTRow)
var PVCcolumnL = getDataArr(columnL,lastRow,lasTRow)
var PVCcolumnO = getDataArr(columnO,lastRow,lasTRow)
var columnP = ReadData(16)
var Num = CheckMaterialgroupNumber(columnP)
var NewData = CreateNewData4(PVCcolumnF,PVCcolumnI,PVCcolumnL,PVCcolumnO)
var duplicates = Duplicates(NewData)
var Index = duplicatesIndex(duplicates,NewData);
//console.log(Index)
Write_Material_group_Value(Index,Num,lastRow)
};
//====================================================================================================================================================================================================
function getNewData(column1,column2,column3) {
var arr = []
for(var i=0; i<column2.length; i++) {
arr.push(column1[i]+column2[i]+column3[i]);
};
return arr;
};
function Write_Material_group_Value(Index,X,LastRow) {
for(var i=0; i<Index.length; i++) {
for(I in Index[i]) {
var range = sheet.getRange(1+Index[i][I]+LastRow,16);
range.setValue(i+1+X);
};
};
};
function getDataArr(column,Initial,Final) {
var arr = [];
for(var i=Initial;i<Final; i++) {
arr.push(column[i]);
};
return arr;
};
function Material_group_set_negative1(Initial,lastRow) {
for(var i=Initial;i<lastRow;i++) {
var range = sheet.getRange(i+2,16);
range.setValue(-1);
};
};
//==================================================================================================================================================================================================
function Coating() {
var columnI = ReadData(9);
var columnH = ReadData(8);
var columnD = ReadData(4);
var columnF = ReadData(6);
var columnJ = ReadData(10);
var columnO = ReadData(15);
var columnG = ReadData(7);
var LastRow = getLastRow(columnF);
var ColumnI = getDataArr(columnI,0,LastRow);
var ColumnH = getDataArr(columnH,0,LastRow);
var ColumnD = getDataArr(columnD,0,LastRow);
var ColumnF = getDataArr(columnF,0,LastRow);
var ColumnJ = getDataArr(columnJ,0,LastRow);
var ColumnO = getDataArr(columnO,0,LastRow);
var ColumnG = getDataArr(columnG,0,LastRow);
var Z = getCoatingIndex(LastRow,ColumnG)
if(Z!=-1) {
return;
};
var newData = getNewData2(ColumnI,ColumnH)
var coatingArr = searchCoatingWords(newData);
var allMessage = getAllMessage(coatingArr,newData);
var revisedMessage = removeAnd(allMessage)
var revisedMessage = removeAnd(revisedMessage)
var revisedMessage = removeAnd(revisedMessage)
var revisedMessage = removeAnd(revisedMessage)
var revisedMessage = removeWith(revisedMessage)
var revisedMessage = removeWith(revisedMessage)
var revisedMessage = removeWith(revisedMessage)
var revisedMessage = removeWith(revisedMessage)
var finalMessage = removeBrackets(revisedMessage)
var coatingData = createCoatingData(finalMessage)
var writingPart = coatingAnalysis(coatingArr,ColumnD,finalMessage,coatingData)
var CoatingReferenceIndex = getCoatingReferenceIndex(coatingArr,ColumnD,coatingData)
var coatings = CheckCoatingOrCoatings(coatingArr,newData)
revisedCoatingInfo = checkCoatings(coatingArr,coatings, CoatingReferenceIndex)
writeValueData(writingPart,LastRow,5)
writeValueData(revisedCoatingInfo,LastRow,6)
var word = "on"
writeWord(revisedCoatingInfo,LastRow,word,7)
writeValueArr(ColumnF,CoatingReferenceIndex,LastRow,9)
writeValueArr(ColumnJ,CoatingReferenceIndex,LastRow,10)
writeValueArr(ColumnO,CoatingReferenceIndex,LastRow,15)
var word = "???"
writeWord(revisedCoatingInfo,LastRow,word,12)
var word = "-1"
writeWord(revisedCoatingInfo,LastRow,word,16)
writeValueArr(ColumnD, CoatingReferenceIndex,LastRow,4)
writeComponentNumber(CoatingReferenceIndex,LastRow,1)
var analyzingDuplicateData = analyzingDuplicate(writingPart,ColumnF, CoatingReferenceIndex,ColumnO)
var duplicates = Duplicates(analyzingDuplicateData)
var Index = duplicatesIndex(duplicates,analyzingDuplicateData);
var column = 5
SetBackgroundColor(Index,column,LastRow)
};
//===================================================================================================================================================================================================
function ReadData(column) {
var lastRow = sheet.getLastRow();
var columnRange = sheet.getRange(2,column,lastRow);
var rangeArray = columnRange.getValues();
rangeArray = [].concat.apply([], rangeArray);
return rangeArray;
};
function getLastRow(column) {
for(var i=0;i<column.length; i++) {
if(column[i].length==0) {
break;
};
};
return i;
};
function getNewData2(data1,data2) {
var arr = [];
for(var i=0; i<data1.length; i++) {
arr.push(data2[i]+data1[i]);
};
return arr;
};
function searchCoatingWords(Arr) {
var coating = [];
for(var i=0; i<Arr.length; i++) {
if(Arr[i].indexOf("coating")!=-1 || Arr[i].indexOf("coating")!=-1
|| Arr[i].indexOf("lacquer")!=-1 || Arr[i].indexOf("lacquer")!=-1) {
coating.push(i);
};
};
return coating;
};
function CheckCoatingOrCoatings(Arr1,Arr2) {
var arr = [];
var reg = RegExp("coatings");
var regi = RegExp("lacquer");
for(var i=0; i<Arr1.length; i++) {
var y = 0;
if(regi.exec(Arr2[Arr1[i]]) && reg.exec(Arr2[Arr1[i]])) {
arr.push("coatings")
var y = 1;
continue
};
if(regi.exec(Arr2[Arr1[i]])){
arr.push("lacquer")
var y = 1;
};
if(reg.exec(Arr2[Arr1[i]])){
arr.push("coatings")
var y = 1;
};
if(y==0) {
arr.push("coating");
};
};
return arr;
};
function getAllMessage(Arr1,Arr2) {
var arr = [];
var reg = RegExp("coating");
var regi = RegExp("lacquer");
for(var i=0; i<Arr1.length; i++) {
var string ="";
if(reg.exec(Arr2[Arr1[i]]) && regi.exec(Arr2[Arr1[i]])) {
var initial= reg.exec(Arr2[Arr1[i]]).index;
for(var I = 0; I<initial; I++) {
var string = string + (Arr2[Arr1[i]])[I];
};
arr.push(string);
continue;
};
if(reg.exec(Arr2[Arr1[i]])){
var initial= reg.exec(Arr2[Arr1[i]]).index;
for(var I = 0; I<initial; I++) {
var string = string + (Arr2[Arr1[i]])[I];
};
arr.push(string);
};
if(regi.exec(Arr2[Arr1[i]])) {
var initial= regi.exec(Arr2[Arr1[i]]).index;
for(var I = 0; I<initial; I++) {
var string = string + (Arr2[Arr1[i]])[I];
};
arr.push(string);
};
};
return arr;
};
function removeAnd(words) {
var arr = [];
var reg = RegExp("and");
for(var i=0; i< words.length; i++) {
var string ="";
if(reg.exec(words[i])){
var initial= reg.exec(words[i]).index;
for(var I=initial+3; I< words[i].length; I++) {
var string = string + (words[i])[I];
};
arr.push(string);
} else {
arr.push(words[i]);
};
};
return arr;
};
function removeWith(words) {
var arr = [];
var reg = RegExp("with");
for(var i=0; i< words.length; i++) {
var string ="";
if(reg.exec(words[i])){
var initial= reg.exec(words[i]).index;
for(var I=initial+4; I< words[i].length; I++) {
var string = string + (words[i])[I];
};
arr.push(string);
} else {
arr.push(words[i]);
};
};
return arr;
};
function removeBrackets(Arr) {
var arr = [];
for(var i=0; i<Arr.length; i++) {
var string = "";
var R = Arr[i].toString();
for(var I=0; I<R.length; I++) {
if(R[I]=="(" || R[I] == ")") {
continue;
};
if(I==0 && R[I]==" ") {
continue;
};
if(I==R.length-1 && R[I]==" ") {
continue;
};
var string = string + R[I];
};
arr.push(string);
};
return arr;
};
function createCoatingData(Arr) {
var newArr = [];
var reg = RegExp(",");
for(var i=0; i<Arr.length; i++) {
var arr = [];
var R = Arr[i].toString();
if(reg.exec(R)){
var R = R.split(",");
for(var I=0;I<R.length;I++) {
var Y = R[I].toString();
var string = "";
for(var J=0; J<Y.length; J++) {
if(Y[J]!=" ") {
break
};
}
for(var k=J; k<Y.length; k++) {
var string = string + Y[k];
};
arr.push(string);
};
} else {
arr.push(R);
};
while(arr.length) newArr.push(arr.splice(0,arr.length));
};
return newArr;
};
function coatingAnalysis(data1,data2,data3,data4) {
var arr = [];
var reg = RegExp("4c");
var regi = RegExp("4C");
for(var i=0; i<data1.length; i++) {
if(reg.exec(data2[data1[i]]) || regi.exec(data2[data1[i]])) {
arr.push(data3[i].toString())
} else {
for(var I=0; I<data4[i].length; I++) {
arr.push(data4[i][I].toString());
};
};
};
return arr;
};
function getCoatingReferenceIndex(Arr1,Arr2,Arr3) {
var arr = [];
var reg = RegExp("4c");
var regi = RegExp("4C");
for(var i=0; i< Arr1.length; i++) {
if(reg.exec(Arr2[Arr1[i]]) || regi.exec(Arr2[Arr1[i]])) {
arr.push(Arr1[i])
} else {
for(var I=0; I<Arr3[i].length; I++) {
arr.push(Arr1[i])
};
};
};
return arr;
};
function checkCoatings(Arr1,Arr2,Arr3) {
var arr =[];
var reg = RegExp("s");
for(var i=0;i<Arr1.length;i++) {
var count = 0;
for(var I=0;I< Arr3.length; I++) {
if(Arr3[I]== Arr1[i]) {
count++;
};
};
if(count==1) {
arr.push(Arr2[i]);
};
if(count>1) {
var R = Arr2[i].toString();
if(reg.exec(R)) {
var R = R.replace("s","");
};
for(var k=0; k<count; k++) {
arr.push(R);
};
};
};
return arr;
};
function writeValueData(data,row,column) {
for(var i=0;i<data.length; i++) {
var range = sheet.getRange(row+2+i,column);
range.setValue(data[i]);
};
};
function writeWord(data,row,word,column) {
for(var i=0;i<data.length; i++) {
var range = sheet.getRange(row+2+i,column);
range.setValue(word);
};
};
function writeValueArr(Arr1,Arr2,row,column) {
for(var i=0; i<Arr2.length; i++) {
var range = sheet.getRange(row+2+i,column);
range.setValue(Arr1[Arr2[i]]);
};
};
function writeComponentNumber(Arr,row,column) {
for(var i=0; i<Arr.length; i++) {
var range = sheet.getRange(row+2+i,column);
range.setValue(row+1+i);
}
}
//=================================================================================================================================================================================================
function analyzingDuplicate(data1,data2,Arr3,data4) {
var arr = [];
for(var i=0;i<data1.length; i++) {
var R = data1[i].toString();
var S = data2[Arr3[i]].toString();
var T = data4[Arr3[i]];
arr.push(R+S+T);
};
return arr;
};
function SetBackgroundColor(Index,column,LastRow) {
for(var i=0; i<Index.length; i++) {
var color = getColor();
for(I in Index[i]) {
sheet.getRange(Index[i][I]+LastRow+1, column).setBackground(color);
sheet.getRange(Index[i][I]+LastRow+1, column+4).setBackground(color);
sheet.getRange(Index[i][I]+LastRow+1, column+10).setBackground(color);
};
};
};
//==================================================================================================================================================================================================
function PVCCoating() {
var columnE = ReadData(5)
var columnF = ReadData(6)
var columnG = ReadData(7)
var columnI = ReadData(9)
var columnJ = ReadData(10)
var columnL = ReadData(12)
var columnO = ReadData(15)
var columnD = ReadData(4)
var columnP = ReadData(16)
var lastRow = getLastRow(columnF)
var M = getCoatingIndex(lastRow,columnG)
if(M==-1) {
return;
};
var ColumnE = getDataArr(columnE,M,lastRow)
var ColumnF = getDataArr(columnF,M,lastRow)
var ColumnG = getDataArr(columnG,M,lastRow)
var ColumnI = getDataArr(columnI,M,lastRow)
var ColumnJ = getDataArr(columnJ,M,lastRow)
var ColumnL = getDataArr(columnL,M,lastRow)
var ColumnO = getDataArr(columnO,M,lastRow)
var ColumnD = getDataArr(columnD,M,lastRow)
var ColumnP = getDataArr(columnP,0,lastRow)
var PVCcoatingIndex = CheckPVCcoatingIndex(ColumnD)
if(PVCcoatingIndex!=-1) {
return;
}
var PVC_Index = CheckPVC(ColumnL)
var DataE = getIndexData(ColumnE,PVC_Index)
var DataF = getIndexData(ColumnF,PVC_Index)
var DataG = getIndexData(ColumnG,PVC_Index)
var DataI = getIndexData(ColumnI,PVC_Index)
var DataJ = getIndexData(ColumnJ,PVC_Index)
var DataL = getIndexData(ColumnL,PVC_Index)
var DataO = getIndexData(ColumnO,PVC_Index)
writeValueData(DataE,lastRow,5)
writeValueData(DataF,lastRow,6)
writeValueData(DataG,lastRow,7)
writeValueData(DataI,lastRow,9)
writeValueData(DataJ,lastRow,10)
writeValueData(DataL,lastRow,12)
writeValueData(DataO,lastRow,15)
writeComponentNumber(DataF,lastRow,1)
var word = "PVC coating"
writeWord(DataF,lastRow,word,4)
var newLastRow = lastRow + DataF.length
var Initial = lastRow
Material_group_set_negative1(Initial,newLastRow)
var Num = CheckMaterialgroupNumber(ColumnP)
var NewData = CreateNewData4(DataF,DataI,DataL,DataO)
var duplicates = Duplicates(NewData)
var Index = duplicatesIndex(duplicates,NewData);
Write_Material_group_Value(Index,Num,lastRow)
}
//===================================================================================================================================================================================================
function getCoatingIndex(lastRow,column) {
for(var i=0;i<lastRow;i++) {
if(column[i]=="on") {
break
}
}
if(i==lastRow) {
var i=-1
}
return i
}
function CheckPVC(Arr) {
var arr = []
var reg = RegExp("PVC")
var reg1 = RegExp("pvc")
var reg2 = RegExp("Polyvinyl chloride")
var reg3 = RegExp("Non")
var reg4 = RegExp("non")
for(var i=0;i<Arr.length;i++) {
if(reg3.exec(Arr[i]) || reg4.exec(Arr[i])) {
continue
}
if(reg.exec(Arr[i]) || reg1.exec(Arr[i]) || reg2.exec(Arr[i])) {
arr.push(i)
}
}
return arr
}
function getIndexData(column,index) {
var arr = []
for(var i=0;i<index.length;i++) {
arr.push(column[index[i]])
}
return arr
}
function CheckPVCcoatingIndex(column) {
var reg = RegExp("PVC")
var reg1 = RegExp("coating")
for(var i=0;i<column.length;i++) {
if(reg.exec(column[i]) && reg1.exec(column[i])) {
break
}
}
if(i==column.length){
var i=-1
}
return i
}
function CheckMaterialgroupNumber(column) {
var j=0
for(var i=0;i<column.length; i++) {
if(j>=parseInt(column[i]) || column[i].length==0) {
continue
}
j++
}
return j
}
function CreateNewData4(data1,data2,data3,data4) {
var arr=[]
for(var i=0;i<data2.length;i++) {
arr.push(data1[i]+data2[i]+data3[i]+data4[i])
}
return arr
}
//==================================================================================================================================================================================================
function Testingstandard() {
var columnF = ReadData(6)
var columnG = ReadData(7)
var lastRow = getLastRow(columnF)
var range = sheet.getRange(1,21);
range.setValue("CL Testing Standard");
var range = sheet.getRange(2,21);
range.setValue("Running color");
var range = sheet.getRange(3,21);
range.setValue("Coating");
var range = sheet.getRange(4,21);
range.setValue("PVCcoating");
var range = sheet.getRange(5,21);
range.setValue("PE/EVA plastic");
var range = sheet.getRange(6,21);
range.setValue("PVC plastic");
var range = sheet.getRange(7,21);
range.setValue("ST-Rubber made toy");
var M = getCoatingIndex(lastRow,columnG)
if(M==-1) {
var M = lastRow
}
var range = sheet.getRange(2,22);
range.setValue("1-"+M);
if(M!=lastRow) {
var columnD = ReadData(4)
var Y = CheckPVCcoatingIndex(columnD)
if(Y==-1) {
var Y = lastRow
}
var range = sheet.getRange(3,22);
range.setValue((M+1)+"-"+Y);
if(Y!=lastRow){
var range = sheet.getRange(4,22);
range.setValue((Y+1)+"-"+lastRow);
} else {
var range = sheet.getRange(4,22);
range.setValue("N/A");
}
} else {
var range = sheet.getRange(3,22);
range.setValue("N/A");
var range = sheet.getRange(4,22)
range.setValue("N/A")
}
var columnL = ReadData(12)
var ColumnL = getDataArr(columnL,0,M)
var PE_EVA = CheckPE_EVA(ColumnL)
if(PE_EVA.length==0) {
var range = sheet.getRange(5,22)
range.setValue("N/A")
} else {
var range = sheet.getRange(5,22)
range.setValue(PE_EVA)
}
var PVC = CheckPvc(ColumnL)
if(PVC.length==0) {
var range = sheet.getRange(6,22)
range.setValue("N/A")
} else {
var range = sheet.getRange(6,22)
range.setValue(PVC)
}
var Rubber = CheckRubber(ColumnL)
if(Rubber.length==0) {
var range = sheet.getRange(7,22)
range.setValue("N/A")
} else {
var range = sheet.getRange(7,22)
range.setValue(Rubber)
}
}
//====================================================================================================================================================================================================
function CheckPE_EVA(column) {
var string = ""
var reg = RegExp("PE")
var reg1 = RegExp("EVA")
var reg2 = RegExp("TPE")
var reg3 = RegExp("PET")
for(var i=0;i<column.length;i++) {
if(reg2.exec(column[i]) || reg3.exec(column[i])){
continue
}
if(reg.exec(column[i]) || reg1.exec(column[i])) {
var string = string + (i+1) + ","
}
}
return string
}
function CheckPvc(column) {
var string = ""
var reg = RegExp("PVC")
var reg1 = RegExp("pvc")
var reg2 = RegExp("Polyvinyl chloride")
var reg10 = RegExp("Non")
var reg11 = RegExp("non")
for(var i=0;i<column.length;i++) {
if(reg10.exec(column[i]) || reg11.exec(column[i])) {
continue
}
if(reg.exec(column[i]) || reg1.exec(column[i]) || reg2.exec(column[i])) {
var string = string + (i+1) + ","
}
}
return string
}
function CheckRubber(column) {
var string = ""
var reg = RegExp("Rubber")
var reg1 = RegExp("Silicone")
var reg2 = RegExp("SB")
var reg3 = RegExp("TPE")
var reg4 = RegExp("TPR")
var reg5 = RegExp("TPU")
var reg10 = RegExp("Non")
var reg11 = RegExp("non")
for(var i=0;i<column.length;i++) {
if(reg10.exec(column[i]) || reg11.exec(column[i])) {
continue
}
if(reg.exec(column[i]) || reg1.exec(column[i]) || reg2.exec(column[i]) || reg3.exec(column[i]) || reg4.exec(column[i]) || reg5.exec(column[i])) {
var string = string + (i+1) + ","
}
}
return string
}
function CheckPU(column) {
var string = ""
var reg = RegExp("PU")
var reg2 = RegExp("TPU")
var reg10 = RegExp("Non")
var reg11 = RegExp("non")
for(var i=0;i<column.length;i++) {
if(reg10.exec(column[i]) || reg11.exec(column[i]) || reg2.exec(column[i])) {
continue
}
if(reg.exec(column[i])) {
var string = string + (i+1) + ","
}
}
return string
}
//===================================================================================================================================================================================================
function highlightPUPVCRubber() {
var ColumnL = ReadData(12)
var PU = CheckPU(ColumnL)
var PVC = CheckPvc(ColumnL)
var Rubber = CheckRubber(ColumnL)
if(PU.length!=0){
var PU = PU.split(",")
var red = "#f18c8c"
highlightmaterial(PU,red)
}
if(PVC.length!=0){
var PVC = PVC.split(",")
var green = "#97dd7b"
highlightmaterial(PVC,green)
}
if(Rubber.length!=0){
var Rubber = Rubber.split(",")
var blue = "#b6f5fa"
highlightmaterial(Rubber,blue)
}
}
function highlightmaterial(material,color) {
for(var i=0; i<material.length-1; i++) {
for(var I=1;I<19;I++) {
sheet.getRange(parseInt(material[i])+1, I).setBackground(color);
}
}
}
//===================================================================================================================================================================================================
function highlightRunningcolorcomponents() {
var columnF = ReadData(6)
var columnG = ReadData(7)
var lastRow = getLastRow(columnF)
var M = getCoatingIndex(lastRow,columnG)
if(M==-1) {
var M = lastRow
}
for(var i=1; i<M+2; i++) {
for(var I=1;I<19;I++) {
sheet.getRange(i, I).setBackground("#f1e1eb");
}
}
}