Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
function myFunction() {
const sheet = SpreadsheetApp.getActive().getActiveSheet();
const client_id = sheet.getRange("B1").getValue();
const client_secret = sheet.getRange("D1").getValue();
const f1 = sheet.getRange("F1").getValue();
const playlist_id = f1.startsWith("https://open.spotify.com/") ? f1.slice(34, f1.indexOf('?')) : f1;
const keytuple = client_id + ":" + client_secret;
const client64 = Utilities.base64Encode(keytuple, Utilities.Charset.UTF_8);
const camelot = [
['5A', '12A', '7A', '2A', '9A', '4A', '11A', '6A', '1A', '8A', '3A', '10A'],
['8B', '3B', '10B', '5B', '12B', '7B', '2B', '9B', '4B', '11B', '6B', '1B']
];
const option_basic = {
'method': 'post',
'contentType': 'application/x-www-form-urlencoded',
'payload': 'grant_type=client_credentials',
'headers' : {
'Authorization' : 'Basic ' + client64
},
'muteHttpExceptions': true
};
const res_basic = UrlFetchApp.fetch('https://accounts.spotify.com/api/token', option_basic);
const bear = JSON.parse(res_basic).access_token;
const option_bearer = {
'method': 'get',
'headers' : {
'Authorization' : 'Bearer ' + bear
},
'muteHttpExceptions': true
};
var result = [];
var next = 'https://api.spotify.com/v1/playlists/' + playlist_id + '/tracks';
for (var itr = 0; itr < 10; itr++) {
const res_playlist = UrlFetchApp.fetch(next, option_bearer);
const playlist = JSON.parse(res_playlist);
result = result.concat(playlist.items.map((e, i) => {
const res_afeat = UrlFetchApp.fetch('https://api.spotify.com/v1/audio-features/' + e.track.id, option_bearer);
const afeat = JSON.parse(res_afeat);
return [
playlist.offset+i+1,
"=HYPERLINK(\"" + e.track.external_urls.spotify + "\", \"" + e.track.name + "\")",
"=HYPERLINK(\"" + e.track.album.external_urls.spotify + "\", \"" + e.track.album.name + "\")",
e.track.artists.map(artist => artist.name).join(', '),
millisToMinutesAndSeconds(afeat.duration_ms),
Math.round(afeat.tempo),
camelot[afeat.mode][afeat.key],
afeat.energy,
afeat.danceability,
]
}));
if (playlist.next == null) break;
next = playlist.next;
}
sheet.getRange(4, 1, result.length, result[0].length).setValues(result);
}
function millisToMinutesAndSeconds(millis) {
var minutes = Math.floor(millis / 60000);
var seconds = ((millis % 60000) / 1000).toFixed(0);
return minutes + ":" + (seconds < 10 ? '0' : '') + seconds;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment