Sync multiple google spreadsheets(5+) back to a master google spreadsheet

Question

I'm having a hard time trying to find a way to sync multiple sheets into a master spreadsheet. For my test, I have a large spreadsheet with addresses in 5 cities. I am trying to create a spreadsheet for each city and be able to sync the data whenever it is changed in either sheet. So I would manage the overall data, and each of the 5 spreadsheets will be assigned to other people. That way, they can update the sheet w/o having access to all the data. The issue with using the builtin query or importrange functions are that if the user makes changes on the 'city' spreadsheets, it'll break the sheet because the data is referenced.

I found the code by 'Dev' below after doing a search.

var sourceSpreadsheetID = "ID HERE";
var sourceWorksheetName = "SHEET NAME HERE";
var destinationSpreadsheetID = "ID HERE";
var destinationWorksheetName = "SHEET NAME HERE";

function importData() {
  var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
  var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
  var thisData = thisWorksheet.getDataRange();
  var toSpreadsheet = SpreadsheetApp.openById(destinationSpreadsheetID);
  var toWorksheet = toSpreadsheet.getSheetByName(destinationWorksheetName);
  var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
  toRange.setValues(thisData.getValues()); 
}

This works GREAT because it copies the data over allowing me to make edits, but the issue is I can't import just the data i want. If i could use a sql query, id do e.g. select * where city = 'miami' but I believe this is not possible with google spreadsheets at the moment.

So the next idea I had was to use this script and set filters on the child spreadsheets. The issue is when it runs the script, it will copy the entire sheet back and if any of the 5 users are updating their own spreadsheets at same time, it would cause an issue as they would be overwriting each others changes. The other thought I had was that since each record has its own unique id#(i have a column called id) is there a way to tell the active spreadsheet to update the row in the mastersheet where id = masterspreadsheet.id on edit?

Any help in the right direction would be greatly appreciated.

You can actually use QUERY in google spreadsheets, but it will be dynamic, as in the data will always be updating: support.google.com/docs/answer/3093343?hl=en. does this solve your issue? — Kriggs
I am pulling data from a separate spreadsheet, so I cannot use the Query function by itself. I have to do an importrange which references the data. So unfortunately, this does not work for me. — User125

Alan Wells Alan Wells · Accepted Answer · 2015-01-06T22:01:13

You can import all the data, then use the JavaScript sort() method on a array.

var thisData = thisWorksheet.getDataRange();

var arryOfData = thisData.getValues();
Loggger.log('arryOfData: ' + arryOfData[0]);

var dataSorted = thisData.sort();
Logger.log('dataSorted: ' + dataSorted[0]);

Run the code, then VIEW the LOG to see what was returned into the variables as a way to debug the code.

Then you could use indexOf() and slice() to remove parts of the data that you don't want.

Sync multiple google spreadsheets(5+) back to a master google spreadsheet

2 Answers