Advertisement
RemcoE33

alphavantage API to sheets

Jul 25th, 2021
160
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.   RemcoE33
  3.   https://www.alphavantage.co/documentation/
  4. */
  5.  
  6.  
  7. function onOpen(e) {
  8.   SpreadsheetApp.getUi().createMenu('AlphaVantage')
  9.     .addItem('GetAlphavantage from selected ticker(s)', 'alphavantage')
  10.     .addItem('Get specific value from selected ticker(s)', 'alphavantageSingle')
  11.     .addItem('Set column headers from active cell', 'columnheaders')
  12.     .addItem('Set API key', 'setToken')
  13.     .addToUi();
  14. }
  15.  
  16. function alphavantage() {
  17.   const ss = SpreadsheetApp.getActiveSpreadsheet();
  18.   const sheet = ss.getActiveSheet();
  19.   const activeRange = sheet.getActiveRange();
  20.   const tickers = activeRange.getValues().flat();
  21.   const env = PropertiesService.getScriptProperties().getProperty('TOKEN');
  22.   const output = [];
  23.  
  24.   let functionType;
  25.   try {
  26.     functionType = prompt();
  27.   } catch (err) {
  28.     return;
  29.   }
  30.  
  31.   tickers.forEach(tic => {
  32.     const url = `https://www.alphavantage.co/query?function=${functionType}&symbol=${tic}&apikey=${env}`;
  33.     const response = UrlFetchApp.fetch(url);
  34.     const json = JSON.parse(response.getContentText());
  35.     const values = Object.values(json)
  36.     values.shift();
  37.     output.push(values);
  38.   })
  39.  
  40.   sheet.getRange(activeRange.getRow(), activeRange.getColumn() + 1, output.length, output[0].length).setValues(output);
  41.  
  42. }
  43.  
  44. function alphavantageSingle(){
  45.   const ss = SpreadsheetApp.getActiveSpreadsheet();
  46.   const sheet = ss.getActiveSheet();
  47.   const activeRange = sheet.getActiveRange();
  48.   const tickers = activeRange.getValues().flat();
  49.   const env = PropertiesService.getScriptProperties().getProperty('TOKEN');
  50.   const output = [];
  51.  
  52.   let functionType;
  53.   let key;
  54.   try {
  55.     functionType = promptType();
  56.     key = promptKey(functionType);
  57.   } catch (err) {
  58.     return;
  59.   }
  60.  
  61.   tickers.forEach(tic => {
  62.     const url = `https://www.alphavantage.co/query?function=${functionType}&symbol=${tic}&apikey=${env}`;
  63.     const response = UrlFetchApp.fetch(url);
  64.     const json = JSON.parse(response.getContentText());
  65.     output.push([json[key]]);
  66.   })
  67.  
  68.   sheet.getRange(activeRange.getRow(), activeRange.getColumn() + 1, output.length, output[0].length).setValues(output);
  69. }
  70.  
  71. function columnheaders() {
  72.   const ss = SpreadsheetApp.getActiveSpreadsheet();
  73.   const sheet = ss.getActiveSheet();
  74.   const cell = sheet.getActiveCell();
  75.   let functionType;
  76.   try {
  77.     functionType = promptType();
  78.   } catch (err) {
  79.     return;
  80.   }
  81.   const url = `https://www.alphavantage.co/query?function=${functionType}&symbol=IBM&apikey=demo`;
  82.   const response = UrlFetchApp.fetch(url);
  83.   const headers = Object.keys(JSON.parse(response.getContentText()));
  84.   const range = sheet.getRange(cell.getRow(), cell.getColumn(), 1, headers.length);
  85.   range.setValues([headers]);
  86.   range.setFontWeight('bold')
  87. }
  88.  
  89. function promptType() {
  90.   const choices = ['OVERVIEW', 'EARNINGS', 'INCOME_STATEMENT', 'BALANCE_SHEET', 'CASH_FLOW', 'LISTING_STATUS', 'EARNINGS_CALENDAR', 'IPO_CALENDAR'];
  91.   const ui = SpreadsheetApp.getUi();
  92.   const type = ui.prompt(`Choose:  ${choices.join(' | ')}`).getResponseText();
  93.   if (choices.includes(type)) {
  94.     return type;
  95.   } else {
  96.     ui.alert('Input type does not match one of the choises')
  97.     return new Error('No match')
  98.   }
  99. }
  100.  
  101. function promptKey(type) {
  102.   const url = `https://www.alphavantage.co/query?function=${type}&symbol=IBM&apikey=demo`;
  103.   const response = UrlFetchApp.fetch(url);
  104.   const headers = Object.keys(JSON.parse(response.getContentText()));
  105.   const ui = SpreadsheetApp.getUi()
  106.   const key = ui.prompt(`Choose:  ${headers.join(' | ')}`).getResponseText();
  107.   if (headers.includes(key)) {
  108.     return key;
  109.   } else {
  110.     ui.alert('Input type does not match one of the choises')
  111.     return new Error('No match')
  112.   }
  113. }
  114.  
  115. function setToken() {
  116.   const ui = SpreadsheetApp.getUi();
  117.   const response = ui.prompt('Set token');
  118.   PropertiesService.getScriptProperties().setProperty('TOKEN', response.getResponseText());
  119. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement