A functional approach to updating master sheet

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's the key, and it's also on github

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j


Reading the sheets

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're 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 more like this, see Google Apps Scripts snippets. Why not join our forumfollow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.






Comments