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