micex_updater.gs

var SOURCE_TAB_NAME = "Портфель";
var TIKER_RANGE = "A8:B120";
var MICEX_RATES_PAGE_NAME = "MicexRateTickers";
var MICEX_INDEX_PAGE_NAME = "IMOEX";

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('MICEX Updater')
      .addItem('Update Rates', 'updateRates')
      .addItem('Update Index', 'updateMecixIndex')
      .addItem('Add row', 'addCopyLastRow')
      .addToUi();
}


function objectToArray(obj) {
  var result = [];
  for(var key in obj) {
    result.push(obj[key]);
  }
  
  return result;
}

function addCopyLastRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRowIndex = sheet.getLastRow();
  var lastColumnIndex = sheet.getLastColumn();
  var sourceRange = sheet.getRange(lastRowIndex, 1, 1, lastColumnIndex);
  var destination = sheet.getRange(lastRowIndex, 1, 2, lastColumnIndex);
  sourceRange.autoFill(destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  var range = sheet.getRange(sheet.getLastRow(), 1, 1, 9);
  var emptyRow = new Array(9);
  for(var i = 0; i < emptyRow.length; i++) {
    emptyRow[i] = null;
  }
  emptyRow[0] = new Date();
  var emptyRangeValues = [emptyRow];
  range.setValues(emptyRangeValues);
  
}

function updateRates() {
  var data = collectExtendingTickers();
  var tickerPrices = [];

  if (data.stocks.length > 0) {
    tickerPrices = tickerPrices.concat(
      MicexAPI.getPriceByTickers(data.stocks, MicexAPI.BOARD_ID.STOCK)
    );
  }
  
  if (data.bonds.length > 0) {
    var bondData = MicexAPI.getPriceByISINs(data.bonds);
    
    var reducedData = bondData.map(function(item) {
      return {
        ticker: item.ticker,
        price: item.price * item.lot / 100
      };
    });
    
    tickerPrices = tickerPrices.concat(reducedData);
  }

  if (data.etfs.length > 0) {
    tickerPrices = tickerPrices.concat(
      MicexAPI.getPriceByTickers(data.etfs, MicexAPI.BOARD_ID.ETF, false)
    );
  }
  saveRates(tickerPrices);
}

function collectExtendingTickers() {
  var tablePage = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SOURCE_TAB_NAME);
  var range = tablePage.getRange(TIKER_RANGE);  
  
  var stocks = [];
  var bonds = [];
  var etfs = [];
  
  var data = range.getValues();
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var ticker = row[0].toString().trim();
    var type = row[1].toString().trim();
    if (ticker) {
      switch(type) {
        case 'Акции': stocks.push(ticker); break;
        case 'Облигации': bonds.push(ticker); break;
        case 'ПИФ': etfs.push(ticker); break;
        default: stocks.push(ticker);
      }
    }
  }
  
  return {
    stocks: stocks,
    bonds: bonds,
    etfs: etfs
  };
}

function saveRates(tickers) {
  var tablePage = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MICEX_RATES_PAGE_NAME);
  var tableContent = tickers.map(function(ticker) {
    return objectToArray(ticker);
  });
  var range = tablePage.getRange("A2:R300");
  range.clear({contentsOnly: true});
  range = tablePage.getRange(2, 1, tableContent.length, tableContent[0].length);
  range.setValues(tableContent);
}

function getIndexData(indexTicker) {
  var indexTickers = MicexAPI.getIndex(indexTicker);
  var map = indexTickers.reduce(function(acc, meta) {
    acc[meta.ticker] = meta;
    return acc;
  }, {});
  const tickersMeta = MicexAPI.getPriceByTickers(Object.keys(map), MicexAPI.BOARD_ID.STOCK, false);
  var mergedMeta = tickersMeta.reduce(function(acc, meta) {
    acc[meta.ticker] = Object.assign({}, acc[meta.ticker], meta);
    return acc;
  }, map);
  
  return mergedMeta;
}

function updateMecixIndex(force) {
  var cache = CacheService.getScriptCache();
  
  var indexData = cache.get('IMOEX');
  if (indexData === null || force) {
    indexData = getIndexData('IMOEX');
    cache.put('IMOEX', indexData, 1);
  }
  
  var tableContent = [];
  for(var key in indexData) {
    tableContent.push(objectToArray(indexData[key]));
  }

  var tablePage = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MICEX_INDEX_PAGE_NAME);
  
  var range = tablePage.getRange("A2:R300");
  range.clear({contentsOnly: true});
  range = tablePage.getRange(2, 1, tableContent.length, tableContent[0].length);
  range.setValues(tableContent);
  range.sort({column: 2, ascending: false});
}

function test() {
  updateRates(true);
}