Sync data across Google Spreadsheets

README.md

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'); 

spreadsheet-sync.js

// 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);

};

Other working example for copying a specific range from one spreadsheet to the currently active spreadsheet.

// 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() {

//Copy link of Sourcesheet to variable below her. At least the "edit must be in the name.
var sUrlSourceSpreadsheet = "https://docs.google.com/spreadsheets/d/1Hg6LPkfvyzilOO7JwHSSughJ_-K8sqdBJq5/edit#gid=0";
var sSourceWorksheet = 'Master spreadsheet'; //Fill in the name of the source worksheet
var sDestinationWorksheet = 'Working Spreadsheet'; //Fill in the name of the destination worksheet

// source doc
var oSourceSpreadsheet = SpreadsheetApp.openByUrl(sUrlSourceSpreadsheet);
// source sheet
var oSourceSheet = oSourceSpreadsheet.getSheetByName(sSourceWorksheet);

// Get full range of data
//var oSourceRange = oSourceSheet.getDataRange(); //For selecting a complete sheet
var oSourceRange = oSourceSheet.getRange("B1:D1"); //For selecting a specific range

// get A1 notation identifying the range.
//var oSourceRange_VanafCellA1 = oSourceRange.getA1Notation(); //Only usefull for copying a complete sheet

// get the data values in range
var oSource_Data = oSourceRange.getValues();

// target spreadsheet
var oTargetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

// target sheet
var oTargetWorksheet = oTargetSpreadsheet.getSheetByName(sDestinationWorksheet);

// fill the target range with the values of the source data
// oTargetWorksheet.getRange(oSourceRange_VanafCellA1).setValues(oSource_Data); // only works when copying a complete sheet
// if copying just a selected range, then use this 2-step version
var oTargetRange = oTargetWorksheet.getRange("B10:D10"); // Step 1: Select destination range
oTargetRange.setValues(oSource_Data); //Step 2: Perform the copy operation
}