function referHL(){
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); //获取第一张Sheet1
var E_to_I_col_data1 = sheet1.getRange(2, 5, sheet1.getLastRow(),5).getValues(); //获取第一张Sheet1 第2行E到I列数据
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2"); //获取第一张Sheet2
var E_to_I_col_data2 = sheet2.getRange(2, 5, sheet2.getLastRow(), 5).getValues(); //获取第二张Sheet2 第2行E到I列数据
var concatEtoI_sheet1=[];
var concatEtoI_sheet2=[];
for (var j = 0; j < E_to_I_col_data1.length; j++) { //用空格将Sheet1 E到I列数据结合埋一齐
concatEtoI_sheet1.push(E_to_I_col_data1[j].join(" "));
}
//console.log(concatEtoI_sheet1)
for (var k = 0; k < E_to_I_col_data2.length; k++) { //用空格将Sheet2 E到I列数据结合埋一齐
concatEtoI_sheet2.push(E_to_I_col_data2[k].join(" "));
}
//console.log("Get Repeat:"+getRepeat(combine_all_arrs)) //get repeat data of 2 sheets arrays
var s1 = concatEtoI_sheet1.filter(element => element.trim() !==null && element.trim() !==""); //用filter方法将sheet1 E到I列数据array内null/没有数据/空格的element移除
var s2 = concatEtoI_sheet2.filter(element => element.trim() !==null && element.trim() !==""); //用filter方法将sheet2 E到I列数据array内null/没有数据/空格的element移除
var combine_all_arrs = [].concat(s1,s2); //将2张 sheets arrays 结合做一个
//console.log(combine_all_arrs)
//console.log(combinedArrs.length);
//console.log(s1)
//console.log(s2)
var repeat = getRepeat(combine_all_arrs); //找出两张结合的一条array内repeat左既elements (用作对照相同elements在不同sheet arrays内找出位置)
//console.log("Repeat values of the two arrays: \n"+repeat)
var s1index = getRepeatDataIndex(repeat,s1); //返回sheet1 内重复elements 的index位置 two dimension array
var s2index = getRepeatDataIndex(repeat,s2); //返回sheet2 内重复elements 的index位置 two dimension array
var s1rangeArray = [].concat.apply([], s1index); //返回sheet1 内重复elements 的index位置转 one dimension array
var s2rangeArray = [].concat.apply([], s2index); //返回sheet2 内重复elements 的index位置转 one dimension array
//console.log("sheet1 indexes: "+s1rangeArray)
//console.log("sheet2 indexes: "+s2rangeArray)
//WriteToB_colSheet1();
//WriteToB_colSheet2();
HL_sheet1()
HL_sheet2()
function getRepeat(combinedA) { //找出两张结合的一条array内repeat左既elements
var repeat = combinedA.filter(function(element,index,arr){
return element !=="" && arr.indexOf(element) !== index
});
var result = repeat.filter(function(element,index,arr){
return arr.indexOf(element) === index;
});
return result
}
function getRepeatDataIndex(repeatData,array) { //将sheet1 or sheet2 的array compare两张结合的一条array内repeat左既elements 来获取repeat elements的index位置
var arr = []
for(I in repeatData) {
arr.push(array.map((duplicate,i) => duplicate === repeatData[I] ? (i+1) : null) //(i+1) for google sheet row 1 or col 1
.filter(i => i !== null && i !== " ") )
}
return arr
}
function HL_sheet1(){ //写入s1rangeArray（indexes） 一维数据 e.g.(s1rangeArray[0]等于repeat index第一个数据[HL到colum2的第一行])
//writing to B column of sheet1
for (var m = 0; m < s1rangeArray.length; m++) {
sheet1.getRange(s1rangeArray[m]+1, 2).setBackground("violet");
}
};
function HL_sheet2(){
//writing to B column of sheet2
for (var m = 0; m < s2rangeArray.length; m++) {
sheet2.getRange(s2rangeArray[m]+1, 2).setBackground("yellow");
}
};
function WriteToB_colSheet1(){ //写入（用空格分开合体的column E到I列数据到column B第二行
//writing to B column of sheet1
for (var k = 0; k < s1.length; k++) {
sheet1.getRange(2+k, 2).setValue(s1[k])
}
};
function WriteToB_colSheet2(){
//writing to B column of sheet1
for (var l = 0; l < s2.length; l++) {
sheet2.getRange(2+l, 2).setValue(s2[l])
}
}
};
_______________________________________________________________________________________________________________________________________________
_______________________________________________________________________________________________________________________________________________
_______________________________________________________________________________________________________________________________________________
reference |
__________|
rows = [
['Email', 'Number of Guests', 'Dinner Choice'],
['jeff@email.com', 2, 'Vegetarian'],
['someone@email.com', 1, 'Beef'],
['else@email.com', 3, 'Vegetarian'],
] //row[0]=email, row[1]=number....
var filteredRows = rows.filter(function(row){
if (row[1].toString() === '3') {
return row;
}
});
log(filteredRows)
filteredRows.forEach(function(row) {
log(row);
})
var array1 = [1, 2, 3, 4, 5, 8, 3, 8];
var array2 = [3, 4, 5, 6, 7, 8];
var duplicates = array1.filter(function(val) {
return array2.indexOf(val) != -1;
});
console.log(duplicates);
var array_one = ['a','b','c','d',"a","a","b","y","y"];
var array_two =['z','x','y','a','y','a',"y"];
array_two.forEach(function(item){
var isPresent = array_one.indexOf(item);
if(isPresent !== -1){
console.log(item)
}
})
//overlap index
var x = ['1', '2', '6', '5', "5"];
var y = ["3", '9', '5', '6', "5"];
//var x = ['1','2','6'];
//var y = ['4','6','5'];
var overlap = y.filter(function(v,i,a){
return x.indexOf(v,1) > -1;
});
console.log("overlap:"+overlap); // ['6']
var indx = []
var indy = []
x.filter(function(yourArray,index) {
if(yourArray == overlap[index]){
indx.push(index)
}
});
y.filter(function(yourArray,index) {
if(yourArray == overlap[index]){
indy.push(index)
}
});
console.log("the index of X is "+indx)
console.log("the index of Y is "+indy)
//remove duplicate
uniqueArray = array_one.filter(function(item, pos) {
return array_one.indexOf(item) == pos;
})
console.log(uniqueArray)
//return index
var arr = [2,2,3,2,7,3,"a","b","a"];
var indices = [];
arr.filter(function(yourArray, index) {
if(yourArray == "a"){
indices.push(index)
}
});
console.log(indices)
let ranks = [1, 5, 7, 8, 10, 7, 7, 5, 7];
let index = ranks.findIndex(
(rank, index) => rank === 7 && index > 5)
console.log(index);
var array = ['I', 'hAve', 'theSe', 'ITEMs', 'i'],
indexOf = (arr, q) => arr.findIndex(item => q.toLowerCase() === item.toLowerCase());
console.log( indexOf(array, 'i') ) // 0
console.log( indexOf(array, 'these') ) // 2
console.log( indexOf(array, 'items') ) // 3
//finding index from duplicate value of data array
var data=[1,5,1,1,6,4,9,5,6,77,8,77,8,77]
var duplicates=[1,5,6,4,77]
getIndexes(data,duplicates)
function getIndexes(data,duplicates){
var indexes = [];
i = -1;
for (var n = 0; n < duplicates.length; n++){
while ((i = data.indexOf(duplicates[n], i + 1)) != -1){ //i加1从index 1开始再找dup array的第一个字既index(每次只会找一个index)
if (i !== -1 && i !== " "){
indexes.push(i)
}
}
}
//return indexes;
console.log("indexes....:" + indexes);
//for (var n = 0; n < indexes.length; n++){
//sheet.getRange(indexes[n] + 1, column).setBackground("#858383")
//}
}
myFunction()
function myFunction(){
var fruits=["Banana","Orange","Apple","Mango","Banana","Orange","Apple","Mango"];
var a=fruits.indexOf("Apple", 2);
console.log("index of apple occurs from index 3 and afterward: "+a);
}
//=============find index 2===========================
var Arr = [1, 2,3, 'a', 3, 1, 'b', 'a',1,1,"a",4,"b","",""];
var repeatData = getRepeatData(Arr)
log("Repeat:"+repeatData)
var repeatDataIndex = getRepeatDataIndex(repeatData,Arr)
log("Repeat indexes:"+repeatDataIndex)
//========================================================================
function getRepeatData(Arr) {
var repeat = Arr.filter(function(element,index,arr){
return element !=="" && arr.indexOf(element) !== index
});
var result = repeat.filter(function(element,index,arr){
return arr.indexOf(element) === index;
});
return result
}
//====================================
function getRepeatDataIndex(repeatData,Arr) {
var arr = []
for(I in repeatData) {
arr.push(Arr.map((duplicate,i) => duplicate === repeatData[I] ? (i+1) : null)
.filter(i => i !== null))
}
return arr
}
//============直接array内整走空格
var arr = [ 1 , 4 , 9, 16,"a "];
var newArr = arr.map(str => str.toString().trim());
console.log(newArr) //[1,4,9,16,'a']
//========2 array find duplicates========
var j = ['IdA', 'ket', 'IdC', 'IdD', 'chj'];
var k = ['ket', 'chj', 'IdF', 'IdA'];
var r = j.filter(function(val) {
return k.indexOf(val) != -1;
});
console.log(r);
var repeatD = r
log("Repeat:"+repeatD)
var repeatDataIndexj = getRepeatDataIndex(repeatD,j)
log("Repeat indexes for array j:"+repeatDataIndexj)
var repeatDataIndexk = getRepeatDataIndex(repeatD,k)
log("Repeat indexes for array k:"+repeatDataIndexk)
//====================================
function getRepeatDataIndex(repeatD,j) {
var arr = []
for(I in repeatData) {
arr.push(j.map((duplicate,i) => duplicate === repeatD[I] ? (i) : null) //(i+1) for google sheet row 1 or col 1
.filter(i => i !== null))
}
return arr
}
function getRepeat(combinedA) {
var repeat = combinedA.filter(function(element,index,arr){
return element !=="" && arr.indexOf(element) !== index
});
var result = repeat.filter(function(element,index,arr){
return arr.indexOf(element) === index;
});
return result
}
function getRepeatDataIndex(repeatD,j) {
var arr = []
for(I in repeatData) {
arr.push(j.map((duplicate,i) => duplicate === repeatD[I] ? (i) : null) //(i+1) for google sheet row 1 or col 1
.filter(i => i !== null))
}
return arr
}
Array.filter(element => element.trim() !==null && element.trim() !==""); //remove array elements with null or space, only return elements with data inside the array