How to match data based on id in multiple google sheets having huge data(approx 27000 rows)?

Question

Deposit report sheet(10000 rows) and payment sheet which was actually .CSV file (26000 rows).And now i want to check that Id of payment sheet exist in deposit sheet or not. Here is an Appscript i applied.

function compareColumns() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = spreadsheet.getSheetByName("rps_243_payments_datetime_since=2025-01-01&datetime_until=2025-01-01");
  var sheet2 = spreadsheet.getSheetByName("deposit-report");
  var sheet3 = spreadsheet.getSheetByName("Comparison"); // Output sheet
  var range1 = sheet1.getRange("A2:A"); // Column A from Sheet1
  var range2 = sheet2.getRange("I2:I"); // Column A from Sheet2
  var values1 = range1.getValues();
  var values2 = range2.getValues();
  var outputRange = sheet3.getRange("B2"); // Starting cell for output in Sheet3
  for (var i = 0; i < values1.length && values1[i][0] != ''; i++) {
    for (var j = 0; j < values2.length && values2[i][0] != ''; ; j++)
    {
      if (values1[i][0] !== values2[j][0]) {
        sheet3.getRange("B" + (i + 2)).setValue("Mismatch");
        sheet3.getRange(i + 2, 1).setBackground("red");
      }
      else {
        sheet3.getRange("B" + (i + 2)).setValue("Match");
        sheet3.getRange(i + 2, 1).setBackground("green");
      }
    }
  }
}

Answer 1

Match Data based on ID

Your code performs that way it is as you are writing the results one at a time. I modified your code to not write the results inside a loop but instead write it in one go which makes it way faster.

function compareColumns() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = spreadsheet.getSheetByName("rps_243_payments_datetime_since=2025-01-01&datetime_until=2025-01-01");
  var sheet2 = spreadsheet.getSheetByName("deposit-report");
  var sheet3 = spreadsheet.getSheetByName("Comparison"); // Output sheet
  
  var range1 = sheet1.getRange("A2:A"); // Column A from Sheet1
  var range2 = sheet2.getRange("I2:I"); // Column I from Sheet2
  
  var values1 = range1.getValues();
  var values2 = range2.getValues();
  
  var depositIdsArray = [];
  for (var j = 0; j < values2.length; j++) {
    if (values2[j][0] != '') {
      depositIdsArray.push(values2[j][0]);
    }
  }

  var output = [];
  var backgroundColors = [];
  for (var i = 0; i < values1.length && values1[i][0] != ''; i++) {

    if (depositIdsArray.indexOf(values1[i][0]) !== -1) {
      output.push(["Match"]);
      backgroundColors.push(["#00FF00"]);
    } else {
      output.push(["Mismatch"]);
      backgroundColors.push(["#FF0000"]);
    }
  }

  sheet3.getRange(2, 2, output.length, 1).setValues(output);
  sheet3.getRange(2, 1, backgroundColors.length, 1).setBackgrounds(backgroundColors);
}

Note: My code is faster than yours but the performance still depends on the size of your data set as well.

References:

Arrays in apps Script

Loops in Apps Script