function findDuplicateInMultiSheets2() {
var responseText = SpreadsheetApp.getUi().prompt("Please enter sheet names of refer job number: job1,job2,job3").getResponseText();
var sheetNames = responseText.trim().replace(/[,]$/,"").split(",").map(item => item.toString().trim());
var allSheetsData = getAllSheetsData(sheetNames);
var duplicateData = getAllSheetsDuplicatesData(allSheetsData,sheetNames);
var duplicateIndex = getAllSheetsDuplicatesIndex(allSheetsData,sheetNames,duplicateData);
var referList = getReferString(duplicateData,duplicateIndex,sheetNames);
setReferComponents(duplicateIndex,sheetNames,referList);
function getAllSheetsData(sheetNames) {
var allSheetsData = {};
sheetNames.forEach(sheetName => {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var lastRow = sheet.getLastRow();
var sheetDatas = sheet.getRange(2,1,lastRow-1,18).getDisplayValues();
var columnEtoIData = getReferColumnData(sheetDatas.map(item => item.slice(4,9)));
var columnJData = getReferColumnData(sheetDatas.map(item => [item[9]]));
var regex = /\bmetal\b|\bglass\b|\bceramic\b|\bmaterial\b/i;
var columnEtoJData = columnEtoIData.map((item,i) => regex.test(item)? (item+" ("+columnJData[i]+")").trim() : item);
var columnKData = sheetDatas.map(item => {
if(/packaging/i.test(item[10]))
return "(packaging)";
if(/internal/i.test(item[10]))
return "(internal)";
return "";
});
allSheetsData[sheetName] = columnEtoJData.map((component,i) => (component+" "+columnKData[i]).trim());
})
return allSheetsData;
}
function getReferColumnData(sheetData) {
return sheetData.map(arr => arr.map(str => str.toLowerCase()
.trim()
.replace(/\-/g,"")
.replace(/\s+/g," ")
.replace(/colour/g,"color")
).filter(value => value !== "").join(" "));
}
function getAllSheetsDuplicatesData(allSheetsData,sheetNames) {
var duplicateData = [];
for(var i=0;i<sheetNames.length;i++) {
for(var j=i+1;j<sheetNames.length;j++) {
duplicateData.push(findDuplicate(allSheetsData[sheetNames[i]],allSheetsData[sheetNames[j]]));
}
}
return [].concat.apply([],duplicateData).filter((element,index,arr) => arr.indexOf(element) === index);
}
function findDuplicate(sheet1,sheet2) {
return sheet1.filter(value => sheet2.includes(value) && value !== "");
}
function getAllSheetsDuplicatesIndex(allSheetsData,sheetNames,duplicateData) {
return sheetNames.reduce((acc,sheetName) => (acc[sheetName] = getRepeatDataIndex(duplicateData,allSheetsData[sheetName]), acc), {});
}
function getRepeatDataIndex(duplicateData,sheetData) {
return duplicateData.map(duplicate => sheetData.map((component,i) => duplicate === component ? (i+1) : null)
.filter(i => i !== null));
}
function getReferString(duplicateData,duplicateIndex,sheetNames) {
return duplicateData.map((_,i) =>
sheetNames.reduce((arr,sheetName) => (
arr.concat(duplicateIndex[sheetName][i].map(number => `${sheetName}-${number};`))
), []).join("")
);
}
function setReferComponents(duplicateIndex,sheetNames,referList) {
sheetNames.forEach((sheetName,i) => {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var regString = sheetNames.slice(i,).map(item => `${item}(.*?);`).join("|");
var reg = new RegExp(regString, "g");
duplicateIndex[sheetName].forEach((sheetData,index) => sheetData.forEach(sheetRow => {
var referStatement = referList[index].replace(reg,"");
if(referStatement)
sheet.getRange(sheetRow+1,4).setValue(referStatement.match(/(.*?);/)[1]);
else
sheet.getRange(sheetRow+1,4).setValue("TBR");
})
)
})
}
}