how to return values from a list that are not present in another list using google script

user_13926528 0
Viewed 41

I have two lists. List one has all current employee names and list two has all current and past employee names.

I need to develop a script that returns all the past names with the info connected to them on list two. I wrote this code which matches all current names to their current info but cannot figure out how to do the reverse.

function current(){

  var app = SpreadsheetApp;
 
 var activeSheet = app.getActiveSpreadsheet();

  var issued = app.getActiveSpreadsheet().getSheetByName("Issued");

  var waiting = app.getActiveSpreadsheet().getSheetByName("Waiting for Inventory");

  var active = app.getActiveSpreadsheet().getSheetByName("Active Officers");
  
 for (var j = 2; j<100; j++){ 
  
  var officer = issued.getRange(j,11).getValue();
 
  for(var i = 2; i<70; i++){ 

    var officerList = active.getRange(i, 2).getValue();

     if (officer === ""){
       continue; 
      }

     if (officer === officerList){
      var rowInfo = issued.getRange(j,1,1,12).getValues();
      var lastrow = waiting.getLastRow();
      waiting.getRange(lastrow+1,1,1,12).setValues(rowInfo);
      }
    }
  }
}
google-apps-script arrays javascript
1 Answers

The easiest way to retrieve the existance of a string within the array is withindexOf()

  • It will return you -1 if a values is not found
  • Retrieve all officers from active and transform the values to a 1D array with flat()
  • Retrieve all officers in issued with getValues (more efficient than multiple requests with getValue and loop through them
  • This set-up needs only one for loop instead of two which will make your code significantly faster

Sample:

function current(){
  
  var app = SpreadsheetApp;
  
  var activeSheet = app.getActiveSpreadsheet();
  
  var issued = app.getActiveSpreadsheet().getSheetByName("Issued");
  
  var waiting = app.getActiveSpreadsheet().getSheetByName("Waiting for Inventory");
  
  var active = app.getActiveSpreadsheet().getSheetByName("Active Officers");
  
  var officerList = active.getRange(2, 2, 69, 1).getValues().flat();
  var officers = issued.getRange(2,11,99,1).getValues();
  Logger.log(officerList);
  for (var j = 0; j < 99; j++){     
    var officer = officers[j][0];
    if (officerList.indexOf(officer) ==-1){
      Logger.log("officer " + officer + " from Issued does not exist in Active Officers");
      var rowInfo = issued.getRange(j+2,1,1,12).getValues();
      var lastrow = waiting.getLastRow();
      waiting.getRange(lastrow+1,1,1,12).setValues(rowInfo);
    }
  }
}
user_115997890
Related