Automate your Vocabulary List with Google Assistant
EDIT (05/01/2020): Updated to work with the Oxford Dictionary API v2 and fixed for duplicate words.
A recurring annoyance I find myself facing in everyday life is coming across a word or phrase unknown to me, wanting to quickly take note of it so as to improve my diction. Sounds familiar?
This how-to post will guide you through setting up an automated way of quickly and easily adding words or phrases to a spreadsheet and pulling in its definition from the Oxford Dictionary. We will be setting up a spreadsheet to be used as our vocabulary list in Google Sheets, using IFTTT to create a custom Google Assistant action and automatically retrieve definitions using the Oxford Dictionaries API. I highly recommend adapting this guide to suit your personal needs. The script file detailed towards the end of this how-to is available here.
Services we will be using:
- IFTTT
- Google Assistant
- Google Sheets
- Oxford Dictionaries API
Before we start, make sure that you have an account for the above services and access to a Google Assistant-enabled device. I assume that you have some basic coding skills, however if you do not understand the code snippets then go ahead and give the relevant documentation a look through or post a comment below.
As from v2 of the Oxford Dictionary API, the Free account will not work. Therefore, you will need a Prototype account (max of 1000 calls/month) or upgrade your account to their paid ones.
Step 1: Create & Setup Google Sheet
- Create a spreadsheet in Google Sheets. We will be storing the word/phrase, its definition and a link to its entry in the Oxford Dictionary.
- We are going to need 3 columns: (1) Word/Phrase, (2) Definition and (3) Dictionary Link.
- [Optional] Format the rows to be alternating colours, improving legibility.
- To make the rows alternating colours in Google Sheets, go to Format → Alternating Colours, and proceed to select the range A1:C1000 (this can always be increased at a later stage if necessary).
- Select a predefined formatting style or create your own.
4. [Optional] Freeze the first row containing the column headers by selecting the row and go to View → Freeze → 1 row.
Step 2: Create Google Assistant action using IFTTT
If you have never heard of or used IFTTT before, it stands for If This Then That, and allows you to create applets linking your apps and devices. We will be using IFTTT to create a custom Google Assistant command that inserts a word into our spreadsheet.
- Navigate to IFTTT and create a new applet.
- For ‘this’ search Google Assistant → Say a phrase with a text ingredient.
- You can customise the commands and response as you wish. The commands you input will act as the trigger for running this IFTTT applet.
- I found that the phrase ‘Store the word $’ was the most successful, however this may vary. The $ represents the text ingredient you wish to use, and may be a single word or a phrase.
3. For ‘that’ search Google Sheets → Add row to spreadsheet.
- Formatted Row: The TextField cell represents the word or phrase we wish to insert. This will be inserted in the first column of our spreadsheet.
4. Save the applet and test it.
- Once you’ve saved your applet, go ahead and test your applet using a Google Assistant-enabled device. If successful, you should see a new row in your spreadsheet with the word/phrase given.
- Example: Store the word happy.
Step 3: Use Oxford Dictionary API within Google Sheets
Note: When dealing with APIs, Postman is a great tool to easily and quickly create HTTP requests and visualise the data returned. Check out their documentation to get started.
The Oxford Dictionary API will allow us to extract definitions of words or phrases. I recommend testing out the API calls in Postman (or a similar tool), ensuring that authentication is successful. You will need to create an app on your Oxford Dictionaries account in order to retrieve your Application ID and Key.
- Testing the Oxford Dictionary API.
- API Base URL: https://od-api.oxforddictionaries.com/api/v2
- Application ID and Key may be found by navigating to API Credentials on Oxford Dictionary’s site (once logged in) and viewing your app’s details.
- Two Headers need to be added to the request: app_id and app_key, with the appropriate values.
We will be making a GET request to the /entries/{source_lang}/{word_id} endpoint to retrieve the definitions, with the query string: ?fields=definitions
- You may wish to add another query string to make the request less sensitive to strict matches: strictMatch=false
- Final URL should look something like this: https://od-api.oxforddictionaries.com/api/v2/entries/en-gb/your_word?fields=definitions&strictMatch=false
- We will be focusing on the primary definition returned within the senses object.
2. Create a script in Google Sheets to run whenever a new row is inserted, i.e. a new word/phrase is added.
- Google Sheets has an in-built Script Editor, allowing you create and run scripts written in Google Apps Script (a scripting language based on JavaScript). If unfamiliar with JavaScript and/or Google Apps Script, have a look at their documentation.
- From your spreadsheet, select Tools → Script Editor, which should bring up a code editor with a template .gs file.
- Create a function onNewRow() that gets called whenever a new row is inserted into the spreadsheet.
function onNewRow(e) {
var sheet = SpreadsheetApp.getActiveSheet();
if(e.changeType == 'INSERT_ROW') {
// Get index of row inserted
var row = sheet.getLastRow();
// Get word/phrase inserted
var range = sheet.getRange(row, 1);
var phrase = range.getValue();
if (alreadyExists(sheet, phrase, row)) {
sheet.deleteRow(row);
Logger.log(phrase + " already exists!");
} else {
fillDictionaryLink(sheet, row, phrase);
addDefinition(sheet, row, phrase);
// Sort words in ascending order
sheet.sort(1);
Logger.log(phrase + " added!");
}
}
}- Select Edit → Current Project’s Triggers, and add a new trigger running the onNewRow() function with the events from spreadsheet and on change.
3. Create a function to check if the word already exists in your spreadsheet
- Create a function alreadyExists() that will accept 3 arguments sheet, word and row.
- It will get all the values from the first column (the words) and compare them with the new word.
- If the word already exists, the newly added row is deleted.
function alreadyExists(sheet, word, latestRow) {
var lastRowIndex = sheet.getLastRow();
var words = sheet.getRange(2,1,lastRowIndex).getValues();
var wordsFlat = words.map(function(row) {
return row[0];
});
indexOf = wordsFlat.indexOf(word) + 2;
Logger.log(latestRow + ", " + indexOf);if (indexOf != -1 && indexOf != latestRow) {
return true;
}
return false;
}
4. Create a function to add the definition to the spreadsheet.
- Create a function addDefinition() that will accept 3 arguments sheet, row and phrase, and insert the definition into the appropriate cell.
- When using a phrase in the request, we need to substitute any whitespace for an underscore ‘_’.
- It is important that we encode the word or phrase we wish to retrieve a definition for, so as to ensure that any special characters are encoded appropriately.
- Remember to replace the app_id and app_key placeholders with your credentials.
function addDefinition(sheet, row, phrase) {
var base_url = 'https://od api.oxforddictionaries.com/api/v1/entries/en/';
// To lowercase and substitute whitespace for '_'
phrase.toLowerCase();
phrase = phrase.replace(/\s/g, '_');
// Encode word/phrase
phrase = encodeURI(phrase);
// Append phrase to base URL
var url = (base_url.concat(phrase)).concat('/definitions');
// Make a GET request to Oxford Dictionary API and retreive the definition/s.
var options = {
"method": "GET",
"headers": {
"app_id": "YOUR_APP_ID",
"app_key": "YOUR_APP_KEY"
}
};
var response = UrlFetchApp.fetch(url, options);
var responseObj = JSON.parse(response);
var definition = responseObj["results"][0]["lexicalEntries"][1]["entries"][0]["senses"][0]["definitions"];
// Insert definition
sheet.getRange(row, 2).setValue(definition);
}- Call our addDefinition() function from onNewRow() so that it is called whenever a new row is inserted. Place the following code above sheet.sort(1);
addDefinition(sheet, row, phrase);Step 4: Add Oxford Dictionary link for each new entry
Create a function to add the word/phrase Oxford Dictionary URL.
- Since we are storing a basic definition of the word or phrase in our spreadsheet, we may wish to view more details. We shall therefore be automatically inserting a link to the word or phrase’s entry in the Oxford Dictionary.
- Create a function fillDictionaryLink() that will accept 3 arguments sheet, row and phrase, and insert the link into the appropriate cell.
function fillDictionaryLink(sheet, row, phrase) {
var base_url = 'https://en.oxforddictionaries.com/definition/';
//Substitute whitespace for '-'
phrase = phrase.replace(/\s/g, '-');
// Append phrase to base URL
var link = base_url.concat(phrase);
// Insert link
sheet.getRange(row, 3).setValue(link);
}- Call our fillDictionaryLink() function from onNewRow() so that it is called whenever a new row is inserted. Place the following code underneath the call to the addDefinition() function.
addDefinition(sheet, row, phrase);Step 5: Have fun and experiment!
If all was successful, you should now be able to say your trigger phrase to your Google Assistant, which inserts a new row into your spreadsheet with the word or phrase given, and then adds a definition and link to its entry in the Oxford Dictionary.
Google Assistant does not always understand the word or phrase I try telling it to save. This how-to does not result in 100% success rate, and expect occasionally having to repeat your trigger sentence.
Resources
Some external resources that may aid you with services used in this guide.
