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

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

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.

CodePudding user response：

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:

• Simple Triggers
• Installable Triggers