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 fiddler, Fiddling with text fields that look like dates , More sheet data fiddling.