title: Copying values into a spreadsheet if the value does not exist already - Google Script
body: <p>I'm trying to:</p>
  
  <ol>
  <li>Go into a Drive folder and retrieve the spreadSheetIDs of existing spreadsheets</li>
  <li>Go into each spreadsheet and get some data</li>
  <li>Copy that data into a TargetSheet</li>
  <li>With the condition that the data does not exist already</li>
  </ol>
  
  <p>From 1-3 I have no issues, but I cannot correctly search and match if the data exists already. </p>
  
  <p>This is my code so far. If I run the code twice on the same data set, some data is copied when it should not be, since it already exists.</p>
  
  <p>Any help, please?</p>
  
  <pre><code>function getReportData() {
  
  //Sources
  var reportFolder = DriveApp.getFolderById('ReportFolderID') // Get Status reports folder
  var reportsList = reportFolder.getFiles(); //Returns FileIterator object
  var spreadSheets = [];
  var targetSSheet = SpreadsheetApp.openById('TargetSheetID');
  var targetSheet = targetSSheet.getActiveSheet();
  var lastRow = targetSheet.getLastRow();
  
  var searchRange = targetSheet.getRange(2, 1, lastRow, 2);
  var searchRangeV = searchRange.getValues();
  var allStatuses = [];
  
  
  //Populate the reportSheets list with latest report sheet IDs
  while (reportsList.hasNext()) {
      var reports = reportsList.next(); //Object of type file
      spreadSheets.push(reports.getId());
  }
  
  
  // Loop through the list of report sheets
  for (i = 0; i &lt; spreadSheets.length; i++) {
      var spreadSheet = SpreadsheetApp.openById(spreadSheets[i]);
      var activeSheet = spreadSheet.getActiveSheet();
      var individualStatus = [];
  
  
      // Gets project report data
      var projectName = activeSheet.getRange("B3:B4").getValue();
      var reportDate = activeSheet.getRange("I3:I4").getValue();
      var projectStatus = activeSheet.getRange("G9:i9").getValue();
  
      // Creates reports array
      individualStatus.push(reportDate, projectName, projectStatus);
  
      allStatuses.push(individualStatus)
  
  }
  
     //Cleans the status array of existing ones
  for (j = 0; j &lt; allStatuses.length; j++) {
  
      var searchKey = allStatuses[j][0] + allStatuses[j][1];
      searchKey
  
      Logger.log(searchKey)
  
      for (k = 0; k &lt; searchRangeV.length; k++) {
  
          var matchKey = searchRangeV[k][0] + searchRangeV[k][1];
  
          if (searchKey == matchKey) {
              allStatuses.splice(j, 1)
              break;
          } else {
              Logger.log(searchKey)
              Logger.log(matchKey)
          }
  
      }
  }
  
  //Copies the data to Target Sheet
  for (var project = 0; project &lt; allStatuses.length; project++) {
      //Gets the last row each time it goes through the loop
      var latestRow = targetSheet.getLastRow();
      var lastColumn = targetSheet.getLastColumn();
  
      for (var status = 0; status &lt; allStatuses[project].length; status++) {
  
          targetSheet.getRange(latestRow + 1, status + 1, 1, 1).setValue(allStatuses[project][status])
  
  
      }
  }
  </code></pre>
  
  <p>}</p>