Is there a way to keep two sheets synchronized?

Question

There isn't a way to share only one sheet of one spreadsheet in Google Sheets. So, you have to share an entire spreadsheet. So, I was thinking in writing a script to synchronize two sheets (each one in a different spreadsheet). I thought using a function to get rows as array to do this. Is there a better strategy to do that?

Swatter

Do you expect collaborators to edit information in the shared sheets, or are they read-only? – Brewis

Yes, I expected they can edit one of the sheets (the shared one). – Swatter

Not sure but have you tried =importrange() function instead of any scripts... – Concussion

I'm loocking for the documentation about =importrange(). But I think that, with import range, the sync occurs only from on side (echo sync), is that right? Could you put your comment as an answer? – Swatter

Answer 1

One way you could accomplish this is by adding a script to both spreadsheets that copies it's contents to the other spreadsheet on a change trigger. For example if you were to add something like the below to both spreadsheets, swapping the source and destination information around.

var sourceSpreadsheetID = "ID HERE";
var sourceWorksheetName = "SHEET NAME HERE";
var destinationSpreadsheetID = "ID HERE";
var destinationWorksheetName = "SHEET NAME HERE";

function importData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}

Just add a change trigger for the importData function and then when any changes are made to either document it will copy the contents to the other spreadsheet, thus keeping the both synced.

Obviously if both spreadsheets are being updated at the same time you will run into trouble.

Synchrocyclotron

Agree with last comment in your answer but the OP needed a one direction synchronization so this case should not happen no? – Phreno

Hi @Sergeinsas Yes if only one way sync is needed then could just add it to one sheet and forget the other. – Synchrocyclotron

Hi, @vletech, it seems like magic! I'll test if it works. Thanks for your time (and brain!). – Swatter

Hi, @vletech, by "change trigger" you mean one of the 3 available trigers: on edit, on open, on submit form? (since there arent't a specific "on change" trigger? – Swatter

@Swatter hmm that's strange I have a fourth which is onChange. I've just tried onEdit and it appears to be acting the same. Is this working for you? – Synchrocyclotron

Hi, @vletech, I tryied onEdit(), but it didn't work. About onChange(), I didn't find it in the documentation page I read (maybe I read an outdated one). I'll ask another question based on your code. I think you give the right direction, now I have to put the gears to work. Thanks again! – Swatter

There is a bug in the code above. It only works when you add values. It fails to see removal of values. So it only sync "add", not sync "sync". If I manage to find the fix myself, I'll post it here... – Oxidate

ok got it: simply add "toWorksheet.clear()" before the toRange statement. Guess it might get long if there is a LOT of data in the test. For my needs, the content isn't too big so it works – Oxidate

Watch out for the bug in onEdit() where is doesn't fire if you undo a delete: code.google.com/p/google-apps-script-issues/issues/…. – Cutaneous

Answer 2

Two-way synchronization is a hard problem, but one that is solved quite effectively by Google Docs. Your concern about need-to-know, however, is a common one when sharing spreadsheets. Your best option in your example is probably to change which sheet is considered the 'master' of the shared data.

Everyone who needs to edit spreadsheet content should do that on the same 'master' sheet, taking advantage of the sharing and revision tracking provided by Google Docs. If you need or wish to combine all or part of that sheet with other sheets, for example to perform review alongside other data that is privileged and unavailable to all editors of the master sheet, then use the ImportRange() spreadsheet function in the secondary sheet to pull from the master sheet, effectively a 1-way sync.

Brewis

Hi, @Mogsdad, one problem with importrange() is that comments are not syncronized. Do you have any suggestion to solve this? (comments syncronization) – Swatter

Other than copying the entire spreadsheet, there's nothing you can do to copy comments until Issue 2566 is fixed. Funny story - Google marked the issue as "Triaged" the same day they deprecated all Comments methods. See setComment(). If you abandon comments and use notes instead, you'll be able to copy them by script. – Brewis

Answer 3

There is no built-in method to keep two sheets synchronized.

First, you can decide on the synchronization scope: whether to include only values or to incorporate other properties like cell and content formatting, rows and columns, tab color, protections, etc.

If you only want the values from one sheet on the second sheet, you might use the built-in function IMPORTRANGE.

Another option is to use Google Apps Script, which can help you sync the sheets automatically or on demand.

The most efficient way might be to use the Advanced Sheets Service, more specifically, using batchUpdate. The simplest two-way synchronization of values is to use the Spreadsheet Service (SpreadsheetApp) together with on edit and on change triggers.

If you want a value-only two-way sync, you might want to create a library to hold a single copy of the primary function and onEdit simple trigger on each spreadsheet.

Below is a sample script for syncing values, assuming that only one cell is edited, the edits are done slowly, and both spreadsheets are not used simultaneously.

Disclaimer: I have not tested this code yet.

/**
 * Primary function to be included in a library to make it easier to maintain / extend.
 *
 * @param {EditEventObject} e Edit event object
 * @param {string} targetId Id of the spreadsheet to which the values will be pushed.
 */
function sync(e, targetId){
   if( // The edited range has only one cell
       e.range.rowStart === e.range.rowEnd 
       && e.range.columnStart === e.range.columnEnd
     ){
        const target = SpreasheetApp.openById(targetId);
        const name = e.range.getSheet().getName();
        const sheet = target.getSheetByName(name);
        const range = sheet.getRange(e.range.rowStart, e.range.columnStart);
        if(e.value){
          range.setValue(e.value);
        } else {
          range.clearContent();
        }
     } else {
       // do nothing
     }
}

Let's say that the library identifier is MyLibrary, the code to be included in the bounded Apps Script project to each spreadsheet is the following

const targetId = "FILE_ID_OF_THE_TARGET_SPREADSHEET";

function onEdit(e){
   MyLibrary.sync(e, targetId);
}

To sync row and column insertion/deletion, an installable on-change trigger should be used in addition to the on-edit trigger.

Burstone