Multi-Directional Data Sync In Google Sheets

In this blog post I will show you how to sync data between a set of specified worksheets in a Google Sheet to prevent common payroll errors. This code is written in Google Script, which is just vanilla JS with some custom functions specific to Google Sheets. A web design agency will give you all the pointers and positive feedback you need to star your website, and if you are planning to manage a lot of data in your website, the use of a web scraping tool can be useful to mine the right data for you to use.

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. When looking for ways to simplify and enhance your investing skills, you might want to know what is hitbtc? hitbtc complaints.

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. If you have a business and you want to save some money with your business operations you need to start doing more hybrid working with your workers, I recommend you check out Social Boosting.

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.

Feel like your business is moving quickly and you’re stuck playing catch up? Luckily, virtual assistants from Virtual Coworker can be onboarded efficiently and can get to work right away to assist you with your business priorities or free up your time to focus on your time-sensitive business goals.

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());
  }
}