//GS
function enterTestingStandard() {
var htmlInformation = HtmlService.createHtmlOutputFromFile('testStandard').setTitle('Enter Testing Standard');
SpreadsheetApp.getUi().showSidebar(htmlInformation);
}
//===================================================================================================================================================================================================
function highlightComponentNumber(componentNumber) {
sheet.getRange(1,1,lastRow,lastColumn).setBackground("white");
highlightNumber(hyphen_to_comma(componentNumber), "yellow");
}
function hyphen_to_comma(numberStr) {
return numberStr.split(",").filter(item => item.toString().trim() != "").flatMap(str => {
var arr = str.split("-").map(value => parseInt(value.trim()));
if(arr.length > 2) throw 'hyphen error~~';
if(arr.length > 1) {
return [...Array(arr[1]-arr[0]+1).keys()].map(num => arr[0]+num);
}
return arr;
})
}
function highlightNumber(numberArr,color) {
try {
numberArr.forEach(num => sheet.getRange(num+1,1,1,18).setBackgrounds([new Array(18).fill(color)]));
} catch(e) {};
}
function checkComponentAssign(information) {
var testStandard = Object.keys(information);
var numberArr = Object.values(information).map(hyphen_to_comma);
var componentArray = [...new Set(numberArr.flat())];
var columnF = sheet.getRange(2,6,lastRow-1).getDisplayValues().map(arr => arr[0].trim());
var revised_columnF = columnF.filter(item => item != "");
for(var i=0;i<columnF.length;i++) {
if(columnF[i] == "") {
if(revised_columnF.length != i) {
throw `Error: Component ${i+1} Material (Column F) 存在空格~`;
} else {
break;
}
}
}
revised_columnF = revised_columnF.map((_,num) => num+1);
var emptyComponent = revised_columnF.filter(num => componentArray.indexOf(num) == -1);
if(emptyComponent.length) {
throw `Error: Component ${emptyComponent.join()} did not assign for any standards~`
}
if(revised_columnF.length < componentArray.length) {
var out_of_range = numberArr.map((arr,index) => arr.some(num => num > revised_columnF.length || num < 1)? testStandard[index] : null)
.filter(test => test !== null);
throw `Error: Component no. of Test Standard "${out_of_range.join()}" is out of range~`
}
}
function getFilterComponentNumber(obj) {
var material_dict = {
coating:["coating","coatings","ink","lacquer","nail polish","liquid glue","tattoo","paint"],
printing:["printing","printings","printed"],
plastic:["plastic","foam","hooked","synthetic","glitter","lurex","glue","elastic","epoxy","resin","silicone","pcb","fiber","fibre","adhesive tape"],
fabric:["fabric","woven","satin","knit","yarn","plush","velour","velvet","webbing","ribbon","terry","mesh","net","felt","twill","fleece","flannel",
"jacquard","corduroy","string","flocked","pompom","zig-zag","lace","elastic band","chenille","synthetic","embroidery","thread","velcro","pom pom"],
wood:["wood","bamboo"],
paper:["paper"],
metal:["metal","foil sheet","solder"],
["glass/ceramic"]: ["glass","ceramic"],
["other material"]:["material","lip","eye","blush","powder","liquid","mascara","bath","paste","gel","nail","thinner","bronzer",
"hand","lotion","cream","solution","face","body","chalk","slime","putty","clay","through","playdoh","shell","stone","leaf","grpahite","writing part","plaster","feather","leather","color cake"],
}
var information = {};
information.index = obj.index;
var selected = obj.selected;
var remarks = obj.remarks;
var allDatas = sheet.getRange(2,1,lastRow-1,18).getDisplayValues();
var sheetDatas = allDatas.map(item => item.slice(4,9));
var remarks_info = allDatas.map(arr => {
if(/packaging/i.test(arr[10])) {
return "packaging"
}
if(/internal/i.test(arr[10])) {
return "internal"
}
return "accessible"
});
if(remarks.indexOf("CPSIA Acc.") != -1) {
allDatas.map(item => item[17] + " " + item[3]).forEach((el,i) => {
if(/CPSIA ACC/i.test(el)) {
remarks_info[i] = "accessible"
}
})
}
var allData = sheetDatas.map(arr => arr.join(" "));
var main_material = sheetDatas.map(arr => arr[1].trim());
var match_data = [];
for(var i=0;i<selected.length;i++) {
var regex = new RegExp(material_dict[selected[i]].map(item => `\\b${item}\\b`).join("|"), "i");
for(var k=0;k<allData.length;k++) {
if(main_material[k].match(regex) && selected[i] == "coating" && remarks.indexOf(remarks_info[k]) !== -1) {
match_data.push(k+1);
continue;
}
if(allData[k].match(regex) && selected[i] !== "coating" && !/coating|lacquer/i.test(main_material[k]) && remarks.indexOf(remarks_info[k]) !== -1) {
match_data.push(k+1);
}
}
}
information.componentNumber = comma_to_hyphen([...new Set(match_data)].join());
return information;
}
function comma_to_hyphen(numberStr) {
var arr = numberStr.replace(/\,$/,"").split(",").map(num => parseInt(num));
arr.sort((a,b) => a-b);
var string = ""
var count = []
for(var i=0; i<arr.length;) {
count.push(arr[i])
if(arr[i]+1 == arr[i+1]) {
i++
continue;
}
if(count.length>1) {
string = string + count[0] +"-"+ count.pop() +","
} else {
string = string + count[0] + ","
}
i++
count.length = 0;
}
return string.replace(/\,$/,"");
}
//testingStandard.html
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Standard</title>
<style>
html, body {
margin:0;
padding:0;
}
#upper-wrapper {
position:relative;
}
#hidden-button {
display:none;
}
#import-title {
color:red;
font-weight:bold;
}
#standard {
width:90%;
}
#temporary {
width:50px;
height:25px;
float:left;
display:none;
}
#getTemporary {
width:75px;
height:25px;
display:none;
transform:translateX(100px);
}
#jobNumber {
font-size:18px;
margin-top:5px;
font-weight:bold;
}
#labGroup {
font-size:14px;
color:blue;
}
#testTotal {
color:red;
font-size:14px;
}
li {
list-style-type:none;
}
div.chosen-container {
width:280px !important;
}
</style>
<link href="https://cdnjs.cloudflare.com/ajax/libs/chosen/1.8.2/chosen.min.css" rel="stylesheet"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://code.jquery.com/ui/1.13.2/jquery-ui.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/chosen/1.8.2/chosen.jquery.min.js"></script>
</head>
<body>
<button id="hidden-button"></button>
<div id="upper-wrapper">
<div id="import-test-std">
<div id="import-title">Please import the testing standard first</div>
<textarea id="standard" rows=20 cols=30></textarea>
<button onclick="create();">import</button> <button onclick="getJavascriptCode();">get Javascript Code</button>
</div>
<button id="temporary" onclick="temporaryStorage();">暫存</button><button id="getTemporary" onclick="gettemporaryStorage();">暫存取出</button>
<div id="jobNumber"></div>
<div><label id="labGroup"></label> <label id="testTotal"></label></div>
<div id='entryTestStandard'></div><br><br><br>
</div>
<script>
function getJavascriptCode() {
var content =
`
(function () {
var script = document.createElement('script');
script.src = 'https://johnchu.neocities.org/sortingProgramme/getTextStandard2023.js?t=' + new Date().getTime();
document.body.appendChild(script);
document.body.removeChild(script);
})();
`
txt_download(content,'getTextStandard.txt');
}
function create() {
var jsonData = $("#standard").val().trim();
var obj = JSON.parse(jsonData);
var standard = Object.keys(obj.testStandard);
var assignNumber = Object.values(obj.testStandard);
$("#import-test-std").remove();
$("#jobNumber").text(obj.jobNumber);
$("#labGroup").text(obj.labGroup);
$("#temporary").css("display","block");
$("#getTemporary").css("display","block");
$("#testTotal").text(`Test Total: ${standard.length}`);
var $div = $("<div>");
$div.attr("id", "upper-filter-div");
$div.attr("style", "font-size:16px;background:white;position:sticky;top:-1px;border:3px solid black;");
$div.html(`
<div id="filter-main-material"><font color="brown">Filter Material (F)</font><button type="button" id="chosen-button" style="width:70px;
transform:translateX(20px);font-size:10px;" onclick="chosenAll();">全選</button><button type="button" id="toggle-filter" style="width:55px;float:right;font-size:8px;"
onclick="toggle_filter();">解鎖</button>
<select style="width:80vw;height:20px;font-size:14px;text-align:center;" id="filter-remarks">
<option value="accessible" selected>Accessible</option>
<option value="accessible/CPSIA Acc.">Accessible (including CPSIA Acc.)</option>
<option value="internal">Internal</option>
<option value="accessible/internal">Accessible + Inaccessible</option>
<option value="packaging">Packaging</option>
<option value="accessible/internal/packaging">Product + Packaging</option>
</select>
<select data-placeholder="material filter" multiple class="chosen-select" name="test">
<option>coating</option>
<option>printing</option>
<option>plastic</option>
<option>fabric</option>
<option>wood</option>
<option>paper</option>
<option>metal</option>
<option>glass/ceramic</option>
<option>other material</option>
</select></div>
`);
$div.insertBefore("#entryTestStandard");
var $form = $("<form>");
$form.attr("action", "javascript:submit();");
var $div = $("<div>");
$div.attr("id", "sortable1");
$div.attr("class", "connectedSortable");
for(var i=0; i<standard.length; i++) {
var $li = $("<li>");
$li.attr("id", "item"+i);
$li.attr("class", "ui-state-default");
$li.html(`<pre class="test">${standard[i]}</pre> <pre><input type="text" id="search${i}" value="${assignNumber[i]}"
placeholder="請輸入Component數字..." required pattern="^((\\d+[-]\\d+[,])|(\\d+[,]))*(\\d+|(\\d+[-]\\d+))$"> <button type="button" id="highlight${i}">Highlight</button> <button type="button" id="filter-autofill${i}" style="width:25px;">F</button></pre>`);
$div.append($li);
}
$form.append($div);
var $input = $("<input>");
$input.attr("type", "submit");
$input.attr("value", "提交");
$form.append($input);
$("#entryTestStandard").append($form);
$('[id^="highlight"]').each(function(index) {
$(this).on("click",function() {
var value = $("#search"+index).val();
google.script.run.withFailureHandler(onFailure).highlightComponentNumber(value);
})
});
$('[id^="filter-autofill"]').each(function(index) {
$(this).on("click", function() {
if($("#search"+index).val()) {
alert("請先清空component數字，然後繼續~");
return;
}
var information = {
remarks: $("#filter-remarks").val(),
selected: $.map($(".chosen-select option:selected"), el => $(el).text()),
index: index,
};
if(information.selected.length) {
google.script.run.withFailureHandler(onFailure).withSuccessHandler(setFilterValues).getFilterComponentNumber(information);
}
})
});
$("#sortable1").sortable({
connectWith: ".connectedSortable",
containment: "form",
});
$(".chosen-select").chosen();
$("#upper-wrapper").css("height",`${document.body.scrollHeight}px`);
}
function resetfilter() {
$('.chosen-select option').each(function() {$(this).prop("selected", false)});
$('.chosen-select').trigger("chosen:updated");
$("#chosen-button").html("全選");
document.querySelector("#chosen-button").onclick = chosenAll;
}
function chosenAll() {
$('.chosen-select option').each(function() {$(this).prop("selected", true)});
$('.chosen-select').trigger("chosen:updated");
$("#chosen-button").html("取消全選");
document.querySelector("#chosen-button").onclick = resetfilter;
}
function toggle_filter() {
if(document.querySelector("#upper-filter-div").style.position == "sticky") {
document.querySelector("#upper-filter-div").style.position = "relative";
$("#toggle-filter").html("上鎖");
} else {
document.querySelector("#upper-filter-div").style.position = "sticky";
$("#toggle-filter").html("解鎖");
}
}
function setFilterValues(information) {
$("#search"+information.index).val(information.componentNumber);
}
function temporaryStorage() {
var jobNumber = $("#jobNumber").text();
var obj = [...document.querySelectorAll("pre.test")].reduce((acc,el) => (acc[el.innerText]=el.nextElementSibling.firstChild.value, acc), {});
localStorage.setItem(jobNumber, JSON.stringify(obj));
alert("已成功暫存");
}
function gettemporaryStorage() {
if(!confirm("將會取代你現有的資料，是否繼續??"))
return;
var jobNumber = $("#jobNumber").text();
var obj = JSON.parse(localStorage.getItem(jobNumber));
var keys = Object.keys(obj);
var values = Object.values(obj);
for(var i=0;i<keys.length;i++) {
[...document.querySelectorAll("pre.test")].filter(el => el.innerText == keys[i])
.forEach(el => el.nextElementSibling.firstElementChild.value = values[i]);
}
}
function submit() {
var information = [...document.querySelectorAll("pre.test")].reduce((acc,el) => (acc[el.innerText]=el.nextElementSibling.firstChild.value, acc), {});
google.script.run.withFailureHandler(onFailure).withSuccessHandler(download_results).checkComponentAssign(information);
}
function download_results() {
var obj = [...document.querySelectorAll("pre.test")].reduce((acc,el) => (acc[el.innerText]=el.nextElementSibling.firstChild.value, acc), {});
var content =
`(async function () {
var test = [...document.querySelector("#scrollTopTable > tbody > tr").children];
var information = ${JSON.stringify(obj)};
for(var i=0; i<test.length; i++) {
document.querySelector("#Text").value = information[test[i].textContent];
await test[i].click();
};
console.log("ok");
})();`;
var filename = $("#jobNumber").text() + "_" + $("#labGroup").text() + "_output.txt";
txt_download(content, filename);
};
function txt_download (content, filename) {
var el = document.createElement('a');
el.download = filename;
el.style.display = 'none';
var blob = new Blob([content]);
el.href = URL.createObjectURL(blob);
document.body.appendChild(el);
el.click();
document.body.removeChild(el);
};
function onFailure(err) {
alert(err.message);
}
</script>
</body>
</html>