0
198
VistasHwo to compare these two arrays the way they are built using GAS?
Array 1 values is built by getting the values from the ranges mapped through a range list and Arra2 is a row obtained from another range. I see that these arrays' structures are not similar and it's not possible to compare them the way they are, but what can I change in the code so that the comparison works?
These is how the data look:
This is the code I'm getting humiliated by:
function saveEditedItem() {
const ssCadProd = SpreadsheetApp.getActiveSpreadsheet();
const sheetCadProd = ssCadProd.getSheetByName('Cadastro de Produto');
const ref = sheetCadProd.getRange('B5').getValue();
const variac = sheetCadProd.getRange('K5').getValue();
//MAPS DATA RANGES
const dataRng = [
"B5", "D5", "G5", "I5", "K5",
];
const rngList = sheetCadProd.getRangeList(dataRng).getRanges();
//THROW MAPPED DATA INTO AN ARRAY
let values = [];
for (let i = 0; i < rngList.length; i++) {
values = [].concat(values, rngList[i].getValue());
}
Logger.log('Values: ' + values)
Logger.log('Values Length: ' + values.length)
//GETS THE ITEM WITH THE SAME REF AND VARIAC
const produtosDB = sheetCadProd.getRange(10, 1, 3, 5).getValues();
let prodSelecDB = [];
for (let a = 0; a < produtosDB.length; a++) {
if (produtosDB[a][0] == ref && produtosDB[a][4] == variac) {
prodSelecDB.push(produtosDB[a])
}
}
Logger.log('prodSelecDB: ' + prodSelecDB)
Logger.log('prodSelecDB Length: ' + prodSelecDB.length)
Logger.log('Stringified Values: ' + JSON.stringify(values))
Logger.log('Stringified ProdSelecDB: ' + JSON.stringify(prodSelecDB))
//COMPARES Values' DATA WITH ProdSelecDB's
var duplicate = false;
for (var x = 0; x < values.length; x++) {
for (var j = 0; j < prodSelecDB.length; j++) {
if (JSON.stringify(values[x]) == JSON.stringify(prodSelecDB[j])) {
duplicate = true;
break;
}
}
}
Logger.log('Are they the same? ' + duplicate)
}
These are the logs:
Here's the link to the file, in case you feel like jumping in: https://docs.google.com/spreadsheets/d/1v1N6rHP7qIheDDUkcQiNfEZ4rmjrguBHYoyWuTuWwKw/edit?usp=sharing
Appreciate your time, as usual!
2 Respuestas
Responde la pregunta0
I think you could look for full row matches this way
function compare() {
const ss = SpreadsheetApp.getActive();
const sh0 = ss.getSheetByName('Sheet0');
const v0s = sh0.getRange(1,1,1,sh0.getLastColumn()).getDisplayValues().map(r => [r[1],r[3],r[6],r[8],r[10]].join(""));
const sh1 = ss.getSheetByName("Sheet1");
const v1s = sh1.getRange(2,1,sh1.getLastRow() - 1,sh1.getLastColumn()).getDisplayValues();
let n = 0;
v1s.forEach((r,i) => {
let j = r.join("");
let idx = v0s.indexOf(j);
if(~idx) {
//there is a match and for this example it occurs on row idx + 1
n++;
}
})
Logger.log('matches: %s',n)
}
Execution log
2:02:36 PM Notice Execution started
2:02:37 PM Info matches: 1.0
2:02:37 PM Notice Execution completed
Sheet0:
Sheet1:
0
I care about reusability and you can build a custom function with the script I made. I didn't know where you're gonna output that code, but I made a console.log() however you can modify it as you need it
const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
function saveEditedItem() {
const array1 = getArray1()
const array2= getArray2('A11:E11')
console.log(compareArr(array1, array2))
return compareArr(array1, array2)
}
function compareArr(arr1, arr2) {
const string1 = JSON.stringify(arr1)
const string2 = JSON.stringify(arr2)
if(string1 === string2){
return true
} else {
return false
}
}
function getArray1() {
const listRange = ['B5', 'D5', 'G5', 'I5', 'K5']
const rngList = ss.getRangeList(listRange).getRanges()
return extractArray(rngList)
}
function getArray2(range){
const array = ss.getRange(range).getValues()
return array[0]
}
function extractArray(rngList) {
let array = [];
for (let i = 0; i < rngList.length; i++) {
array = [].concat(array, rngList[i].getValue());
}
return array
}