Multi-Directional Data Sync In Google Sheets

How To Send Google Form Responses With Google Scripts

In this blog post I will show you how to sync data between a set of specified worksheets in a Google Sheet. This code is written in Google Script, which is just vanilla JS with some custom functions specific to Google Sheets.

An example use case would be if you have one very complicated Master sheet that many people use. Each person only needs several columns, so they hide the columns they don’t need. Another person using the spreadsheet may need to unhide or hide a different set of columns. This can get annoying with people getting in each other’s way.

With a multi-directional data sync between worksheet, you can have separate worksheets for each business unit or person that then feed into the Master sheet.

There are two approaches to achieve this.

The First Approach: Sync Everything and Hide Columns

With this method you specify the sheets you want to sync on line 6 below. The script does the rest.

Make sure to also setup the trigger to run this function onChange. For a demo of how to add a Google Script and specify a trigger, see my other blog post on getting cryptocurrency prices inside a google sheet.

Two Caveats

  1. If you delete a row or column, you will have to manually delete it across all sheets
  2. Filtering/Sorting affects all sheets
function syncData() {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
        sourceSheet = spreadsheet.getActiveSheet()
    activeSheetName = sourceSheet.getSheetName(),
        sourceData  = sourceSheet.getDataRange(),
        sheets      = ['Master', 'Sheet1', 'Sheet2', 'Sheet3'];

    // loop through all sheets   
    sheets.forEach(function(sheet) {

        // if sheet we're modifying is not active sheet
        if (activeSheetName !== sheet) {
            // select destination sheet and set destination values
            spreadsheet.getSheetByName(sheet).getRange(1, 1, sourceData.getNumRows(), sourceData.getNumColumns()).setValues(sourceData.getValues());
        }
    });
}

The Second Approach: Sync Specific Columns and Concatenate on Master Sheet

With this approach you will have worksheets with only the data relevant to that specific worksheet. Those sheets will then be synced to specific columns on the Master sheet.

Below is the script I’ve used in one of my work projects. I won’t break the code down until I’ve optimized it (if that will ever happen), but if you have any questions, please feel free to reach out on the contact page.

function syncData() {
  var sheet             = SpreadsheetApp.getActiveSpreadsheet(),
      sourceSheet       = sheet.getActiveSheet()
      activeSheetName   = sourceSheet.getSheetName(),
      mainSheetName     = 'Monthly',
      pmSheetName       = 'PM',
      devSheetName      = 'Dev',
      updateSheetName   = 'Updates',
      mainSheetPMRange  = 'A:B',
      mainSheetDevRange = 'C:E',
      pmSheetRange      = 'A:B',
      devSheetRange     = 'A:C',
      numRows           = {
          'mainSheet'   : sheet.getSheetByName(mainSheetName).getMaxRows(),
          'pmSheet'     : sheet.getSheetByName(pmSheetName).getMaxRows(),
          'devSheet'    : sheet.getSheetByName(devSheetName).getMaxRows(),
        };

  // sync rows
  (function syncRows() {
    if (numRows['mainSheet'] === numRows['pmSheet'] && numRows['mainSheet'] === numRows['devSheet'] && numRows['mainSheet'] === numRows['updateSheet']) {
      return;
    } else {
      var addPMRows     = numRows['mainSheet'] - numRows['pmSheet'];
      var addDevRows    = numRows['mainSheet'] - numRows['devSheet'];
      var pmSheet       = sheet.getSheetByName(pmSheetName);
      var devSheet      = sheet.getSheetByName(devSheetName);
      var pmLastRow     = pmSheet.getLastRow();
      var devLastRow    = devSheet.getLastRow();

      pmSheet.insertRowsAfter(pmLastRow, addPMRows);
      devSheet.insertRowsAfter(devLastRow, addDevRows);
    }
  })();

  if( activeSheetName == mainSheetName ) {
    // define destination sheets
    var destSheet1  = sheet.getSheetByName(pmSheetName);
    var destSheet2  = sheet.getSheetByName(devSheetName);

    // define source data
    var sourceData1 = sourceSheet.getRange(mainSheetPMRange);
    var sourceData2 = sourceSheet.getRange(mainSheetDevRange);

    // define destination range
    var destRange1 = destSheet1.getRange(pmSheetRange);
    var destRange2 = destSheet2.getRange(devSheetRange);

    // set destination values
    destRange1.setValues(sourceData1.getValues());
    destRange2.setValues(sourceData2.getValues());

  } else if ( activeSheetName == pmSheetName ) {
    // define destination sheet
    var destSheet = sheet.getSheetByName(mainSheetName);

    // define source data
    var sourceData = sourceSheet.getRange(pmSheetRange);

    // define destination range
    var destRange = destSheet.getRange(mainSheetPMRange);

    // set destination values
    destRange.setValues(sourceData.getValues());

  } else if ( activeSheetName == devSheetName ) {
    // define destination sheet
    var destSheet = sheet.getSheetByName(mainSheetName);

    // define source data
    var sourceData = sourceSheet.getRange(devSheetRange);

    // define destination range
    var destRange = destSheet.getRange(mainSheetDevRange);

    // set destination values
    destRange.setValues(sourceData.getValues());
  }
}

 

Mike Doubintchik

Author Mike Doubintchik

More posts by Mike Doubintchik

Leave a Reply