· 4 years ago · Jul 25, 2021, 07:32 PM
1/*
2 RemcoE33
3 https://www.alphavantage.co/documentation/
4*/
5
6
7function 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
16function 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
44function 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
71function 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
89function 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
101function 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
115function setToken() {
116 const ui = SpreadsheetApp.getUi();
117 const response = ui.prompt('Set token');
118 PropertiesService.getScriptProperties().setProperty('TOKEN', response.getResponseText());
119}