A common thing to do is to update a master sheet from an update sheet – where you want to replace master items with updates, or add new items if they are not already in the master.

Consider these examples, which will be matched on the key column – item “a123” and “d123” will be replaced, and item “f778” will be inserted.

A master sheet

an update sheet

giving this result

This is a pretty simple problem, but it’s easy to tie yourself in knots trying to accomplish it in Apps Script. Here’s a “functional” way to approach the problem which can easily be generalized to any sheet combination.

I always work with sheet values using a Fiddler as described in  A functional approach to fiddling with sheet data and More sheet data fiddling .This class – Fiddler can be found in my cUseful library.

Here is the key, and it is also on github

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

Reading the sheet

Just create an instance for each sheet and pass the sheet objects that need to be manipulated.

 var MASTERID = "1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o";
  var UPDATEID = MASTERID;
  var MASTERNAME = "master";
  var UPDATENAME = "updates";
  
  // get all the master and update data
  var masterFiddler = new cUseful.Fiddler (SpreadsheetApp.openById(MASTERID).getSheetByName(MASTERNAME));
  var updateFiddler = new cUseful.Fiddler (SpreadsheetApp.openById(UPDATEID).getSheetByName(UPDATENAME));

Next we use the contents of the update sheet to drive the processing using an anonymous function to work on each update row

// now we drive the update off the update inserting as required
  updateFiddler.getData().forEach (function (updateRow) {

We are looking to see which master rows have the same key as the update row. We don’t need to care about column numbers, as Fiddler takes care of all that. We can address columns by their heading

// get matches on this key
    var matches = masterFiddler.selectRows ("key" , function (value) {
      return value === updateRow.key;
    });

Matches will be an array of row numbers that have a matching key. If it’s an existing item, we’ll find one or more matches, so just replace the data for each matching row with the data from the update row. You’d probably throw an error here if matches.length > 1, but you may be allowing duplicates as in this example.

// it's an existing item
    if (matches.length) {
      // assume its okay to have duplicate keys and update them all for this example
      matches.forEach (function (match) {
        masterFiddler.getData()[match] = updateRow;
      });
    }

If it’s a new item, then just add it at the end

// its an update
    else {
      // insert 1 row at end
      masterFiddler.insertRows ( null , 1 , updateRow);
    }

and close off the anonymous function processing each of the update rows

});

Now we’re done and just need to write out the updated data, but’s let’s assume it needs to be sorted first.

// might want to sort the master by key now
  masterFiddler.setData(masterFiddler.sort ("key"));

and finally write the updated values back to the original sheet

// and write the updated data
  masterFiddler.dumpValues ();

Here’s the whole thing together.

function updater () {
  
  var MASTERID = "1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o";
  var UPDATEID = MASTERID;
  var MASTERNAME = "master";
  var UPDATENAME = "updates";
  
  // get all the master and update data
  var masterFiddler = new cUseful.Fiddler (SpreadsheetApp.openById(MASTERID).getSheetByName(MASTERNAME));
  var updateFiddler = new cUseful.Fiddler (SpreadsheetApp.openById(UPDATEID).getSheetByName(UPDATENAME));

  // now we drive the update off the update inserting as required
  updateFiddler.getData().forEach (function (updateRow) {
    
    // get matches on this key
    var matches = masterFiddler.selectRows ("key" , function (value) {
      return value === updateRow.key;
    });
    
    // it's an existing item
    if (matches.length) {
      // assume its okay to have duplicate keys and update them all for this example
      matches.forEach (function (match) {
        masterFiddler.getData()[match] = updateRow;
      });
    }
    // its an update
    else {
      // insert 1 row at end
      masterFiddler.insertRows ( null , 1 , updateRow);
    }
    
  });
 
  // might want to sort the master by key now
  masterFiddler.setData(masterFiddler.sort ("key"));
  
  // and write the updated data
  masterFiddler.dumpValues ();

}

For a lot more on fiddler, and videos too, see A functional approach to fiddling with sheet data,  Unique values with data fiddlerFiddling with text fields that look like dates , More sheet data fiddling.

Fiddler now supports joins to merge matching columns from multiple sheets

You may have come across my post on SQL for Apps Script it's here where I shared a library for ...

A fourth way to preserve and create formulas with Fiddler for Sheets, plus some more new methods

This is (probably) the last in a series of posts describing how Fiddler for Sheets can be used to help ...

A third way to preserve formulas with fiddler, plus 2 new methods

In 2 ways to create and preserve formulas with fiddler for Google Apps Script I gave some examples of how ...

2 ways to create and preserve formulas with fiddler for Google Apps Script

I've had a few queries from fiddler users about how to deal with formulas. Fiddler is primarly for abstracting spreadsheet ...

Handly helper for fiddler

If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and ...

Optimize updates to Google Sheets with fiddler fingerprints

If you use some of my libraries, you may have come across the Fiddler class, which abstracts sheet data and ...

Fiddler and rangeLists

Header formatting with fiddler  shows how to use fiddler to easily set formats for your sheet headings and Formatting sheet column ...

Formatting sheet column data with fiddler

Header formatting with fiddler  shows how to use fiddler to easily set formats for your sheet headings. here's how to ...

Header formatting with fiddler

When playing around with sheet data, I always find myself using the Fiddler object - some more posts on this ...

Populating sheets with API data using a Fiddler

Another place that Fiddler can be handy is if you are populating a sheet with data retrieved from an API ...

A functional approach to updating master sheet with Fiddler

A common thing to do is to update a master sheet from an update sheet - where you want to ...

Unique values with data fiddler

Fiddler is a way to handle spreadsheet data in a functional way, as described in A functional approach to fiddling ...

Fiddler – A functional approach to fiddling with sheet data

  I wondered if there might be more functional programming, more declarative approach to the problem of fiddling around with ...

Color scales, custom schemes and proxies with Apps Script

A couple of my favorite topics - object proxies and fiddling with colors - in the same article today. We'll ...

Find nearest matches in color schemes with Apps Script

Here's another article on playing around with color in Apps Script. Let's say you have a defined list of colors ...

Detect fake news with Google Fact Check tools

I came across Google's FactCheckTools API today, so I thought it might be fun to add to my bmApiCentral Apps ...