Copy Notes Between Google Sheets

The Basic Setup

For this example, we have a master Google Sheet with several rows of data organized by location, like this:

The sample master spreadsheet

We want to share child sheets tied to schools A, B, and C with only their relevant data. This first part is done with query and importrange in the following formula in cell A1 of the child spreadsheet.

=query(importrange("masterSheetURL","Sheet1:A:E"),"SELECT * WHERE Col1 CONTAINS 'A'",1)

Query selects an entire row where Column 1 contains the string 'A'. If this were a real situation, the building name would be the imported data, which looks like this:

Imported data into a child spreadsheet using QUERY.

I prefer query because I don't have to select a specific range - it will look at the entire sheet for that data. If you don't want to import notes, this works really well. If you do want to import notes, make sure data is grouped together because you need to determine some offsets when writing to the child sheets. Create a second spreadsheet in masterthat has the following structure:

Helper sheet in master with links to the child sheets

where column A is the building (or location or other selector) and the URL to the child sheet you want to update. Column C will become very important as it holds the offset data for writing notes to the child sheet.

The Script

Copying notes can only be done with the .getNotes() Google Apps Script method. This script looks at the sheet and uses a couple of loops to build arrays to post to the child sheets. The first challenge is to set the offsets. Notice location B in the master sheet is rows six through eight, while on the child sheet it is rows two through four. Without setting an offset, our notes would be written into the wrong rows, and that's no fun.

After running that script, your child sheet will update the offset of each building in Column 3 of the helper sheet in the master copy. I also added an onChange trigger to this function so it runs when rows are inserted anywhere in the sheet.

Now, you're ready to copy notes from one sheet to another.

This script will loop the master spreadsheet and look for a building name it recognizes. If there's a match, it will open the child sheet and set the notes for each row using the offset provided in the previous step. To be honest, this isn't the most elegant solution, but hey, it works.

Just in case of catastrophe, here's another little utility script you can use to clear all notes from the child sheets. This was particularly helpful when I had my offset calculations off by a row.

This is a manual process - there is no edit or change event you can hook into when a note is added or deleted. So, I wrapped all of this into an onOpen simple trigger to add a custom menu.

There are definitely improvements that can be made. Here are all the files as a gist so you can clone, copy/paste, and hack away at your own sheets.

Comments

onOpen

         function reauth() {
            SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet 1').getDataRange().getValues();
         }