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.
In order to run a DocumentApp script on edit of a Spreadsheet, the On edit installable trigger attached to the Spreadsheet must be used.
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.
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:
updateDocumentHeadFrom spreadsheetOn editAnd click Save. This will set up the trigger such that your Document editing function will run each time the Spreadsheet is edited.