manniru
8/23/2017 - 10:02 AM

Sync data across Google Spreadsheets

Sync data across Google Spreadsheets

// create menu buttons
function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
        name: "Sync Spreadsheet Data",
        functionName: "copyDataToWorkingSpreadsheet"
    }];
    ss.addMenu("Data Update Functions", menuEntries);
};

// copy data from form sheet to live mapping sheet
function copyDataToWorkingSpreadsheet() {

  // source doc
  var sss = SpreadsheetApp.openById('0An8....');

  // source sheet
  var ss = sss.getSheetByName('Master spreadsheet');
  
  // Get full range of data
  var SRange = ss.getDataRange();

  // get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();

  // get the data values in range
  var SData = SRange.getValues();

  // target spreadsheet
  var tss = SpreadsheetApp.openById('0An8.....');
  
  // target sheet
  var ts = tss.getSheetByName('Working Spreadsheet'); 

  // set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);

};

Snippets: Sync data across Google Spreadsheets

Added to a Google spreadsheet, this script will take data from a master spreadsheet and sync it to a working spreadsheet.

Variables to change are:

// source doc
var sss = SpreadsheetApp.openById('0An8....');

// source sheet
var ss = sss.getSheetByName('Master spreadsheet');

// target spreadsheet
var tss = SpreadsheetApp.openById('0An8.....');

// target sheet
var ts = tss.getSheetByName('Working Spreadsheet');