Adding The data into another spreadsheet-CodePudding

Continuing from previous question

This code recodes the last edited data from Spreadsheet A Sheet1 into the same Spreadsheet A Sheet2, but I want that data (Spreadsheet A Sheet1) to be added into another Spreadsheet B Sheet2

I couldn't configure how to modify the code since it looks a little complex to me.

Can you please help me to modify this code?

function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet1" || range.rowStart == 1) return;
  const col = range.columnStart - 1;
  const [header, ...values] = sheet.getDataRange().getDisplayValues();
  const value = values[range.rowStart - 2];
  const v = [[`${value[0]} - ${value[1]}`, `${header[col]} - ${value[col]}`]];
  e.source.getSheetByName("Sheet2").getRange("A2:B2").setValues(v);
  
}

CodePudding user response：

In your situation, how about the following modification?

From:

e.source.getSheetByName("Sheet2").getRange("A2:B2").setValues(v);

To:

SpreadsheetApp.openById("###SpreadsheetID of SpreadsheetB###").getSheetByName("Sheet2").getRange("A2:B2").setValues(v);

And also, please rename the function name from onEdit to others like installedOnEdit. And, please install OnEdit trigger to the renamed function as the installable trigger. Because openById cannot be used with the simple trigger. I thought that this might be the reason of your issue of it keeps giving error message.

When you use this script, please edit the cell. By this, the script is run.

Note:

When you install the OnEdit trigger to onEdit function when the cell is edited, both the simple trigger and the installable trigger are run with the asynchronous process. Ref So I proposed to rename the function name. Please be careful about this.

Reference:

Installable Triggers