Last active
February 23, 2022 16:45
-
-
Save danicholls/8c218692772316ff8092cb73647c9f3c to your computer and use it in GitHub Desktop.
I cobbled together a lot of disparate notes on Google Scripts to send a summary email of the emails I had starred, so they could be followed up on, and to control the subscription list by a Google Sheet. Don't need it anymore, but in case someone does, it's at least a good place to start. NOTE: LOOK FOR and do TODOs.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// This didn't seem to work on its own; set trigger via Apps Script interface. | |
// ScriptApp.newTrigger('bottleTheStars').timeBased().everyWeeks(1).onWeekDay(ScriptApp.WeekDay.MONDAY).atHour(4) | |
function bottleTheStars() { | |
const max = 20 | |
let noHtml = `Below are the ${ max } latest unresolved email threads (still starred). | |
The link should take you to a search that will contain the thread (and hopefully only that thread). | |
` | |
let html = '<p>' + noHtml.replace(/\n/g, '<br />') + '</p><hr />' | |
const emails = GmailApp.search("is:starred label:XXXX") // TODO: replace XXXX with your label | |
const limit = emails.length > max ? max : emails.length | |
let emailTo = [ 'Someone <*@gmail.com>' ] // TODO: Make this into a real address. It's the default (before Sheet entries). | |
const sheetID = 'XXXX' // TODO: replace XXXX with your Sheet ID | |
const ss = SpreadsheetApp.openById( sheetID ) | |
const emailsInSheet = ss.getSheets()[0].getRange('A1:A').getValues() // first column == email addresses | |
emailsInSheet.forEach(cell => { | |
emailTo.push( cell ) | |
}) | |
if ( limit == 0 ) { | |
html = '<p>No emails found. Huzzah?!</p>' | |
} | |
for ( i=0; i < limit; i++ ) { | |
if ( emails[ i ] ) { | |
let thread = emails[ i ] | |
let subj = '' + thread.getFirstMessageSubject().slice(0, 50) | |
let sender = '' + thread.getMessages()[0].getFrom().split('<')[0] | |
let firstDate = thread.getMessages()[0].getDate() | |
let firstDateFormatted = Utilities.formatDate(firstDate, Session.getTimeZone(), 'MM/dd/yyyy') | |
let lastDate = thread.getLastMessageDate() | |
let lastDateFormatted = Utilities.formatDate(lastDate, Session.getTimeZone(), 'MM/dd/yyyy') | |
// let link = thread.getPermalink() // DO NOT USE: only works for YOUR email, not shared across accounts | |
// so, use search... | |
let link = 'https://mail.google.com/mail/u/0/#search/' | |
link += encodeURIComponent('after:' + Utilities.formatDate(firstDate, Session.getTimeZone(), 'yyyy/MM/dd')) | |
link += '+' | |
link += encodeURIComponent('from:"' + sender +'"') // TODO: Fix? This seems to sometimes generate string breaks. | |
link += '+' | |
link += encodeURIComponent( '"'+ subj + '"' ).split('%20').join('+') | |
let body = thread.getMessages()[0].getPlainBody().replace(/\r?\n|\r/g, ' ').slice(0, 150) | |
html += '<p style="margin: 1rem 0.5rem 1.25rem;"><b>"' + subj + '"</b> from ' + sender + '<br />' | |
html += 'Started: ' + firstDateFormatted | |
if ( firstDateFormatted !== lastDateFormatted ) { | |
html += ' | Last response: ' + lastDateFormatted | |
} | |
if ( thread.getMessageCount() < 2 ) { | |
html += ' | <b style="background-color:yellow; color:darkorange;">No response yet.</b>' | |
} | |
html += '<br />"' + body + '..."<br />' + link + '</p>' | |
noHtml += '"' + subj + '" from ' + sender + '\n' | |
noHtml += 'Sent in: ' + firstDateFormatted | |
if ( firstDateFormatted !== lastDateFormatted ) { | |
noHtml += ' | Last response: ' + lastDateFormatted | |
} | |
if ( thread.getMessageCount() < 2 ) { | |
noHtml += ' | NO RESPONSE yet.' | |
} | |
noHtml += '\n"' + body + '..."\n' + link + '\n\n' | |
} | |
} | |
html += '<hr /><p>This email automatically generated because you are on the project team. Contact administrator at the From address to remove.</p>' | |
noHtml += '\n\nThis email automatically generated because you are on the project team. Contact administrator at the From address to remove.' | |
GmailApp.sendEmail(emailTo.join(','), limit + ' emails to wrap up', noHtml, { | |
htmlBody: html | |
}) | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment