CODEWITHSUNDEEP
javascriptgoogle-apps-script
Dan Howard
Dan Howard

Reputation: 316

Checking to see if a value exists anywhere in an array

I have a script that looks at one sheet ("Data") and from a column of dates finds dates that are 3 months in the future and copies those rows into another sheet ("3 Months"). It can do that, Yay!

What I'd also like it to do is check on the "3 Months" sheet for a unique ID on each row and if that Unique ID exists, then don't copy that row from "Data".

It does that, but only if the data on "3 Months" is in the same order as the data in "Data", otherwise it doesn't. Clearly what it's doing is matching the current Unique ID with the equivalent Unique ID in the array. Rather than try and match the current Unique ID with all the ID's in the array and make sure it doesn't match any of them.

This is what I have:

function contractSearch2() {
  //Get all the sheet names as variables
  var dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var threeMonths = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("3 Months");

  //Get all the expiry dates on the data sheet
  var lastRow = dataSheet.getLastRow();
  var endDates = dataSheet.getRange(1, 11, lastRow).getValues();

  //Get today's date
  var today = new Date();

  // Work out when 3 months from now is.
  var threeMonthsOut = new Date(today);
  var CurrentDate = new Date();
  threeMonthsOut.setMonth(CurrentDate.getMonth() + 3);

  // Find months / years that match 3 months out.
  var threeMonthslastRow = threeMonths.getLastRow();
  var CallOffIDs = threeMonths.getRange(1, 4, threeMonthslastRow+1, 1).getValues();
  var test = 0
  var j = 0; 
  //Logger.log(CallOffIDs[1])

  for (var i = 0; i < endDates.length; i++){
    var fDate = new Date(endDates[i][0]);
    // if a date is 3 months in the future
    if (fDate.getMonth() == threeMonthsOut.getMonth() && 
        fDate.getFullYear() == threeMonthsOut.getFullYear()) {
      //get all the row info
      var entry = dataSheet.getRange(i+1, 1, 1, 44).getValues();

      // Check to see if CallOff ID Exists already
      // Get the CallOff ID from the entry row
      var CallOffCheck = entry[0][3];
      ++j;
      for ( test in CallOffIDs) {
        ++test;
        if (CallOffCheck == CallOffIDs[j]) {
          break;
        } else {
          var threeMonthslastRow2 = threeMonths.getLastRow(); 
          threeMonths.getRange(threeMonthslastRow2 + 1, 1, 1, 44).setValues(entry);
          break;  
        }
      }  
    }
  }
}

Since I need to re-order the rows on "3 Months" for another reason and the data on the "Data" sheet is always being updated I'll constantly need to be running the script. Which is why I need it to check all the values.

Thanks in Advance.

Upvotes: 0

Views: 5548

Answers (1)

S Gupta
S Gupta

Reputation: 1587

Array provides four ways of searching:

  • someArray.some - returns true or false based on whether an item was found or not. lets you write a function to do comparison/matching. This method is available in Google Apps Script.
  • someArray.find - returns an element from array and lets you write a function to do comparision/matching. if more than one matching element is present, only the first one is returned. This method is not available in Google Apps Script.
  • someArray.findIndex - returns index of an element from array and lets you write a function to do comparision/matching. if more than one matching element is present, only the first one is returned. This method is not available in Google Apps Script.
  • someArray.indexOf - returns -1 if no match is found otherwise returns a 0 based index of matching element. you have to pass a value to search for and can additionally pass the position of array from which to being the search loopup. This method is available in Google Apps Script.

var beasts = ['ant', 'bison', 'camel', 'duck', 'bison'];

    console.log(beasts.indexOf('bison'));
    // expected output: 1
    
    // start from index 2
    console.log(beasts.indexOf('bison', 2));
    // expected output: 4
    
    console.log(beasts.indexOf('giraffe'));
    // expected output: -1
    
    var foundItem = beasts.find(function(item, arr, index) {
       return item === 'camel'; // my function is searching for camel
    });
    console.log(foundItem);
    // expected output: 'camel'
    
    
    var foundItemIndex = beasts.findIndex(function(item, arr, index) {
       return item === 'camel'; // my function is searching for camel
    });
    console.log(foundItemIndex);
    // expected output: 2

I hope this helps.

Upvotes: 2

Related Questions