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

Question

I need to do a 2 way sync between 2 spreadsheets and I found this solution.

Here is the script from the link above. And yes, I did modify "TheSheetNameToCheck" and targetFile/targetSheet.

//Author: RemcoE33
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)
  }

}

The issue is that when I run the script, I get an error message "TypeError: Cannot read property 'source' of undefined" and I'm not sure how to proceed.

It looks like you copied this from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, [edit] to make sure you do **ALL** the following: 1.Include a [link](/editing-help#links) to the source, 2. Mention the author's name, and 3. [Quote](/editing-help#simple-blockquotes) the copied content. For more details, see [referencing help](/help/referencing) and [this FAQ](https://meta.stackexchange.com/q/160077/343832). — TheMaster, Oct 25 '22 at 05:56

score 1 · Accepted Answer · answered Oct 25 '22 at 01:33

Modification points:

Although, unfortunately, I cannot understand I found this [solution][1], in your script, function onEdit(e) {,,,} is used. In this case, the script is run by the simple trigger of OnEdit. When the script is run by the OnEdit trigger, the event object e is given. But, from your showing error message of TypeError: Cannot read property 'source' of undefined, I'm worried that you might run the script with the script editor. In this case, the value of e is not given. By this, such an error occurs. Ref
- When you run this script with the event object, please edit a cell. By this, the script is automatically run.
But, when I saw your script, SpreadsheetApp.openById('###') is used. In this case, SpreadsheetApp.openById('###') cannot be used with the simple trigger. So, in order to run your script, it is required to use the installable trigger of OnEdit.

When these points are reflected in your script, how about the following modification?

Modified script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please install the OnEdit trigger to the function of installedOnEdit. When you run this script, please edit the cells of sheet.getName() == 'TheSheetNameToCheck' && range.getColumn() >= 5 && range.getColumn() <= 31 && range.getRow() >= 14 && range.getRow() <= 20. By this, the script is run.

function installedOnEdit(e) { // Renamed function name.
  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);
  }
}

When a cell is edited, this script is run.
In this modification, in order to avoid the duplicate execution of the simple and the installable OnEdit trigger, the function name was changed.

Note:

From your error message, if you want to directly run the script with the script editor, how about the following modification? In this case, after you selected a cell of sheet.getName() == 'TheSheetNameToCheck' && range.getColumn() >= 5 && range.getColumn() <= 31 && range.getRow() >= 14 && range.getRow() <= 20, when you run this script with the script editor, the script is run using the current selected cell.

  function myFunction() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const range = sheet.getActiveCell();
    const value = range.getValue();
    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);
    }
  }

References:

@Kay Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. — Tanaike, Oct 25 '22 at 04:33

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

1 Answers1

Modification points:

Modified script:

Note:

References: