I'm working from the solution provided HERE to pare two arrays. The example provided returns values found in both arrays to Array1 (same) and values only found on one or the other two Array2 (diff).

ISSUE: When I apply it to my own script, valuesDATA returns nothing and valuesCheckSeeding returns ALL values from both arrays

DESIRED RESULT: I have two arrays that I'd either like to create a third out of, or only select values from the first array, valuesDATA which are NOT present in the second, valuesCheckSeeding. Using the solution above, I was trying to have all values not found in valuesCheckSeeding AND valuesDATA pushed to valuesDATA.

SAMPLE OF valuesDATA: "U09 F Harford FC Hill/Healey - A MD CMSA Girls Saturday U09 A/B North Premier - Top TID0118"

What am I doing wrong? I tinkered with changing matchfound==false and matchfound=true in the loop, but that still didn't give me the desired result.

MOST RELEVANT SNIPPET

var matchfound = false;

for (var i = 0; i < valuesDATA.length; i++) {
  matchfound=false;
    for (var j = 0; j < valuesCheckSeeding.length; j++) {
      if (valuesDATA[i] == valuesCheckSeeding[j]) {
        valuesCheckSeeding.splice(j, 1);
        matchfound=true;
        continue;
        }
        }
          if (matchfound==false) {
            valuesCheckSeeding.push(valuesDATA[i]);
            valuesDATA.splice(i, 1);
            i=i-1;
            }
          }

WORKIG SCRIPT EDITED FROM COMMENTS/ANSWERS BELOW

//UPDATE SEEDING SHEET
function updateSeedingSheet() {

var today = Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "MM/dd/yyyy hh:mm a");   

//INPUT SHEET INFO
var inputCurrentRow = 4;
var inputCurrentColumn = 20;
var inputNumRows = 1000;
var inputNumColumns =1;

var ssInput = SpreadsheetApp.openById('1Wzg2BklQb6sOZzeC0OEvQ7s7gIQ07sXygEtC0CSGOh4');
var sheetDATA = ssInput.getSheetByName('DATAREF');
var rangeDATA = sheetDATA.getRange(inputCurrentRow, inputCurrentColumn, inputNumRows, inputNumColumns);
var valuesDATA = rangeDATA.getValues();


//SEEDING SHEET INFO
var seedingCurrentRow = 4;
var seedingCurrentColumn = 1;
var seedingNumRows = 1000;
var seedingNumColumns = 1;

var ssSeeding = SpreadsheetApp.openById('1DuCHeZ3zba-nHq-7vYTrylncPGqcA1J9jNyW9DaS3mU');
var sheetSeeding = ssSeeding.getSheetByName('Seeding');
var rangeCheckSeeding = sheetSeeding.getRange(4, 102, 1000, 1);
var columnToClear = sheetSeeding.getRange(seedingCurrentRow, seedingCurrentColumn, seedingNumRows, seedingNumColumns);
var valuesCheckSeeding = rangeCheckSeeding.getValues();


//METHOD TO FILTER
valuesCheckSeeding = valuesCheckSeeding.map(function(e){return e[0];}); //flatten this array
var filteredArr = valuesDATA.filter(function(e){
    return !(this.indexOf(e[0])+1);
},valuesCheckSeeding);
Logger.log(filteredArr);
Logger.log(filteredArr.length);

var rangeSeeding = sheetSeeding.getRange(seedingCurrentRow, seedingCurrentColumn, filteredArr.length, seedingNumColumns);

sheetSeeding.getRange('A1').setValue(today);
columnToClear.clearContent();
rangeSeeding.setValues(filteredArr);



/*
//ALTERNATIVE METHOD USING LOOPS
for (var i = 0; i < valuesDATA.length; i++) {
    for (var j = 0; j < valuesCheckSeeding.length; j++) {
      if (valuesDATA[i][0] == valuesCheckSeeding[j][0]) {
        valuesDATA.splice(i, 1);
        i--; //account for the splice
        break; //go to next i iteration of loop
          }
       }
     }
          Logger.log("VALUES DATA:" + valuesDATA);
          Logger.log("VALUES CHECK SEEDING: " + valuesCheckSeeding);         

//sheetSeeding.getRange('A1').setValue(today);
//rangeSeeding.clearContent();
//rangeSeeding.setValues(valuesDATA); //INCORRECT RANGE HEIGHT, WAS 71 BUT SHOULD BE 1000 - Is splice affecting this? 

*/

}//END FUNCTION

I'm working from the solution provided HERE to pare two arrays. The example provided returns values found in both arrays to Array1 (same) and values only found on one or the other two Array2 (diff).

ISSUE: When I apply it to my own script, valuesDATA returns nothing and valuesCheckSeeding returns ALL values from both arrays

DESIRED RESULT: I have two arrays that I'd either like to create a third out of, or only select values from the first array, valuesDATA which are NOT present in the second, valuesCheckSeeding. Using the solution above, I was trying to have all values not found in valuesCheckSeeding AND valuesDATA pushed to valuesDATA.

SAMPLE OF valuesDATA: "U09 F Harford FC Hill/Healey - A MD CMSA Girls Saturday U09 A/B North Premier - Top TID0118"

What am I doing wrong? I tinkered with changing matchfound==false and matchfound=true in the loop, but that still didn't give me the desired result.

MOST RELEVANT SNIPPET

var matchfound = false;

for (var i = 0; i < valuesDATA.length; i++) {
  matchfound=false;
    for (var j = 0; j < valuesCheckSeeding.length; j++) {
      if (valuesDATA[i] == valuesCheckSeeding[j]) {
        valuesCheckSeeding.splice(j, 1);
        matchfound=true;
        continue;
        }
        }
          if (matchfound==false) {
            valuesCheckSeeding.push(valuesDATA[i]);
            valuesDATA.splice(i, 1);
            i=i-1;
            }
          }

