I am working on google sheet which has 3 sheets Deposit report sheet(10000 rows) , payment sheet which was actually .CSV file (26000 rows) and output sheet which will show output by appscript in which matching of id is done betweenthe other two sheets.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");
      }
    }
  }
}

I am working on google sheet which has 3 sheets Deposit report sheet(10000 rows) , payment sheet which was actually .CSV file (26000 rows) and output sheet which will show output by appscript in which matching of id is done betweenthe other two sheets.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");
      }
    }
  }
}

• google-sheets
• google-apps-script
• excel-formula
• google-sheets-formula

• Please provide your sample sheet – 4thAnd1 Commented Feb 3 at 9:09
• 1 Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. – doubleunary Commented Feb 3 at 9:17
• Are the 2 sheets separate files? – 4thAnd1 Commented Feb 3 at 9:17
• 1 Please post sample code/formulas that you have tried so far – 4thAnd1 Commented Feb 3 at 9:56
• 1 Please do NOT share images as the only source of data, to avoid closure of the question. Make sure to add input and expected output as a plain text table to the question. – 4thAnd1 Commented Feb 3 at 9:57

2 Answers 2

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

Put this formula in cell N2 in the payments tab:

=arrayformula(let( 
  payments, trim('rps_243_payments_datetime_since=2025-01-01&datetime_until=2025-01-01'!A2:A), 
  deposits, trim('deposit-report'!I2:I), 
  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().