Friday, June 21, 2013

Google Spreadsheet Synchronize Two Sheets

Google Spreadsheet Synchronize Two Sheets


Question:

(by Omar)


I have 2 worksheets. Sheet1 and Sheet2


I want to have cell A1 on Sheet1 to be shown on Sheet2 - I can do this

BUT... what I want is to be able to change the value in both places - a change made on Sheet 2, gets shown on Sheet 1
And a change made on Sheet 1 shoes on Sheet 2

How can I do?

Thanks


Solution:

Now, have a look at the following script:




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


When you edit Cell A1 in either Sheet1 or Sheet2 then the above script will run the onEdit function, and Cell A1 on both the sheets that is Sheet1 and Sheet2 will automatically synchronize.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:


If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 


2 comments:

  1. Hi Kishan,
    This tools is really great however a problem that I have been struggling for a longtime is:

    I have one sheet where I have put "importrange" function for my subordinate so that he can look into what I have updated in the sheet. But he can not update it after doing the task.

    Is there any way, this script can work with another googlespreadsheet as it works with "Sheet1" and "Sheet2" in the same spreadsheet.

    please suggest.

    ReplyDelete
  2. Hi Kishan,
    Did you get the chance to loook into the solution of this problem?

    ReplyDelete