Match data based on id between sheets of one googlesheet &extract alphanumeric values in separate column

Question

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");
      }
    }
  }
}

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
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

4thAnd1 · Accepted Answer · 2025-02-03 10:58:00Z

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

It worked partial while id payment id are present in deposit id but it is showing mismatch — amrita, Commented Feb 4 at 10:41
Please provide a sample spreadsheet where we can reproduce the situation — 4thAnd1, Commented Feb 4 at 13:46
Can we compare numeric values with alphanumeric values using appscript? — amrita, Commented Feb 10 at 15:12

doubleunary · Accepted Answer · 2025-02-05 10:20:08Z

0

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().

edited Feb 5 at 10:20

answered Feb 3 at 14:49

doubleunary

19.7k10 gold badges35 silver badges76 bronze badges

it worked partial......some payment id exist in deposit file but it is resulting mismatch for them
– amrita
Commented Feb 4 at 10:40
If the formula shows "Mismatch", chances are that the payment ID doesn't appear in deposits. Look for differences such as leading or trailing spaces. Edit your question and add 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 4 at 18:35
yes there is trailing spaces in id coulumn than how can i avoid it
– amrita
Commented Feb 5 at 9:40
1

As i mentioned that one file was in CSV format which was converted to xlsx thats why there is spaces and id in format in "1.72609E+11"
– amrita
Commented Feb 5 at 10:03
1

To trim leading and trailing spaces, add trim(). Edited the answer. Your "xlsx" reference makes it unclear whether the file is a Microsoft Excel file or a Google Sheet. If you need more help, please share a sample spreadsheet.
– doubleunary
Commented Feb 5 at 10:20

| Show 2 more comments

Collectives™ on Stack Overflow

Match data based on id between sheets of one googlesheet &extract alphanumeric values in separate column

2 Answers 2

Match Data based on ID

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Match Data based on ID

Related