Google Sheets – How to Sync Two Cells Value

google sheetsgoogle-apps-script

Is there a way to way to link two cells so when one is altered the other is altered also?

Like this:

I alter `A1` to 1, `B1` is now 1
I alter `B1` to -1, `A1` is now -1

Best Answer

You can link two cells using a google script. You can use on Edit trigger of each sheet to poll for which cell is getting modified and modify the other one in the other sheet.

The below code will link cell A1 in "Sheet1" in of Spreadsheet with ID spd1ID to B1 in "Sheet1" in Spreadsheet with ID spd2ID 

var spd1ID = "ID of first sheet here"
var spd2ID = "ID of second sheet here"

function myLinkCells() {
 ScriptApp.newTrigger("callOnEdit").forSpreadsheet(spd1ID).onEdit().create()
 ScriptApp.newTrigger("callOnEdit").forSpreadsheet(spd2ID).onEdit().create()
}


function callOnEdit(e){
  var range = e.range
  var cellAdd = range.getA1Notation()
  Logger.log("Called")
  var ssID = e.source.getId()
  var sName = range.getSheet().getSheetName()
  if( ssID == spd1ID && sName == "Sheet1"){
  if(cellAdd == "A1"){
     var ss = SpreadsheetApp.openById(spd2ID)
     var rng = ss.getSheetByName("Sheet1").getRange("B1")
     rng.getSheet().getSheetName()
     rng.setValue(e.value)
     Logger.log("In Cell A1")
     }
  } else if(ssID == spd2ID && sName == "Sheet1"){
    if(cellAdd == "B1"){
     var ss = SpreadsheetApp.openById(spd1ID)
     var rng = ss.getSheetByName("Sheet1").getRange("A1")
     rng.setValue(e.value)
     Logger.log("In Cell B1")

     }
  }
}

Where to paste this code? Open your spreadsheet Tools>Script Editor

What is spreadsheet ID? https://developers.google.com/sheets/api/guides/concepts#spreadsheet_id

After you paste the code and modify the ID, run myLinkCells() function using Run menu. That will link the cells.

Hope that helps!

Related Topic