Bidirectional content syncing between google spreadsheets

Question

I am creating a google spreadsheet dashboard to summarize content from many different projects. All project content is contained in other google spreadsheets. For each project there is a task list/to-do list. I am trying to find a solution that will allow me to do the following:

1) Pull task lists from each project spreadsheet into one master task sheet on the dashboard

2) Add/edit/delete tasks on the project task sheets, which will then automatically update the master task sheet.

3) Add/edit/delete tasks on the master task sheet, which will then automatically update the project task sheets.

Numbers 1 and 2 are fairly simple using the importrange() and query() functions but to my knowledge these are only unidirectional solutions. Any help with number 3?

Sample Documents:

Project 1

Project 2

Dashboard

user1856510 user1856510 · Accepted Answer · 2013-07-12T03:42:34

You can use this script and perform the task. But This works in the same spreadsheet. I have not been able to link this with two different spreadsheets.

function onEdit()

{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var activeSheetName = ss.getActiveSheet().getSheetName();

  if( activeSheetName == "Sheet1" || activeSheetName == "Sheet2" )
  {
    var activeCell = activeSheet.getActiveCell();
    var activeCellinA1 = activeCell.getA1Notation();

    if( activeCellinA1 == "A1" )
    {
      var activeCellValue = activeCell.getValue();
      if( activeSheetName == "Sheet1" )
        ss.getSheetByName("Sheet2").getRange("A1").setValue(activeCellValue);
      if( activeSheetName == "Sheet2" )
        ss.getSheetByName("Sheet1").getRange("A1").setValue(activeCellValue);
    }      
  }
}

Bidirectional content syncing between google spreadsheets

1 Answers