CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-sheets-formula
amrita
amrita

Reputation: 59

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

Deposit report sheet(10000 rows) and payment sheet which was actually .CVS file (26000 rows).And now i want to check that Id of payment sheet exist in deposit sheet or not. I tried I used appscript to match data between these two sheets but it is taking long time to work ? I want it to work fast? I need solution whether it is formula for sheet or appscript which is 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 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");
      }
    }
  }
}

Upvotes: -1

Views: 59

Answers (2)

doubleunary
doubleunary

Reputation: 18733

Put this formula in cell N2 in the payments tab:

=let( 
  payments, 'rps_243_payments_datetime_since=2025-01-01&datetime_until=2025-01-01'!A2:A, 
  deposits, 'deposit-report'!I2:I, 
  arrayformula(ifs(
    ifna(match(payments, deposits, 0)), "Match", 
    len(payments), "Mismatch", 
    true, iferror(ø) 
  )) 
)

To color result cells, use conditional formatting.

See let(), arrayformula(), ifs(), ifna() and match().

Upvotes: 0

4thAnd1
4thAnd1

Reputation: 796

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

Upvotes: 1

Related Questions