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.

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

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

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.

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

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.

and finally write the updated values back to the original sheet

Here’s the whole thing together.

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.

More on this topic available below (click to expand)

Styling Gmail html tables

When you use Gmail (or Sites), you are allowed to provide HTML input so you can have control over the content. So if you wanted to send an email with red text, you could do […]

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 do the same for column formats. This is quite a long read, as there’s a […]

Header formatting with fiddler

When playing around with sheet data, I always find myself using the Fiddler object – some more posts on this below if you are not familiar with it already. More on this topic available here […]

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. The examples use the Itunes API which will return tabular data as an array of […]

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 […]

Fiddling with text fields that look like dates

Let’s say you have a field in a sheet that says January 2017 that you want to treat as text, but it looks like a date. In the Sheet UI you simply add a single […]

More sheet data fiddling

In A functional approach to fiddling with sheet data and Unique values with data fiddler I showed a way of working with sheet data without bothering too much about column numbers and all that stuff […]

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 with sheet data This class – Fiddler can be found in my cUseful library. Here’s […]

Sorting Google Sheet DisplayValues

If you use getDisplayValues to retrieve data from sheets, you can hit a problem with sorting. Perhaps numbers have been converted to strings, or dates are in an unsortable format for example 1,2,3,101 would get sorted […]

A functional approach to fiddling with sheet data

I wondered if there might be a more functional programming , more declarative approach to the problem of fiddling around with spreadsheet data. Using the API to insert rows and worrying about column numbers and […]

For more like this see Google Apps Scripts Snippets