Crypto Portfolio API via Google Sheets

CryptoPortfolioAppleWatchApiViaGoogleSheets.gs

// This App Script attaches to the following Google Sheet to provide API
// way of tracking assets in one's crypto portfolio.
// Personal usage of this is to pull numbers into Numerics API that
// shows values in widgets (complications) on Apple Watch watchface.
// Google Sheet: https://docs.google.com/spreadsheets/d/1g3WKGK4IgnzGNJn5l1D1BJpcuEsHGUfMTmydshz1XG0/edit?usp=sharing
// See this post for more info on setup: https://www.reddit.com/r/AppleWatch/comments/a6k82x/request_complication_to_display_simple_data_from/

function doGet(e) {
  return dispatchRequest(e);
}

function dispatchRequest(e) {
  var action = (e) ? e.parameter.action : "summary";

  if ("price" === action) {
    return findPrice(e.parameter.currencyName);
  } else if ("summary" === action) {
    return findAllPricesJson();
  } else {
    return ContentService.createTextOutput('Invalid relativePath parameter!');
  }
}

function refreshInProgress() {
  var refreshAlreadyInProgress = PropertiesService.getScriptProperties().getProperty('REFRESH');
  if ('FALSE' === refreshAlreadyInProgress) {
    return false;
  }
  var lastRefreshDateTimeStr = PropertiesService.getScriptProperties().getProperty('LAST_REFRESH_DATETIME');
  var lastRefreshDateTime = stringToDate(lastRefreshDateTimeStr);
  // Calculate the time difference in milliseconds
  var timeDifference = new Date().getTime() - lastRefreshDateTime.getTime();
  // Convert the time difference to minutes
  var minutesDifference = Math.floor(timeDifference / (1000 * 60));

  // sometimes errors happen so refresh doesn't close transaction not allowing following refreshes
  // if refresh didn't happen for 5 minutes - it means it's no longer in progress
  return minutesDifference <= 5;
}

function refresh() {
  if (refreshInProgress()) {
    return;
  }

  PropertiesService.getScriptProperties().setProperty('REFRESH', 'TRUE');

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var ss = doc.getSheetByName('Portfolio');
  
  var formulasMap = new Map();

  var formulaRanges = ['b5', 'b6', 'b7', 'b8', 'b9', 'b10', 'b11', 'b12', 'b13', 'b14', 'b15', 'b16', 'b17', 'b18', 'b19', 'b20'];
  for (var i = 0; i < formulaRanges.length; i++) {
    var cellarRange = ss.getRange(formulaRanges[i]);
    formulasMap.set(formulaRanges[i], cellarRange.getFormulas());
    cellarRange.clearContent();
  }
  SpreadsheetApp.flush();
  for (var i = 0; i < formulaRanges.length; i++) {
    var theFormulas = formulasMap.get(formulaRanges[i])
    var cellarRange = ss.getRange(formulaRanges[i]);
    cellarRange.setFormulas(theFormulas);
  }
  SpreadsheetApp.flush();
  PropertiesService.getScriptProperties().setProperty('REFRESH', 'FALSE');
  PropertiesService.getScriptProperties().setProperty('LAST_REFRESH_DATETIME', dateToString(new Date()));
}

function findPrice(currencyName) {
  // already checks if refreshed
  if (!currencyName) {
    return ContentService.createTextOutput('Error: currencyName parameter is missing.');
  }
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName('Portfolio');
  var values = sheet.getDataRange().getValues();
  var output = {
    "postfix": "Default Currency Ticker",
    "data": {
      "value": "Default Currency Price"
    }
  };

  if ('TOTAL_VAL' === currencyName || 'INVESTMENT' === currencyName || 'PROFIT' === currencyName || 'PROFIT_PCT') {
    var cellMap = new Map();
    cellMap.set('TOTAL_VAL', [1, 4]);
    cellMap.set('INVESTMENT', [1, 5]);
    cellMap.set('PROFIT', [1, 6]);
    cellMap.set('PROFIT_PCT', [1, 7]);

    output['postfix'] = currencyName;
    output['data']['value'] = findNumberInCellTxt(currencyName, cellMap.get(currencyName)[0], cellMap.get(currencyName)[1]) + '$';
    var jsonStr = JSON.stringify(output);

    return ContentService.createTextOutput(jsonStr).setMimeType(ContentService.MimeType.JSON);
  };
 
  // Loop through the rows to find the entry with the specified currency
  for (var i = 4; i < values.length; i++) {
    var rowCurrencyName = values[i][0];

    // If the email matches, populate the output object and break out of the loop
    if (rowCurrencyName === currencyName) {
      var price = values[i][1];
      price = Math.floor(price);

      output['postfix'] = values[i][0];
      output['data']['value'] = price + "";
      break;
    }
  }

  // If no matching entry is found, return an error message
  if (Object.keys(output).length === 0) {
    ContentService.createTextOutput(JSON.stringify({})).setMimeType(ContentService.MimeType.JSON);
  }

  var jsonStr = JSON.stringify(output);
  return ContentService.createTextOutput(jsonStr).setMimeType(ContentService.MimeType.JSON);
}

function findAllPricesJson() {
  if (refreshInProgress()) {
    var summaryJsonStr = PropertiesService.getScriptProperties().getProperty("SUMMARY");
    
    return ContentService.createTextOutput(summaryJsonStr).setMimeType(ContentService.MimeType.JSON);
  }
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName('Portfolio');
  var values = sheet.getDataRange().getValues();
  // total value as first line
  var output = {};
  
  output['Summary'] = 'Profit: ' + findNumberInCellTxt('PROFIT', 1, 6) + '$ (' + findNumberInCellTxt('PROFIT_PCT', 1, 7)  + '%)\n';
  output['Summary'] = output['Summary'] + 'Total Value: ' + findNumberInCellTxt('TOTAL_VAL', 1, 4) + '$\n';
  output['Summary'] = output['Summary'] + 'Investment: ' + findNumberInCellTxt('INVESTMENT', 1, 5) + '$\n';

  output['Summary'] = output['Summary'] + '\n';

  // Loop through the rows to find the entry with the specified currency
  for (var i = 4; i < values.length; i++) {
    var rowCurrencyName = values[i][0];
    var price = values[i][1];
    var profit = Math.floor(values[i][6]);
    var profitPct = Math.floor(values[i][7]);
    var indInvestment = values[i][5];
    //price = Math.floor(price);
    var avgBuyPrice = values[i][3];
    var value = Math.floor(values[i][4]);

    //output = output + '\n\n`' + rowCurrencyName + '`: *' + price + '$*\navg: ' + avgBuyPrice + '$\nval: ' + value + '$';

    output['Summary'] = output['Summary'] + 
      rowCurrencyName + '\n' +
      'Profit: ' + profit + '$ (' + profitPct + '%)\n' +
      'INV: ' + indInvestment + '$\n' +
      'Price: ' + price + '$\n' +
      'AVGBP: '+ avgBuyPrice + '$\n' +
      'Value: ' + value + '$\n\n';
  }
  var prevSummaryJsonStr = PropertiesService.getScriptProperties().getProperty("SUMMARY");
  var newSummaryJsonStr = JSON.stringify(output);
  // If no matching entry is found, return an error message
  if (newSummaryJsonStr.length < 10) {
    return ContentService.createTextOutput(prevSummaryJsonStr).setMimeType(ContentService.MimeType.JSON);
  } else {
    PropertiesService.getScriptProperties().setProperty("SUMMARY", newSummaryJsonStr);
    return ContentService.createTextOutput(newSummaryJsonStr).setMimeType(ContentService.MimeType.JSON);
  }
}

function findNumberInCellTxt(name, rowIdx, colIdx) {
  if (refreshInProgress()) {
    var prevPriceTxt = PropertiesService.getScriptProperties().getProperty(name);
    
    return prevPriceTxt + '';
  }
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName('Portfolio');
  var values = sheet.getDataRange().getValues();
  // total value as first line
  var newNumber = Math.floor(values[rowIdx][colIdx]);
  PropertiesService.getScriptProperties().setProperty(name, newNumber + '');

  return newNumber + '';
}

function sendTelegramMsg(msg) {
  msg = encodeURIComponent(msg);

  var token = '646064xxxxxxxxxxxxxxy09RVF9Y'
  var chatID = '5230xxxxx'
  var url = 'https://api.telegram.org/bot' + token + '/sendMessage?parse_mode=markdown&chat_id='+ chatID + '&text=' + msg;

  UrlFetchApp.fetch(url, {'method': 'post'});
}

// Function to convert date to string
function dateToString(date) {
  var dateString = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
  return dateString;
}

// Function to convert string to date
function stringToDate(dateString) {
  var date = new Date(dateString);
  return date;
}