Streamlined listings using Google Sheets and the Discogs API
For quite a while now, I have been collecting vintage LP records. It is a great occupation, which brings a lot of music back to life and is a great way to explore cities, through their record stores.
Lately, the website discogs.com has emerged as a fantastic companion service for records collectors. It features a huge, comprehensive database of records, with all the different editions, tracks, details and even preview of the songs when they are available online. On top of that, the website provides a market place to sell and buy records, with price history and all sort of cool tools. This website truly is a beautiful work on web design and application.
What’s even better, they also provide a API, allowing programmatic access to their data. Needless to say, I’ve been itching for a while to play with it. So, I finally took a minute to look at it and ended-up connecting this API to a Google Sheet that allows for fast records indexing and listing. Let me show you!
The base sheet used in the following can be found here.
Authentication
The first thing you will need is a custom API token. You can obtain it at https://www.discogs.com/settings/developers. Once generated, you should fill the sheet with it:
Indexing your records
For fast indexing, the idea is to pull all the usual data, artist, album, label, from the discogs API so that one only has to enter a discogs release ID and let the script do the rest. Here’s the code:
function discogsToken() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Records");
return sheet.getRange(4,1).getValue();
}function makeUrl(path) {
return "https://api.discogs.com" + path + "?token=" + discogsToken();
}function discogsRelease(id) {
var key = "discogs-" + id;
var cache = CacheService.getDocumentCache();
var cached = cache.get(key);
if (cached != null) {
return JSON.parse(cached);
}
var url = makeUrl("/releases/" + id);
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
cache.put(key,json,864000); // 10 days cache..
return JSON.parse(json);
}function discogsThumbUrl(id) {
return discogsRelease(id).thumb;
}function discogsTitle(id) {
return discogsRelease(id).title;
}function discogsArtist(id) {
return discogsRelease(id).artists[0].name;
}function discogsLabel(id) {
return discogsRelease(id).labels[0].name;
}function discogsUrl(id) {
return discogsRelease(id).uri;
}
The language used here is google’s own Google Script. It’s essentially a spin-off of Javascript with integrated Google APIs and specific functionalities such as HTTP requests interfaces, making it pretty easy to get started quickly.
In this code, we fetch the API token previously entered, generate a URL for a release API request and distribute those results to the different target entries. We also cache the result as we can reasonably expect release data to not change too often and because Discogs is not happy when an application uses the API too frequently, which is bound to happen as the index grows bigger and bigger.
On the sheet side, we use those functions to populate our rows. On each target row, we use the functions discogsArtist , discogsAlbum, etc. Then, adding a new entry is as easy as extending the spreadsheet values with the usual select/drag method:
Listing for sale
Finally, if you want to list records as available for sale, you can also use the discogs API, making extremely efficient to keep an inventory of the records you list online through a single spreadsheet and synchronize your listings on the website. Let’s look at the code first:
var validConditions = [
"Mint (M)", "Near Mint (NM or M-)",
"Very Good Plus (VG+)", "Very Good (VG)",
"Good Plus (G+)", "Good (G)",
"Fair (F)", "Poor (P)"
]var validSleeveConditions = [
"Mint (M)", "Near Mint (NM or M-)",
"Very Good Plus (VG+)", "Very Good (VG)",
"Good Plus (G+)", "Good (G)",
"Fair (F)", "Poor (P)",
"Generic", "Not Graded", "No Cover"
]var validStates = ["Draft", "For Sale"]function syncDiscogsListing(sheet, header, index) {
var row = sheet.getRange(index,1,1,header.length);
var data = row.getValues()[0];
var releaseIndex = header.indexOf("Release ID");
var listingIndex = header.indexOf("Listing ID");
var statusIndex = header.indexOf("Listing Status");
var conditionIndex = header.indexOf("Condition");
var sleeveConditionIndex = header.indexOf("Sleeve Condition");
var priceIndex = header.indexOf("Price");
var commentsIndex = header.indexOf("Comments");
var listedOnIndex = header.indexOf("Listed On");
var errorsIndex = header.indexOf("Errors");
var errorCell = sheet.getRange(index,errorsIndex+1);
errorCell.setValue("");
if (!data[releaseIndex]) return;
var errors = [];
var checkError = function(index, values) {
if (values.indexOf(data[index]) == -1) {
var label = header[index];
errors.push("Error: " + label + " should be one of: " + values.join(", "));
}
}
checkError(statusIndex,validStates);
checkError(conditionIndex,validConditions);
checkError(sleeveConditionIndex,validSleeveConditions);
if (errors.length > 0) {
errorCell.setValue(errors.join(", "));
return;
}
var options = {
"method": "post",
"contentType": "application/json"
};
var payload = {
release_id: data[releaseIndex],
condition: data[conditionIndex],
sleeve_condition: data[sleeveConditionIndex],
price: data[priceIndex],
comments: data[commentsIndex],
status: data[statusIndex]
};
var url;
if (data[listingIndex]) {
url = makeUrl("/marketplace/listings/" + data[listingIndex]);
} else {
url = makeUrl("/marketplace/listings");
}
options.payload = JSON.stringify(payload);
var response = UrlFetchApp.fetch(url,options); if (!data[listingIndex]) {
var ret = JSON.parse(response.getContentText());
// Set Listing ID
var cell = sheet.getRange(index,listingIndex+1);
cell.setValue(ret.listing_id);
// Set Listed On
cell = sheet.getRange(index,listedOnIndex+1);
cell.setValue((new Date).toLocaleDateString());
}
}
function syncDiscogsListings() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Records");
var lastRow = sheet.getLastRow();
var header = sheet.getDataRange().getValues()[0]; var index;
for (index = 2; index <= lastRow; index++) {
syncDiscogsListing(sheet,header,index);
}
}
The top-level function syncDiscogsListings fetches the whole array of data and synchronizes the listings for each populated row, using the function syncDiscogsListing .
The function syncDiscogsListing takes the first row as header to find all the entry’s relevant information, performs basic data validation and generates a HTTP POST request that either creates a new listing if it was not present already or updates an existing one. That’s it!
Last words
This was a truly enjoyable experience. As we grow more and more connected, APIs such as this one create new ways of interacting with data and services that are really interesting and can make life much easier. The only drawbacks that I experienced were the following:
- Discogs does not provide a sandboxed version of their API. This means that you have to do your testing on live data. So, when creating a listing for sale, make sure that you swiftly delete it if that was just meant as a test!
- There does not seem to be an easy path forward from sale listings to actual sales. I have not sold records through Discogs so I have not had a chance to play with real data though. It would be great to be able to wrap the whole workflow through a single spreadsheet. Here, too, a sandboxed API would be great.
Hope you enjoyed this!
PS: No records emoji? Someone please tell @sundarpichai! 😂
