Two Way Sync of Specific Ranges Across Two Separate Files In Google Sheets via Apps Script

Question

Apologies if this is a somewhat long post - I can further clarify anything here if needed. I basically have an Apps script question in regards to two way sync across two separate Google Sheet files on two separate accounts.

Both files are the same, but named differently. I would like to know if there is a way where I can establish a two way sync between the two files, but only for a specific range, which would be the same across both files.

So say for example we have an Account A & Account B, with files 1 and 2 respective to each account. Both files are identical. I'm looking to have it so that if I make a change anywhere between ranges E14:AE20 in either of the files, those changes are reflected in the opposite file.

If this is confusing at all or requires further elaboration please let me know, I can also provide photos of what I mean if it helps at all. I tried to make this post as straightforward as possible but I can elaborate further if needed.

Thanks, G

Answer 1

I think you can do this with the onEdit(e) triggered script. This will run with every edit and if the if statement in the script is true it will set the value in the target file->sheet->range.

Note: This will copy only values, not formula's, formatting etc...

1. Extensions -> Apps script
2. Clear the boilercode
3. Paste the code from below
4. Change the sheetnames TheSheetNameToCheck
5. Change the spreadsheet id from the taget file.
6. Go to triggers (clock icon), Add trigger and set Select event type to On edit
7. Same for the other file.

The script:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const value = e.value;

  if (
    sheet.getName() == 'TheSheetNameToCheck'
    && range.getColumn() >= 5
    && range.getColumn() <= 31
    && range.getRow() >= 14
    && range.getRow() <= 20
  ) {
    const targetFile = SpreadsheetApp.openById('1hVcBM7Tzkg40YxxxxxxxxxF3prATgF8')
    const targetSheet = targetFile.getSheetByName('TheSheetNameToCheck')
    const a1 = range.getA1Notation()
    targetSheet.getRange(a1).setValue(value)
  }

}