Skip to content

Instantly share code, notes, and snippets.

@yuhui

yuhui/Email when a Google Doc's linked files are updated

Created September 21, 2023 02:18
Show Gist options
  • Save yuhui/f92e4a09d984a658a2796e9d6a4d8981 to your computer and use it in GitHub Desktop.
Save yuhui/f92e4a09d984a658a2796e9d6a4d8981 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Email when a Google Doc's linked files are updated
Send an email when a Google Doc's linked files are updated.
Raw
Helpers.gs
function scriptUrl_() {
const scriptId = ScriptApp.getScriptId();
const scriptUrl = `https://script.google.com/home/projects/${scriptId}/edit`;
return scriptUrl;
}
function mail_(subject, body) {
const myEmail = Session.getActiveUser().getEmail();
GmailApp.sendEmail(myEmail, subject, body);
}
function logProperties() {
const properties = getProperties_();
Logger.log(properties);
}
function getProperties_() {
const documentProperties = PropertiesService.getDocumentProperties();
const properties = documentProperties.getProperties();
return properties;
}
function deleteProperty_(key) {
const documentProperties = PropertiesService.getDocumentProperties();
documentProperties.deleteProperty(key);
}
function getProperty_(key) {
const documentProperties = PropertiesService.getDocumentProperties();
const valueString = documentProperties.getProperty(key);
const value = JSON.parse(valueString);
return value;
}
function setProperty_(key, value) {
const valueString = typeof value !== 'string' ? JSON.stringify(value) : value;
const documentProperties = PropertiesService.getDocumentProperties();
documentProperties.setProperty(key, valueString);
}
Raw
NotifyUpdatedResources.gs
function notifyUpdatedResources() {
// DEBUGGING!
//PropertiesService.getDocumentProperties().deleteAllProperties();
//
const doc = DocumentApp.getActiveDocument();
const docId = doc.getId();
const docName = doc.getName();
const docUrl = doc.getUrl();
const body = doc.getBody();
const bodyResources = getResources_(body, docId);
const otherResources = getOtherResources_(docId);
const resources = [otherResources, bodyResources].flat(Infinity);
const resourcesStatusesAndNamesUrls = {
added: {},
deleted: {},
missing: {},
trashed: {},
updated: {},
};
const resourceProperties = getProperties_();
// this array will be used later for identifying resources that have been deleted from the document
const documentResourcePropertyKeys = [];
resources.forEach(({ resource, resourceId, resourceLastModifiedTime, resourceName, resourceUrl }) => {
let resourceStatus;
const propertyKey = getResourcePropertyKey_(resourceId);
documentResourcePropertyKeys.push(propertyKey);
if (!(propertyKey in resourceProperties)) {
setProperty_(propertyKey, resourceLastModifiedTime);
resourceStatus = 'added';
resourcesStatusesAndNamesUrls[resourceStatus][resourceName] = resourcesStatusesAndNamesUrls[resourceStatus][resourceName] || [];
if (!resourcesStatusesAndNamesUrls[resourceStatus][resourceName].includes(resourceUrl)) {
resourcesStatusesAndNamesUrls[resourceStatus][resourceName].push(resourceUrl);
}
} else if (!resource) {
deleteProperty_(propertyKey);
resourceStatus = 'missing';
resourcesStatusesAndNamesUrls[resourceStatus][resourceName] = resourcesStatusesAndNamesUrls[resourceStatus][resourceName] || [];
if (!resourcesStatusesAndNamesUrls[resourceStatus][resourceName].includes(resourceUrl)) {
resourcesStatusesAndNamesUrls[resourceStatus][resourceName].push(resourceUrl);
}
} else if (resource.isTrashed()) {
resourceStatus = 'trashed';
resourcesStatusesAndNamesUrls[resourceStatus][resourceName] = resourcesStatusesAndNamesUrls[resourceStatus][resourceName] || [];
if (!resourcesStatusesAndNamesUrls[resourceStatus][resourceName].includes(resourceUrl)) {
resourcesStatusesAndNamesUrls[resourceStatus][resourceName].push(resourceUrl);
}
} else {
const previousLastUpdatedTime = resourceProperties[propertyKey];
if (previousLastUpdatedTime && resourceLastModifiedTime <= previousLastUpdatedTime) {
// this resource has not been updated recently, ignore it
return;
}
setProperty_(propertyKey, resourceLastModifiedTime);
resourceStatus = 'updated';
resourcesStatusesAndNamesUrls[resourceStatus][resourceName] = resourcesStatusesAndNamesUrls[resourceStatus][resourceName] || [];
if (!resourcesStatusesAndNamesUrls[resourceStatus][resourceName].includes(resourceUrl)) {
resourcesStatusesAndNamesUrls[resourceStatus][resourceName].push(resourceUrl);
}
}
});
// next, compile all of the resources where there are properties, yet are not in the document
// i.e. these resources used to be in the document, but are not found now
// i.e. these resources are "deleted"
Object.keys(resourceProperties).forEach((propertyKey) => {
if (!documentResourcePropertyKeys.includes(propertyKey)) {
deleteProperty_(propertyKey);
const resourceName = 'deleted file';
const resourceUrl = propertyKey.replace('Google Drive file ID: ', '');
const resourceStatus = 'deleted';
resourcesStatusesAndNamesUrls[resourceStatus][resourceName] = resourcesStatusesAndNamesUrls[resourceStatus][resourceName] || [];
if (!resourcesStatusesAndNamesUrls[resourceStatus][resourceName].includes(resourceUrl)) {
resourcesStatusesAndNamesUrls[resourceStatus][resourceName].push(resourceUrl);
}
}
});
mailChangedResources_(resourcesStatusesAndNamesUrls, docName, docUrl);
}
function getOtherResources_(docId) {
const FOLDER_ID = 'abc_1234';
const folder = DriveApp.getFolderById(FOLDER_ID);
const files = folder.getFiles();
let resources = [];
let resource;
let resourceFile;
while (files.hasNext()) {
resourceFile = files.next();
resource = getResource_(resourceFile, docId);
if (resource) {
resources.push(resource);
}
}
return resources;
}
function getResources_(element, docId) {
let resources = [];
const resourceUrl = getResourceUrl_(element, docId);
if (resourceUrl) {
const resource = getResource_(resourceUrl, docId);
if (resource) {
resources.push(resource);
}
}
let parentElement = element;
if (typeof parentElement.getNumChildren === 'function') {
const numChildren = parentElement.getNumChildren();
let child;
for (let i = 0; i < numChildren; i++) {
child = parentElement.getChild(i);
childResources = getResources_(child, docId);
resources = resources.concat(childResources);
}
}
return resources;
}
function getResource_(resourceOrResourceUrl, docId) {
let resource;
let resourceId;
let resourceUrl;
if (typeof resourceOrResourceUrl !== 'string') {
resource = resourceOrResourceUrl;
resourceId = resource.getId();
resourceUrl = resource.getUrl();
} else {
resourceId = getResourceIdFromUrl_(resourceOrResourceUrl);
resourceUrl = resourceOrResourceUrl;
if (resourceId) {
try {
resource = DriveApp.getFileById(resourceId);
} catch (e) {
// resource does not exist any more
}
}
}
if (resourceId === docId) {
// ignore resources that reference this document itself
return;
}
let resourceLastModifiedTime = 0;
let resourceName = 'file not found';
if (resource) {
resourceLastModifiedTime = resource.getLastUpdated().getTime();
resourceName = resource.getName();
}
return {
resource,
resourceId,
resourceLastModifiedTime,
resourceName,
resourceUrl,
};
}
function getResourceUrl_(resource, docId) {
let resourceUrl;
if (typeof resource.getUrl === 'function') {
resourceUrl = resource.getUrl();
} else if (typeof resource.getLinkUrl === 'function') {
resourceUrl = resource.getLinkUrl();
} else {
// resource does not have a method for getting its URL, ignore it
return;
}
if (!resourceUrl) {
return;
}
if (resourceUrl.startsWith('#heading=')) {
// resource is a bookmark, ignore it
return;
}
if (!resourceUrl.startsWith('http')) {
// resource does not have a full valid URL, ignore it
return;
}
const resourceId = getResourceIdFromUrl_(resourceUrl);
if (resourceId === docId) {
// resource is the document itself, ignore it
return;
}
return resourceUrl;
}
function getResourceIdFromUrl_(resourceUrl) {
if (!resourceUrl) {
// resource does not have a URL, ignore it
return;
}
const resourceUrlParts = resourceUrl.replace(/^https?:\/\//,'').split('/');
if (resourceUrlParts.length <= 3) {
// resource URL does not match the expected Google Drive file URL pattern
return;
}
const resourceId = resourceUrlParts[3];
return resourceId;
}
function mailChangedResources_(resourcesStatusesAndNamesUrls, docName, docUrl) {
let numChangedResources = 0;
let mailMessageStatuses = [];
Object.keys(resourcesStatusesAndNamesUrls).forEach((status) => {
const resourceNameAndUrls = resourcesStatusesAndNamesUrls[status];
let numStatusResourceUrls = 0;
let resourceString = '';
Object.keys(resourceNameAndUrls).sort().forEach((resourceName) => {
const resourceUrls = resourceNameAndUrls[resourceName];
const numResourceUrls = resourceUrls.length;
if (numResourceUrls === 0) {
return;
}
numStatusResourceUrls += numResourceUrls;
const resourceUrlsString = resourceUrls.map((resourceUrl) => ` - ${resourceUrl}`).join('\n');
resourceString = `${resourceString}* ${resourceName}\n${resourceUrlsString}\n`;
});
if (numStatusResourceUrls === 0) {
return;
}
numChangedResources += numStatusResourceUrls;
switch (status) {
case 'added':
mailMessageStatuses.push(`The following embedded documents have been added:\n\n${resourceString}`);
break;
case 'deleted':
mailMessageStatuses.push(`The following embedded documents have been removed:\n\n${resourceString}`);
break;
case 'missing':
mailMessageStatuses.push(`The following embedded documents are missing in Google Drive:\n\n${resourceString}`);
break;
case 'trashed':
mailMessageStatuses.push(`The following embedded documents are in Google Drive's recycle bin:\n\n${resourceString}`);
break;
case 'updated':
mailMessageStatuses.push(`The following embedded documents have been updated recently:\n\n${resourceString}`);
break;
}
});
if (numChangedResources === 0) {
return;
}
const mailMessage = `${mailMessageStatuses.join('\n\n')}`;
const scriptUrl = scriptUrl_();
const mailBody = `${mailMessage}\n\nUpdate the document at ${docUrl}.\n\n---\nSent from ${scriptUrl}.`;
mail_(`${docName} resources`, mailBody);
}
function getResourcePropertyKey_(resourceId) {
return `Google Drive file ID: ${resourceId}`;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment