Compare two sheets and append new data

Question

Long time user, first time caller. I recently made and account and am loving the help this site provides so I thought I would look for some of my own.

So I have been able to parse the two sheets ("Grab" and "Copy"), and update dates that have changed. ("Grab" gathers new data and is copied to "Copy" which is where the email script lies). It checks the rows for matching data and updates the rest of the row with any new data. But I cannot figure out how to get new rows from "Grab" to move to "Copy". the code below takes the ranges in grab and copies any differences to the Copy sheet.

**Is there a way to append the new data to "Copy"? If a new row appears in the grab, append it to the Copy sheet. **

Here is a link to a sheet that is very similar to my setup:

https://docs.google.com/spreadsheets/d/1kdn48lel-0er6ACZRjxSUHgYJQobDj2nHrnFq2G2Hm8/edit?usp=sharing

The code that checks and fixes data is in the script editor of that sheet, but I will also post it below.

function mergeData()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Copy");
  var sheet2 = ss.getSheetByName("Grab");
  var rangeSheet1 = sheet1.getRange(3, 1, sheet1.getLastRow() + 1, sheet1.getLastColumn() -7);
  var dataSheet1 = rangeSheet1.getValues();
  var displaySheet1 = rangeSheet1.getDisplayValues();
  var rangeSheet2 = sheet2.getRange(2, 1, sheet2.getLastRow(), sheet2.getLastColumn());
  var dataSheet2 = rangeSheet2.getValues();
  var displaySheet2 = rangeSheet2.getDisplayValues();

  for (var i = 0; i < dataSheet1.length; i++)
   {
    for (var j = 0; j < dataSheet2.length; j++)
     {
      if (displaySheet1[i][0] == displaySheet2[j][0])
       {
        if (dataSheet1[i].length != dataSheet2[j].length)
         {
          for (var k = 0; k < dataSheet1[i].length - dataSheet2[j].length; k++)
           {
            dataSheet2[j].push(dataSheet1[i][dataSheet1[i].length - 1]);
          }
        }
        dataSheet1[i] = dataSheet2[j]
      }
    }
  }
  rangeSheet1.setValues(dataSheet1);
}

How can I also add new rows to the "Copy" sheet?

I have gotten this far by relying on these two answers, but neither seem to append new data:

Update or Replace Row data in other sheet when unique id is found - Google Appscript

Merging google sheets: match with specific col values and merge data in 2 different sheets

these are Stackoverflow links, not external sources

and I cant even vote them up because I dont have the rep :(

Thank you for your help. If I didn't provide enough info or didn't ask correctly, tell me. I'm new.

Answer 1

You can check this sample code:

function mergeData()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Copy");
  var sheet2 = ss.getSheetByName("Grab");
  var sheet1Header = 2;
  var sheet2Header = 1;
  var rangeSheet1 = sheet1.getRange(sheet1Header + 1, 1, sheet1.getLastRow() - sheet1Header);
  var displaySheet1 = rangeSheet1.getDisplayValues();
  var rangeSheet2 = sheet2.getRange(sheet2Header + 1, 1, sheet2.getLastRow() - sheet2Header, sheet2.getLastColumn());
  var displaySheet2 = rangeSheet2.getDisplayValues();
  Logger.log(displaySheet1);
  Logger.log(displaySheet2);

  displaySheet2.forEach( data => {

   Logger.log(data);
   var regNo = data[0];
   Logger.log(regNo);
   var textFinder = rangeSheet1.createTextFinder(regNo);
   var match = textFinder.findNext();
   if(match){
     Logger.log("UPDATE DATA");
     //Reg. No found in Copy Sheet, Update data
     sheet1.getRange(match.getRow(),1,1,data.length).setValues([data]);
     
   }
   else {
     //Add new Reg. No to Copy Sheet
     Logger.log("APPEND DATA");
     sheet1.appendRow(data);
   }
  });

}

• You can check the execution logs to further understand what the code does. You can remove it after if it is not necessary anymore.

What it does?

1. Get the range of valid data (without header) in Copy Sheet Column A. We will use this range later to create a text finder.
2. Get the all the data without header in Grab Sheet. Set it to displaySheet2 variable
3. Loop each row of data in displaySheet2. Check if current row's regNo data exists in rangeSheet1 (The range we obtained in step 1) using Range.createTextFinder(findText). It will return a TextFinder obj, use TextFinder.findNext() to get the match range. If the current regNo string was not found in the range selected, it will return a null value.
4. If match was found, it will return a Range pointing to the matched cell. Update the data in Copy Sheet by selecting the range based on the matched cell's row index.
5. If match was not found, append the current row data using Sheet.appendRow(rowContents)

Output: