Google Spreadsheet

Whether you are buying cryptocurrencies for the long term or the short term it is definitely a time-consuming task to have to check the new price every few hours or days. This post will show how to build an Alerts System using Google Spreadsheet to check all the cryptocurrencies for you and send an email whenever some conditions are met.

First you need to create a new Google Spreadsheet. Then add two sheets: 'Data' and 'Alerts'.

'Data' sheet

The 'Data' sheet will import all cryptocurrencies from coinmarketcap and do some processing over that data. Specifically it will extract the currency name, the price in USD and the price in BTC. The sheet should look like the one below:

'Alerts' sheet

The 'Alerts' sheet will have a row for each cryptocurrency that we want to track. Then for each row the price data will be retrieved from the 'Data' sheet and some below and above conditions will be defined. Should look like the one below:

Note only % Below and % Above are to be configured to set the alarms. Also the Buy price needs to be set so a comparison can be performed.

That is our Google Spreadsheet ready, now all we need are some Google Scripts to do the checks!

Google Script

In that same Google Spreadsheet open the Script editor under the Tools menu. Two functions are needed to perform all checks: checkAlerts() and createAlert().

checkAlerts()

This function runs through the 'Alerts' sheet and check every row (from row 2). Then for each configured cryptocurrency will call createAlert() to assess the conditions. Finally an email is sent if there are any notifications.
Note t_spreadsheet_url and your email address will have to be defined to work for you.

function checkAlerts () {
  // Config
  var t_spreadsheet_url = 'https://docs.google.com/spreadsheets/d/1p1Q7FUdK5a23siktr-KP68-JGDLUGWOeh_-ph5_QoZc/edit#gid=0'  
  var t_spreadsheet = SpreadsheetApp.openByUrl(t_spreadsheet_url);
  var t_sheet = t_spreadsheet.getSheetByName('Alerts'); 
  
  // Init vars
  var t_message = {}
  t_message.send = 0
  t_message.message = "AlertsSheet:\n";
  
  // Loop through spreadsheet
  for (var i=2; i<10; i++){  
    t_message = createAlert (t_sheet, i, t_message);
  }  
  t_message.message = t_message.message + "\n\n\n" + t_spreadsheet_url
  
  // Send email
  if (t_message.send > 0) MailApp.sendEmail("[email protected]", "AlertsSheet", t_message.message);  
}

createAlert()

This function will check the conditions and create an alert if needed.

function createAlert (s, row, message){
  // Config
  col_id = 2; col_value = 3;
  col_below = 5; col_below_percent = 4;
  col_above = 6; col_above_percent = 7;
  col_enabled = 8; col_actioned = 9;
  
  // Get data from spreadsheet
  id = s.getRange(row,col_id).getValues(); value = s.getRange(row,col_value).getValues(); 
  below = s.getRange(row,col_below).getValues(); above = s.getRange(row,col_above).getValues();
  below_percent = s.getRange(row,col_below_percent).getValues(); above_percent = s.getRange(row,col_above_percent).getValues();
  enabled = s.getRange(row,col_enabled).getValues(); was_actioned = s.getRange(row,col_actioned).getValues();
  
  // Init vars
  is_actioned = 0;
  
  // Check alert
  if (enabled == "X" && was_actioned != "X"){
    if (+value < +below) {message.message = message.message + "     - " + id + ": " + value + " < " + (below_percent*100) + "% \n"; is_actioned = +1;}
    if (+value > +above) {message.message = message.message + "     + " + id + ": " + value + " > " + (above_percent*100) + "% \n"; is_actioned = +1;}
    if (is_actioned > 0) {s.getRange(row,col_actioned).setValue("X");}
  }
  if (is_actioned == 1) {
    message.send = 1;
  }
  return message;
}

Script triggers

Last, create a trigger to run the checkAlerts() function. This can be found by clicking Current project's triggers under Edit menu in the Script editor window. What drives your trigger and how often should be configured there. For me it looks something like this:

Finally a copy of the spreadsheet can be found here.

Any questions please comment!