eP9ifmum

1var offset = { cmm: 4, gecko: 13 };
2
3function onOpen() {
4  var ui = SpreadsheetApp.getUi();
5  ui.createMenu('Crypto')
6      .addItem('Update Data - All','runUpdate')
7      .addItem('Update Data - CMM','getCMM')
8      .addItem('Update Data - Gecko','getGeckoData')
9      .addItem('Update Gecko IDs', 'getGeckoID')
10      .addToUi();
11}
12
13function runUpdate() {
14  getCMM();
15  getGeckoData();
16  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
17  sheet.getRange(getSheetCoins(sheet, 6, "findBlank"), offset.cmm).setValue(new Date().toLocaleTimeString());
18}
19
20function getCMM() {
21  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
22  var sheetMap = getSheetCoins(sheet, 1, '*');
23  var url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?limit=600", 
24  params = { "headers": { "X-CMC_PRO_API_KEY": "*INSERT API KEY*" } }
25  var data = UrlFetchApp.fetch(url, params), row, cell, percent = ['1h', '24h', '7d', '30d', '60d', '90d'], mcap, newData;
26  if (data.getResponseCode() != 200) { return; }
27  data = JSON.parse(data.getContentText()).data;
28  for (var coin = 0; coin < data.length; coin++) {
29    row = sheetMap.indexOf(data[coin].symbol); if (row == -1) { continue; }
30    //Logger.log(data[coin].symbol + '-' + data[coin].quote.USD.price);
31    cell = sheet.getRange(row + 2, offset.cmm + 2);
32    if (cell.getValue() > data[coin].quote.USD.price) { cell.setBackgroundRGB(255, 186, 186); } //red
33    else if (cell.getValue() < data[coin].quote.USD.price) { cell.setBackgroundRGB(186, 255, 186); } //green
34    
35    mcap = data[coin].quote.USD.market_cap;
36    if (mcap > 1000000000) { mcap = (mcap / 1000000000).toFixed(1) + "B"; } 
37    else { mcap = (mcap / 1000000).toFixed(1) + "M"; }
38    newData = [mcap, data[coin].cmc_rank, data[coin].quote.USD.price];
39    for (var each = 0; each < percent.length; each++) {
40        newData.push(data[coin].quote.USD['percent_change_' + percent[each]].toFixed(2));
41    }
42    sheet.getRange(row + 2, offset.cmm, 1, 9).setValues([ newData ]);
43  }
44}
45
46function getGeckoID() {
47  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
48  var sheetMap = getSheetCoins(sheet, 1, "*");
49  var url = "https://api.coingecko.com/api/v3/coins/list";
50  var params = { "muteHttpExceptions": true };
51  var data = UrlFetchApp.fetch(url, params);
52  if (data.getResponseCode() != 200) { return; }
53  data = JSON.parse(data.getContentText());
54  for (var each = 0; each < data.length; each++) {
55    row = sheetMap.indexOf(data[each].symbol.toUpperCase()); if (row == -1) { continue; }
56    cell = sheet.getRange(row + 2, 3);
57    if (cell.getValue() != '') { continue; }
58    cell.setValue(data[each].id);
59  }
60}
61
62function getGeckoData() {
63  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
64  var coins = getSheetCoins(sheet, 3), sheetMap = getSheetCoins(sheet, 3, '*');
65  var url = "https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&ids=" + coins.join('%2C') + "&sparkline=false&price_change_percentage=200d%2C1y";
66  var params = { "muteHttpExceptions": true };
67  var data = UrlFetchApp.fetch(url, params);
68  if (data.getResponseCode() != 200) { 
69    return; 
70  }
71  data = JSON.parse(data.getContentText());
72  //Logger.log(JSON.stringify(data, null, 2));
73  for (var each = 0; each < data.length; each++) {
74    row = sheetMap.indexOf(data[each].id); if (row == -1) { continue; }
75    sheet.getRange(row + 2, offset.gecko, 1, 3).setValues([ [ data[each].price_change_percentage_200d_in_currency, data[each].price_change_percentage_1y_in_currency, data[each].ath ] ]);
76  }
77}
78
79function getPredictionData() {
80  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home');
81}
82
83function getSheetCoins(sheet, column, blank) {
84  var sheetData = sheet.getRange(2, column, sheet.getLastRow()-1, column).getDisplayValues();
85  var map = [], omit = ['POPULAR', 'SPECULATIVE', 'NFT', 'COMPANIES', 'MEMES'];
86  for (var row = 0; row < sheetData.length; row++) {
87    if (sheetData[row][0].length == 0 || omit.indexOf(sheetData[row][0]) > -1) { 
88      if (blank == 'findBlank') { return row+2; }
89      if (blank) { map.push(blank); } continue; 
90    }
91    map.push(sheetData[row][0]);
92  }
93  return map;
94}