var majorMaterial = `plastic|foam|felt|wax|liquid|metal|glass|ceramic|fabric|body|epoxy|solder|elastic|paper|tape|fibre|glue|powder|PCB|grease|mica|ribbon|stuffing|velcro|woven`
var minorMaterial = `${majorMaterial}|printings?|coatings?|adhesive|plating`
function ROHS() {
var str = SpreadsheetApp.getUi().prompt("Please enter ROHS tested component").getResponseText()
if(str.trim().length==0){
return
}
addFirstRow()
var lastRow = findLastRow()
sheet.getRange(1,1,sheet.getLastRow()).setFontColor("black")
var str = "1 " + str
var data = getROHSdata(str)
var Location = data.map(item => (RegExp(/\(/).exec(item))? item.toString()
.slice(item.lastIndexOf("(")+1,item.lastIndexOf(")")).trim() : "")
var data = data.map(item => item.toString()
.replace(item.slice(item.lastIndexOf("("),item.lastIndexOf(")")+1),"").trim())
var ColumnEdata = getColumnEdata(data)
var data = data.map((item,i) => item.toString().replace(ColumnEdata[i].toString(),""))
var ColumnFdata = getColumnFdata(data)
var data = data.map((item,i) => item.toString().replace(ColumnFdata[i].toString(),""))
var ColumnGdata = data.map(item => (RegExp(/with/i).exec(item)? item.match(/(with)/i)[1]:""))
var data = data.map((item,i) => item.toString().replace(ColumnGdata[i].toString(),""))
var ColumnHdata = getColumnHdata(data)
var data = data.map((item,i) => item.toString().replace(ColumnHdata[i].toString(),""))
var ColumnIdata = getColumnIdata(data)
WriteValueData(ColumnEdata,lastRow+1,5)
WriteValueData(ColumnFdata,lastRow+1,6)
WriteValueData(ColumnGdata,lastRow+1,7)
WriteValueData(ColumnHdata,lastRow+1,8)
WriteValueData(ColumnIdata,lastRow+1,9)
WriteValueData(Location,lastRow+1,10)
WriteComponentNumber(ColumnFdata,lastRow+1,1)
ColumnFdata.length>0? sheet.getRange(lastRow+1,1,ColumnFdata.length).setFontColor("red") : ""
}
function getROHSdata(str) {
var arr =[]
var word = `silver color mica|white glue (?!\\()|translucent yellow grease|black woven tape`
var reg = new RegExp(`${word}`)
var str = str.replace(reg,`${str.match(reg)} ()`).replace(`black plastic commutator of motor)`,`black plastic (commutator of motor)`)
.replace(/palstic/g,"plastic")
while(true) {
try {
var message = str.match(/((\d|\+|\#|\*|\^)(.*?))(\))/i)[1]
var data = message.replace(/\d.{0,0}[a-z]/i,"").replace(/\d|\+|\#|\*|\&|\^/g,"").replace(/CONTACT PLATE|VELCRO/g,"").trim()
arr.push(data.replace(data.slice(data.lastIndexOf("("),data.length),message.slice(message.lastIndexOf("("),message.length)))
var str = str.replace(message,"").trim()
} catch(err) {
break
}
}
var arr = arr.map(item => (item.toString()+")").split(" ").filter(value => value!=="").join(" "))
return arr
}
function getColumnEdata(data){
var arr = []
var regColor = new RegExp(`(.*?)(${majorMaterial})`,`i`)
for(var i=0;i<data.length;i++) {
try{
var color = data[i].match(regColor)[1].trim()
} catch(err) {
var color = ""
}
arr.push(color)
}
return arr
}
function getColumnFdata(data){
var arr = []
for(var i=0;i<data.length;i++) {
var reg = RegExp(/with/i).exec(data[i])?
new RegExp(`((${majorMaterial}).*).*(with)`,`i`): new RegExp(`((${majorMaterial}).*).*`,`i`)
try {
var material = data[i].match(reg)[1].trim()
} catch(err) {
var material = data[i].toString().trim()
}
arr.push(material)
}
return arr
}
function getColumnHdata(data){
var arr = []
var regColor = new RegExp(`(.*?)(${minorMaterial})`,`i`)
for(var i=0;i<data.length;i++) {
try{
var color = data[i].match(regColor)[1].trim()
} catch(err) {
var color = ""
}
arr.push(color)
}
return arr
}
function getColumnIdata(data){
var arr = []
for(var i=0;i<data.length;i++) {
var reg = new RegExp(`((${minorMaterial}).*).*`,`i`)
try {
var material = data[i].match(reg)[1].trim()
} catch(err) {
var material = data[i].toString().trim()
}
arr.push(material)
}
return arr
}