How can I create an edit trigger for a different Google Sheets document?

Other Questions Google docs
1

I’m trying to set up a trigger that runs when someone edits a cell in one Google Sheets file, but I want to create this trigger from a different spreadsheet. Here’s what I’m working with:

This function should run when cells get edited in the first sheet:

function updateTarget() {
    var cellData = SpreadsheetApp.getActiveSheet().getActiveRange().getValue();
    SpreadsheetApp.openById("target-sheet-id").getSheetByName("Data").getRange("C3").setValue(cellData);
}

I tried creating the trigger from my second spreadsheet like this:

function setupTrigger() {
    var sourceSheet = SpreadsheetApp.openById("source-sheet-id");
    ScriptApp.newTrigger("updateTarget").forSpreadsheet(sourceSheet).onEdit().create();
}

The problem is that forSpreadsheet() seems to only work when you’re working within the same spreadsheet file. Is there a way around this limitation?

2

You hit a major limitation of Google Apps Script. The ScriptApp.newTrigger() function cannot create triggers for multiple spreadsheets; the trigger must reside within the same spreadsheet as the function it calls. A recommended approach is to transfer your updateTarget() function into the script of the source spreadsheet and then set up the onEdit trigger from there. This method simplifies the process of synchronizing data between spreadsheets.

3

yeah, that’s a known issue. you need to put the script in the source sheet, not the target one. move your updateTarget function to the source spreadsheet’s script editor and set up the trigger there. cross-sheet triggers don’t work in apps script.