How to email your Kindle highlights to yourself using Google Sheets

Image for post
Image for post

I’ve always been one of those, “Nothing can replace the feel of a real book” kind of people, and I still believe that. But the Kindle is just convenient as hell and being able to slip it in my pocket rather than throw a giant book in my bag has been a game-changer for me. My only complaint is that once I’m done with an e-book it gets filed away into some invisible vault on the internet like in Raiders of the Lost Ark, all of my highlights along with it. I was researching how to better surface these highlights when I came across a service called Readwise.

Basically, Readwise connects to your Kindle account and sends you an email every morning with 5 randomly selected highlights from all the books you have on your account. I instantly signed up. I have a terrible memory and having interesting quotes from books that I’ve read emailed to me daily keeps them fresh in my head.

I love this service and I highly recommend it, but while using it I realized two issues:

  1. It only included books that I read and highlighted on my Kindle. Needless to say, I have a bunch of physical books full of highlights that I would like to add too but they obviously can’t be connected to the internet.
  2. After the free trial finishes the service costs about $5/month or $8 if you want the premium version. I’m not exactly a penny-pincher, but that seemed like a bit much to me.

Instead, I decided to hack together my own version of this. I’m no programmer, but I know enough about following YouTube tutorials that I figured I could manage to put something together. I managed to get it all done using Google Sheets and figured I’d share the process to help anyone else that wants to replicate it. So here’s how you do it.

1. Download your existing highlights using bookcision

Image for post
Image for post

First, you need to get your highlights out of the cloud and onto your computer. To do this I used Bookcision. The official website has instructions on how to use it so I’ll skip the specifics since they can explain their own creation better than I can, but basically it lets you download your book highlights into .txt files which are way easier to open up in a normal text editor like Notepad or TextEdit. Click Download > as plain text

Image for post
Image for post
Make sure to download it as plaint text

Save the file to your computer (I recommend just making a new folder to keep track of them all since the service only lets you download one book at a time). Once you’re done downloading all the highlights from the books you want open up a new Google Sheets document.

2. Copy + Paste the title of the book, the author, and the quote into your Google Sheet

This is where things can get annoying depending how many highlights you have, because you need to copy and paste all of those highlights into your Google Sheet one by one. I know, it’s annoying but trust me you’ll live.

Image for post
Image for post
Column A is the book title, Column B is the author, and Column C is the actual highlight.

Copy the title of the book into column A, the author into column B, and the actual quote into column C. This step took me about 10 minutes for 13 books. While this is kind of tedious, it’s also an opportunity to add any other quotes that you might want to have emailed to yourself. Whether it’s something you heard in a podcast, read in an article, or a highlight from a book on your shelf, this spreadsheet can hold any quotes or fun facts that you want!

Image for post
Image for post
Make sure to rename the sheets exactly as above! Otherwise none of this will work.

Once you’re done adding all your quotes make sure to add a second sheet by hitting the ‘+’ icon and rename the sheets exactly as I have them, capitalization and all. ‘Sheet1’ needs to be named myHighlights and ‘Sheet2’ needs to be named Template before moving on.

Image for post
Image for post
This is the format for the Template page.

In the Template sheet make sure to copy in the text from the image above. The A1 cell is the message that’s going to appear in the body of your email. You can make it say whatever you want at the beginning or the end, but don’t touch what is in the brackets! That’s what the code is looking for and will replace with the appropriate information in the actual email.

Speaking of which, the B1 cell needs to be replaced with the email address that you want to send this to while the C1 cell is going to be the subject line. I made my subject “highlights” but you can obviously say whatever you want here.

3. Time to start coding!

Now as I said, I’m not a programmer at all. I know a little HTML & CSS thanks to Myspace but that’s about it. Javascript is another language to me and I’m sure someone more experienced can make something way more intricate. But hey, I’m a noob. So if you don’t have any experience coding don’t get intimidated because it’s way easier than it looks. Mainly because I already did it for you. Just follow these steps:

  1. In the “myHighlights” Google Sheet locate the menu bar up top and click on Tools > Script Editor. This will open a new Google Scripts page. The text editor in here is where the code goes. It should already have some function in there, but you can delete it because as I said I already have the code.
  2. Visit my GitHub page and copy all of the code in my “kindleHighlights” repository:
Image for post
Image for post

3. Paste that into the Google Script page:

Image for post
Image for post

4. Replace the ‘x’ in line 10 with the number from the last row of your quotes sheet. For example:

Image for post
Image for post

Since the last quote that I entered was in row 358, I replace the ‘x’ in the code with “358”. This is the hardest part because it means that whenever you add more quotes in the future you need to remember to change that number in the code to the new last row.

4. Give Google permission to send emails

Okay, I promise we’re almost done! Once you change the ‘x’ all that’s left to do is give the code permission to run. It’s a few steps, but it isn’t any different than when you click “Login with Google” when you order Seamless or something.

To do this click on the small play icon at the top of the Google Script page. From here it’s going to pop up a bunch of scary looking permissions because you’re essentially telling Google that you want the code you just wrote to use your email to send information. Go through and login to your account to verify that it’s actually you doing this and not some bot and when the giant red exclamation point pops up, click the small “Advanced” text on the bottom to proceed through. Google just wants you to know that the app isn’t verified which we already since know since you wrote it (well, I wrote it. But you get my point).

Image for post
Image for post

Once you give Google all the correct permissions, the code should hopefully run smoothly. Check your email (the same one that you entered into cell B2) to make sure the email went through. If it didn’t work you might want to retrace your steps and make sure that everything is spelled correctly including the email and sheet names. If it did, congratulations! Now all that’s left is to automate the script to automatically run at set intervals. Thankfully, there’s no code needed for this part since Google Script already has this handy feature built it.

5. Schedule the code to automatically run whenever you want!

In the menu bar on the Google Script page just click on Edit > Current project’s triggers > then click the blue Add Trigger button at the bottom right.

Image for post
Image for post

Under where it says “Select event source” make sure to choose the Time-driven option. This will let you determine how often you want to be emailed. I chose every 6 hours but you can choose every day, every week, or even every hour.

Now all you have to do is go back to the page where the code is and hit the play button one last time to run the script again and voila! Now you’ll get emails of a randomly selected quote sent to you automatically and you won’t have to pay any money.

-Adam

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store