Trigger Google DocumentApp Script Upon Edit of SpreadsheetApp

Question

Using Google Apps Script, is there a way to have a Google Documents file update automatically whenever a Google Sheets file is edited?

I've got a Google DocumentApp file with a script that gets data from a Google SpreadsheetApp file. I'm looking to create a script to automatically update the DocumentApp file whenever the SpreadsheetApp file is edited.

This is the code I'm using currently:

function updateDocumentOnEditTrigger() {
  var ss = SpreadsheetApp.openById(SheetID);
  ScriptApp.newTrigger('UpdateDocument')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

Running the updateDocumentOnEditTrigger function doesn't seem to trigger the UpdateDocument function, which works as it should when manually run.

I would like to understand about your goal. But I cannot understand about I want to trigger that DocumentApp script to run whenever the SpreadsheetApp is edited in any way.. Can I ask you about it? By the way, I think that openById cannot be used with the simple trigger using onEdit(). If you want to use openById by the OnEdit event trigger, please use the installable OnEdit event trigger. How about this? — Tanaike
@Tanaike I want to create a trigger script for the DocumentApp file so that anytime the SpreadsheetApp file is edited, the DocumentApp file is updated. I've changed the script as follow: function updateDocumentOnEditTrigger() { var ss = SpreadsheetApp.openById(SheetID); ScriptApp.newTrigger('UpdateDocument') .forSpreadsheet(ss) .onEdit() .create(); } I wrapped the other function inside this function and placed it outside and neither seems to update the DocumentApp file. — Catalyx
It sounds like you want to create an onEdit trigger on the SpreadsheetApp that will then update the DocumentApp. So that anytime the SpreadsheetApp is edited, code will run that will then edit/update the DocumentApp. Is that right? — IMTheNachoMan
@IMTheNachoMan That's correct. I'm a novice with Google Scripts. I've inserted the above installed trigger as advised in my 'UpdateDocument' DocumentApp script. Is this correct or would I need a separate script for the SpreadsheetApp file? — Catalyx

Rafa Guillermo Rafa Guillermo · Accepted Answer · 2020-01-21T09:16:10

Answer:

In order to run a DocumentApp script on edit of a Spreadsheet, the On edit installable trigger attached to the Spreadsheet must be used.

More Information:

As per the Simple Triggers documentation, there are some restrictions which need to be taken into account. In particular:

They can modify the file they are bound to, but cannot access other files because that would require authorization.

As a result, the onEdit(e) trigger function can not be used. There is however an installable trigger which can be created, with settings set up such that it can fire on edit.

Code:

With the following function in the script bound to the Spreadsheet file:

function updateDocument() {
  var doc = DocumentApp.openById('DOCUMENT_ID');
  // here you can put your code that edits the document in the way you want.
}

You can create an installable trigger which runs on the edit of the Spreadsheet. You will need to run the code at least once before setting up the trigger however - this is because DocumentApp needs authorisation and you need to grant it!

Setting up an Installable Trigger:

With the code set up, you can create the installable trigger by completing the following steps:

From the Apps Script editor view for the bound Spreadsheet script, follow the path Edit > Current project's triggers. This will open the triggers for the project in a new tab or window.

In the bottom left, click on the + Add Trigger button, bringing up the Add Trigger modal. From here, set up the trigger with the following properties:

Choose which function to run: updateDocument
Choose which deployment should run: Head
Select event source: From spreadsheet
Select event type: On edit

And click Save. This will set up the trigger such that your Document editing function will run each time the Spreadsheet is edited.

I hope this is helpful to you!

Trigger Google DocumentApp Script Upon Edit of SpreadsheetApp

1 Answers

Answer:

More Information:

Code:

Setting up an Installable Trigger:

References: