Random Musings

You will need a Gmail account (or google workspace/GSuite email account) and access to google sheets

1) Set up the google sheet to host your list

Create a new google sheet.

Call it whatever you want, for instance reading list

Inside reading list create four columns: Title, Link, Comment and Date

Select the entire Date column and click Format -> Number -> Date

2) Create the script to process your emails

From inside the reading list google sheet click Tools, Script Editor

Copy and paste this code into the Script Editor (from // add to // end):

// add menu to Sheet

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('Extract Emails')

.addItem('Extract Emails...', 'extractEmails')

.addToUi();

}

function extractEmails() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("Sheet1");

var label = GmailApp.getUserLabelByName("RL-Add");

var threads = label.getThreads();

var labeldone = GmailApp.getUserLabelByName("RL-Done");

for (var i=0; i<threads.length; i++)

{

var messages = threads[i].getMessages();

for (var j=0; j<messages.length; j++)

{

var msg = messages[j].getPlainBody();

var sub = messages[j].getSubject();

var dat = messages[j].getDate();

var link = msg.split("\n")[0];

var comment = msg.split("\n")[1];

if (comment.length<=3)

{var comment = msg.split("\n")[2];}

sheet.appendRow([sub,link, comment, dat])

}

threads[i].removeLabel(label);

threads[i].addLabel(labeldone);

}

}

// end

Save the script by clicking the save icon and give it a name, for instance "Extract Emails".

On the dropdown to the left of execution log, select "Extract emails" if it isn't selected.

Click on the run icon. Accept any permissions (it will ask for permission for the script you just wrote to access your google sheets and your google email).

Close the script editor window in your browser returning to your google sheet from #1.

Click on refresh in your browser window for reading list. You should see a menu appear called "Extract Emails" at the top to the right of Help

Whenever you want to manually scan your email and add additional articles, click on that menu and choose extract emails in the submenu.

3) Prepare your Gmail to receive the emails

Go to mail.google.com in a new browser tab

Click on Settings (gear icon at top) and choose "See all settings"

Click on Labels

Click on create new label

Enter RL-Add and click create

Enter RL-Done and click create

Now click on Filters and Blocked Addresses

Click "Create a New Filter"

Under "from "enter the email addresses you want to be able to add to your reading list -- separate them by the word OR if there are more than one. (E.g. somebody at university.org OR somebody at company.com). NB they can't be your same Gmail address because emails you send to yourself bypass the inbox so it won't work. Note you also want to restrict these emails or other people could add to your reading list without your permission (which could be embarrassing)

Under "to" enter youremail+reading at gmail.com (or whatever your google domain is, the key is to include +reading at the end before the @ sign because this means the reading list emails won't jam up your inbox)

Click continue

On the next page select:

"Skip the inbox"

"Apply the label" (and choose RL-Add)

"Never send it to spam"

Click save filter

4) How to send the email:

Email yourself at youremail+reading at gmail.com from the address you permitted in "from" above

Subject is the title of the article (will go into title)

First line is the Link followed by enter (will go into link)

Second and subsequent lines are your pithy text about the article (will go into comment)

Date is automatic on the date of import

5) Test and automate

Send yourself a test email as above

NB: I am usually reading articles on my phone -- so in my browser if I "share" the journal article webpage via my email client, the subject and link are already in the new message. I make sure to hit enter after the link to make a line break (needed for the script) and then I write my pithy comment.

Go into the reading list sheet.

Run the script Extract emails

The new item should "show up" in the last row. You now have a way to record all your journal reading via email.

Want to do it automatically? Make sure you test that everything is working first. Then reload the script editor and select "triggers" (clock icon on left) and create a "time based" trigger to run "Extract emails" every X hours. or days I use every 6 hours.

*** Stop here unless you really want to get overly fancy and make it nicer looking***

6) Get CME?

I used to select the year's worth of articles , print it as a PDF, and upload them to the Royal College portal in Canada as "bulk reading". You need to format the width of your columns so it looks nice at 8.5x11" size. I'd just count the rows for that year and claim that # of reading article credits. Worked every time.

But it needs to be nicer looking

[If you are handy, you can probably just take this template from excel and copy it across to your google sheet including the extra functions on Sheet1 and then jump to conditional formatting below]

On Sheet1 inside reading list, add this formula to cell E1:

=CountA(A2:A)

Then, create a new sheet using the + key

Call that sheet "Formatted"

Label the first 3 columns Details, Date, and Record

In cells C2, C3, and C4 put the number 1

In cell C5 put:

=IF(OR(C2>=Sheet1!$E$1,C2=""),"",C2+1)

Copy and paste the formula from C5 all the way down to row 1000

In cell B2 put:

=IF(C2="","",INDEX(Sheet1!$A$2:$D,C2:C,4))

Copy and past that all the way down to row 1000

In cell A2 put:

=IF(C2<>"",INDEX(Sheet1!$A$2:$D,C2,1),"")

In cell A3 put:

=IF(C3="","",IF(OR(INDEX(Sheet1!$A$2:$D,C3,2)="",C3=""),"",HYPERLINK(INDEX(Sheet1!$A$2:$D,C3,2),"Article")))

In cell A4 put:

=IF(C4="","",INDEX(Sheet1!$A$2:$D,C4,3))

Now select cells A2, A3 and A4 and hit control C for copy

Now select rows A5->A1000 and hit control V for paste

That should set things up so that the data from Sheet1 is presented in a nice vertical format for printing -- just play with the column width and make column A use text wrapping to the next line (select column, click wrap text icon and choose wrap to next line)

Select format, conditional formatting

Apply to range A2:C

Custom formula

=ISEVEN($C2)

Choose a color

Now you have a beautiful shading where it alternates from your color and white to make it easier to see individual entries.

One day you may have more than 333 articles read -- just add an additional 1000 rows and copy the formulas by selecting a three row entry columns A to C and copy that, then paste in the next logical line below.