Skip to content

Instantly share code, notes, and snippets.

@danicholls
Last active February 23, 2022 16:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save danicholls/8c218692772316ff8092cb73647c9f3c to your computer and use it in GitHub Desktop.
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 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