I wrote this in January 2023 but didn't publish for some reason. Rather than updating or expanding it I'm going to publish it as is. And also I'll probably create more Programming Journey entries.
After a brief..eight month..break from much of any programming at all I decided to start a new little project: extracting some specific data from my email and saving that to Google Sheets.
Technically there is an API which includes Python for doing this sort of thing. But instead I’m going to use Google’s AppScript, at least until I get a better idea of what I need to do and how to do it.
I still consider this part of my “Programming Journey” even if it is a side road only indirectly related to my prior venture.
All of these Programming Journey posts can be found in the associated category of this blog.
A brief summary of what I’d like to do
The prior Python adventure – which I’ll go back to eventually(tm) – was about scraping data from my Steam library page and saving it in a file.
This semi-related idea is to scan through my email for game purchase receipts and save that information to a spreadsheet.
If you wondering why I would want to do this it’s pretty simple: I was at one time maintaining a spreadsheet as a database of my games both on various stores (epic, steam, gog, etc) and from various consoles. Either at the time of purchase or every few months I would go in and manually find the emails with the of the games I had purchased and enter them into the spreadsheet one at a time by copy/pasting.
But it’s been too long since I last I did that and getting that spreadsheet up to date just seems too time consuming and tedious.
So why not use a script to do the work for me? It’s built into sheets (or the Google suite, anyway).
The Apps Script platform is based around JavaScript and there are plenty of examples and documentation on how to do this.
Since the receipt emails have the same basic subject line the approach as I see it is to find the receipt emails that go into the email body and find the list of games then save that list to the spreadsheet. And perhaps I’ll include additional email like the date and how much I paid in the transaction. Not necessary for EPIC since all those games are the same price of $0.
I’m starting with my EPIC games receipts – these are the ones given away by EPIC – and it turns out this is fitting because all the receipt emails are only one game. Even the days they give away multiple games it’s one email per game. So this will be much easier than the Steam game emails. Furthermore, I only need to go back in time so far, not extract every EPIC receipt I’ve ever received. I don’t know how far yet. At least until sometime in 2020 if I had to guess. The point is at some point the number of emails to extra with be a static number, I’m not extract an unknown number of emails.
A Humble start
It could be I’m making this entirely too complicated. It could be I’ll copy/paste a few lines here and there and get this done with relative easy
Since I’m documenting as I’m going I may as start from the beginning: step one is creating a new Google sheet, creatively called Games via Gmail. Next, I named the default sheet RawData, since that name worked so well in the last spreadsheet (did it?).
Then I started a new Apps Script and started doing some experimenting with function calls and variable names. I started with something simple: perform a gmail search, save the subject line to a variable, save that variable value to a spreadsheet cell. Okay, that’s several things. But I’ll skip to the end.
function GetGames() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
// SheetName = ss.getSheets()[0].getName(); // not needed directly but left here for reference
// first message only - 0,1
var firstThread = GmailApp.search('subject:"Your Epic Games Receipt"', 0, 1)[0];
var messages = firstThread.getMessages();
// save subject line
var savedSubject = messages[0].getSubject();
//save the value to the spreadsheet
ss.getRange("A1").setValue(savedSubject);
}
I also learned I can test variable values with Logger.log(), which makes debugging that much easier.
This code isn’t supposed to useful, just a the first step: getting something from an email and putting into a spreadsheet. The simplest of accomplishments.
The output of the above script is below. I’m hoping there’s no way to personally ID me based on a receipt number. Nah, it’s fine. That receipt ID number might actually work in my favor as an easy way to avoid duplicates.
Your Epic Games Receipt F221228165721943
Look closer at the EPIC emails, it seems there’s both a Order ID number and an Invoice ID. Not sure why both are necessary. I guess EPIC has to EPIC.
For the next step, I could go in multiple directions: multiple subject lines moving downward in the spreadsheet or just extra the data from the one email I’ve found. Well I have to do both tasks at some point so the real question is which would I do first.
Since successfully getting to the game name seems like it will take a while, I think I’m going to start gathering a list of emails, instead.
Since that 0,1 worked so well above, I’ll start by expanding that to 5 and use a for loop to go through them. Then I’ll also increment the cell number.
BringInString = WriteOrOpenDatafile() # returns game list json string
# below, the -1 means "replace them all"
TakeOutEscSlash = BringInString.replace('''/''','''/''',-1)
It seems this is all it took. I’m going to end this entry here even though I could go on a lot longer. I’ll start fresh with a new entry and try and summarize what I’ve learned.
Reference links:
- Automate the Boring Stuff (free book via web site)
- Automate the Boring Stuff 15 part YouTube playlist (from 2015 but still good for basics)
- JSON via Requests lib YouTube video (mCoding)
- Reference: write a new file in python (W3Schools)
- A page with a better explanation of curses I found
- Python and JSON via “Geeks for Geeks”
- An all-in-one reference page for Python (Learn X in Y Minutes)
- A to-the-point JSON/Python and Python/JSON video (Python Coding Club)
I've made a "clearing house" repo for random python programming projects on GitHub: https://github.com/tildesarecool/ReallyHadtoPython
Tildes on the side 