Google Spreadsheet Script to Synchronize Cells on few Sheets

Question:

I have multiple sheets which each sheet has one cell in comum with one another say cell "A3". I like to be able to do is if  I'm on "sheet2" or any other sheet" and change it's A3 value, I also want the cell A3 on sheet1, 3 4, and 5 to display that value. 

Again I like to change the values from any of the sheets not just from one location or else all I would need is a VLOOKUP formula.

Is this something that can be done?

Solution:

I have written the script code which will synchronize the predefined cells on predefined sheets. Here is the code:


function onEdit(e)
{
  kishan();
}

function kishan()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var activeSheetName = ss.getActiveSheet().getSheetName();
  var activeCellName = activeSheet.getActiveCell().getA1Notation();
  var activeCellValue = activeSheet.getActiveCell().getValue();
  var flag = false;
  var sheetnames = ["Sheet1","Sheet3","Sheet4"]
  var cells = ["A1","B1","D1"]
  
  for(var i=0;i<sheetnames.length;i++)
  {
    for(var j=0;j<cells.length;j++)
    {
      if(activeSheetName==sheetnames[i] && activeCellName==cells[j])
      {
        flag=true;
        break;
      }
    }
    if(flag)
      break;
  }
  
  if(flag)
    for(var i=0;i<sheetnames.length;i++)
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetnames[i]).getRange(activeCellName).setValue(activeCellValue);
  

}

Put the above code in the script editor of your spreadsheet.


Now you can see in the code the following line:
var sheetnames = ["Sheet1","Sheet3","Sheet4"]
change it according to your requirement.

And also change the following line as per your requirement:
var cells = ["A1","B1","D1"]


You can increase or decrease the number of sheets and cells as per your requirement.


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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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 

Thanks,

Kishan.

iGoogleDrive