WORKIG SCRIPT EDITED FROM COMMENTS/ANSWERS BELOW

//UPDATE SEEDING SHEET
function updateSeedingSheet() {

var today = Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "MM/dd/yyyy hh:mm a");   

//INPUT SHEET INFO
var inputCurrentRow = 4;
var inputCurrentColumn = 20;
var inputNumRows = 1000;
var inputNumColumns =1;

var ssInput = SpreadsheetApp.openById('1Wzg2BklQb6sOZzeC0OEvQ7s7gIQ07sXygEtC0CSGOh4');
var sheetDATA = ssInput.getSheetByName('DATAREF');
var rangeDATA = sheetDATA.getRange(inputCurrentRow, inputCurrentColumn, inputNumRows, inputNumColumns);
var valuesDATA = rangeDATA.getValues();


//SEEDING SHEET INFO
var seedingCurrentRow = 4;
var seedingCurrentColumn = 1;
var seedingNumRows = 1000;
var seedingNumColumns = 1;

var ssSeeding = SpreadsheetApp.openById('1DuCHeZ3zba-nHq-7vYTrylncPGqcA1J9jNyW9DaS3mU');
var sheetSeeding = ssSeeding.getSheetByName('Seeding');
var rangeCheckSeeding = sheetSeeding.getRange(4, 102, 1000, 1);
var columnToClear = sheetSeeding.getRange(seedingCurrentRow, seedingCurrentColumn, seedingNumRows, seedingNumColumns);
var valuesCheckSeeding = rangeCheckSeeding.getValues();


//METHOD TO FILTER
valuesCheckSeeding = valuesCheckSeeding.map(function(e){return e[0];}); //flatten this array
var filteredArr = valuesDATA.filter(function(e){
    return !(this.indexOf(e[0])+1);
},valuesCheckSeeding);
Logger.log(filteredArr);
Logger.log(filteredArr.length);

var rangeSeeding = sheetSeeding.getRange(seedingCurrentRow, seedingCurrentColumn, filteredArr.length, seedingNumColumns);

sheetSeeding.getRange('A1').setValue(today);
columnToClear.clearContent();
rangeSeeding.setValues(filteredArr);



/*
//ALTERNATIVE METHOD USING LOOPS
for (var i = 0; i < valuesDATA.length; i++) {
    for (var j = 0; j < valuesCheckSeeding.length; j++) {
      if (valuesDATA[i][0] == valuesCheckSeeding[j][0]) {
        valuesDATA.splice(i, 1);
        i--; //account for the splice
        break; //go to next i iteration of loop
          }
       }
     }
          Logger.log("VALUES DATA:" + valuesDATA);
          Logger.log("VALUES CHECK SEEDING: " + valuesCheckSeeding);         

//sheetSeeding.getRange('A1').setValue(today);
//rangeSeeding.clearContent();
//rangeSeeding.setValues(valuesDATA); //INCORRECT RANGE HEIGHT, WAS 71 BUT SHOULD BE 1000 - Is splice affecting this? 

*/

}//END FUNCTION

• javascript
• google-apps-script

Share Improve this question Follow edited Aug 1, 2018 at 10:31 N.O.Davis asked Aug 1, 2018 at 2:50 N.O.DavisN.O.Davis 51333 gold badges1010 silver badges2222 bronze badges 5

• Don't splice an array while iterating over it, else the logic will be quite hard to understand... if you want to split one array into two, better to push to two entirely new arrays – CertainPerformance Commented Aug 1, 2018 at 2:51
• 1 please show an example of the contents of valuesDATA (it's all just primitive values, right? no objects?) knowing {key: 1} !== {key: 1} may shed some light if the contents are not simple primitive values – Jaromanda X Commented Aug 1, 2018 at 2:52
• "U09 F Harford FC Hill/Healey - A MD CMSA Girls Saturday U09 A/B North Premier - Top TID0118" is a sample of the content of valuesDATA Pulls directly from the spreadsheet. I have two arrays that I'd either like to create a third out of, or only select values from the first array, valuesDATA which are not present in the second valuesCheckSeeding. I'll update my question to reflect both points. – N.O.Davis Commented Aug 1, 2018 at 2:56
• 1 Do yourself a favor and review language-agnostic algorithms for paring sets. Then review paring a set with an iterable. Then review constructing a set from an iterable. – tehhowch Commented Aug 1, 2018 at 4:01
• 1 (you have to do it the old -fashioned way since Apps Script has no Set class yet.) – tehhowch Commented Aug 1, 2018 at 4:08

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 7

V8(ES2016 update):

• You can use newer and efficient set class

const array1 = [[1],[2],[3]],
array2 = [[1],[3],[4]],
set = new Set(array2.flat())
console.info(array1.filter(e => !set.has(e[0])))
//expected output [[2]]

---

• You're checking a 2D array. You'd need to use [i][0] and [j][0]
• You can try only splicing valuesDATA

Try

for (var i = 0; i < valuesDATA.length; i++) {
    for (var j = 0; j < valuesCheckSeeding.length; j++) {
      if (valuesDATA[i][0] == valuesCheckSeeding[j][0]) {
        valuesDATA.splice(i, 1);
        i--; //account for the splice
        break; //go to next i iteration of loop
        }
     }
}
Logger.log(valuesDATA);

---

Alternatively, try

valuesCheckSeeding = valuesCheckSeeding.map(function(e){return e[0];}); //flatten this array
var filteredArr = valuesDATA.filter(function(e){
    return !(this.indexOf(e[0])+1);
},valuesCheckSeeding);
Logger.log(filteredArr);