I have a google spreadsheet "shared" where it consists of all the bill of materials. I want to keep another spreadsheet "master" such that only owner can access that. Any data inserted in the shared spreadsheet should get reflected in master spreadsheet, but if we edit shared spreadsheet it should not get reflected in master spreadsheet.
Any help would be appreciated.
Google Spreadsheets does have a scripting capability. It also has a public gallery script.
image http://img593.imageshack.us/img593/5410/screenshot20110720at736.png
One of the public scripts is edit to another spreadsheet
edit to another spreadsheet update in another spreadsheet the changes in the current one ticcaje (at) gmail.com
image http://img97.imageshack.us/img97/240/picture1nns.png
unfortunately it has not been updated in some time, and after looking at the code I don't think it was ever actually completed, as there is a dialog message and then a return statement.
image http://img718.imageshack.us/img718/5264/pictureja.png
I think this could be a really useful script and so I've done a little editing with it, but actually there are no comments in it, and I don't have the time to get it working 100% right now, but I wanted to post it here in hopes that somebody could actually pick it up and run with it.
function onEdit(){
var sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var targetSpreadsheet = SpreadsheetApp.openById('0AntUWac3dtkUac3dtnhTjMwac3dtVjBiac3dtOXcac3dt'); //put in your spreadsheet key here
var sourcesSheet = sourceSpreadsheet.getSheets()[0];
var targetSheet = targetSpreadsheet.getSheets()[0];
var currentSourceCellIndex = SpreadsheetApp.getActiveRange().getRow(); //ActiveCell().getValues();
var selectedCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection().getA1Notation();
var targetRowsCount = targetSheet.getLastRow();
var targetColumns = sourcesSheet.getLastColumn();
var targetRange = targetSheet.getRange(1, 1, targetRowsCount, targetColumns);
var targetSources = targetRange.getValues();
var sourceRows = sourcesSheet.getLastRow();
var sourceColumns = sourcesSheet.getLastColumn();
var sourcesRange = sourcesSheet.getRange(1, 1, sourceRows, sourceColumns);
var sources = sourcesRange.getValues();
var compareName = sources[currentSourceCellIndex-1][0];
return;
//Browser.msgBox("currentSourceCell: "+sources[currentSourceCellIndex-1][0]);return; // ActiveCell()
//Browser.msgBox("currentSourceCell: "+targetRowsCount);
// return;
for (var i = 1; i < targetRowsCount; ++i) {
if (targetSources[i-1][0] == compareName){
targetSheet.deleteRow(i);
break;
}
}
// var sourceRows = sourcesSheet.getLastRow();
// var sourceColumns = sourcesSheet.getLastColumn();
// var sourcesRange = sourcesSheet.getRange(1, 1, sourceRows, sourceColumns);
// var sources = sourcesRange.getValues();
//if ((sources[sourceRows-1][0] == "")||(sources[sourceRows-1][1] == "") ||(sources[sourceRows-1][2] == "") || (sources[sourceRows-1][3] == "") || (sources[sourceRows-1][4] == "") || (sources[sourceRows-1][5] == ""))
//return;
//currentSourceCell = sourceSheet.getActiveCell();
rowToInsert = targetSheet.getLastRow();
targetSheet.insertRowAfter(rowToInsert);
var insertRange = targetSheet.getRange(rowToInsert + 1, 1, 1, targetSheet.getLastColumn());
var kolonnen = [];
var tbText = [];
for (var i = 1; i < 16; ++i) {
kolonnen.push(i);
tbText.push(sources[sourceRows-1][i-1]);
}
//Browser.msgBox("source is: "+tbText);return;
for (j = 0; j < kolonnen.length; j++) {
var zellRange = targetSheet.getRange(rowToInsert+1, kolonnen[j], 1, 1);
zellRange.setValue(tbText[j]);
}
}
The original code can be accessed by editing the script, but if you want it I'll just go ahead and post it here, too:
function onEdito() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {
name: "go",
functionName: "insertRow"
}
// ,
// {
// name: "Build Journal",
// functionName: "collectJournal"
// }
];
ss.addMenu("pastePlus", menuEntries);
}
function onEdit(){
var sourceSpreadsheetName = Browser.inputBox("source spreadsheet");
//var sourceSpreadsheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
var files = DocsList.getFilesByType("spreadsheet");
//var files = SpreadsheetApp;
Browser.msgBox("currentSourceCell: " +files);
//var sourceSpreadsheet;
for (var i = 0; i < files.length; ++i) {
var filename = files[i].getName();
if (filename == sourceSpreadsheetName) {
var sourceSpreadsheet = SpreadsheetApp.openById(files[i].getId());
var sheets = sourceSpreadsheet.getSheets();
var sourcesSheet = sheets[1];
break;
}
}
var targetSpreadsheetName = "Probando Script"; //DocsList.getFilesByType("spreadsheet");
//var targetSpreadsheet;
for (var i = 0; i < files.length; ++i) {
var filename = files[i].getName();
if (filename == targetSpreadsheetName) {
var targetSpreadsheet = SpreadsheetApp.openById(files[i].getId());
var sheets = targetSpreadsheet.getSheets();
var targetSheet = sheets[1];
break;
}
}
var currentSourceCellIndex = SpreadsheetApp.getActiveRange().getRow(); //ActiveCell().getValues();
var selectedCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection().getA1Notation();
var targetRowsCount = targetSheet.getLastRow();
var targetColumns = sourcesSheet.getLastColumn();
var targetRange = targetSheet.getRange(1, 1, targetRowsCount, targetColumns);
var targetSources = targetRange.getValues();
var sourceRows = sourcesSheet.getLastRow();
var sourceColumns = sourcesSheet.getLastColumn();
var sourcesRange = sourcesSheet.getRange(1, 1, sourceRows, sourceColumns);
var sources = sourcesRange.getValues();
var compareName = sources[currentSourceCellIndex-1][0];
//Browser.msgBox("currentSourceCell: "+sources[currentSourceCellIndex-1][0]);return;
Browser.msgBox("currentSourceCell: "+targetRowsCount);return;
for (var i = 1; i < targetRowsCount; ++i) {
if (targetSources[i-1][0] == compareName){
targetSheet.deleteRow(i);
break;
}
}
var sourceRows = sourcesSheet.getLastRow();
var sourceColumns = sourcesSheet.getLastColumn();
var sourcesRange = sourcesSheet.getRange(1, 1, sourceRows, sourceColumns);
var sources = sourcesRange.getValues();
//if ((sources[sourceRows-1][0] == "")||(sources[sourceRows-1][1] == "") ||(sources[sourceRows-1][2] == "") || (sources[sourceRows-1][3] == "") || (sources[sourceRows-1][4] == "") || (sources[sourceRows-1][5] == ""))
//return;
//currentSourceCell = sourceSheet.getActiveCell();
rowToInsert = targetSheet.getLastRow();
targetSheet.insertRowAfter(rowToInsert);
var insertRange = targetSheet.getRange(rowToInsert + 1, 1, 1, targetSheet.getLastColumn());
var kolonnen = [];
var tbText = [];
for (var i = 1; i < 16; ++i) {
kolonnen.push(i);
tbText.push(sources[sourceRows-1][i-1]);
}
//Browser.msgBox("source is: "+tbText);return;
for (j = 0; j < kolonnen.length; j++) {
var zellRange = targetSheet.getRange(rowToInsert+1, kolonnen[j], 1, 1);
zellRange.setValue(tbText[j]);
}
}
