Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- RemcoE33
- https://www.alphavantage.co/documentation/
- */
- function onOpen(e) {
- SpreadsheetApp.getUi().createMenu('AlphaVantage')
- .addItem('GetAlphavantage from selected ticker(s)', 'alphavantage')
- .addItem('Get specific value from selected ticker(s)', 'alphavantageSingle')
- .addItem('Set column headers from active cell', 'columnheaders')
- .addItem('Set API key', 'setToken')
- .addToUi();
- }
- function alphavantage() {
- const ss = SpreadsheetApp.getActiveSpreadsheet();
- const sheet = ss.getActiveSheet();
- const activeRange = sheet.getActiveRange();
- const tickers = activeRange.getValues().flat();
- const env = PropertiesService.getScriptProperties().getProperty('TOKEN');
- const output = [];
- let functionType;
- try {
- functionType = prompt();
- } catch (err) {
- return;
- }
- tickers.forEach(tic => {
- const url = `https://www.alphavantage.co/query?function=${functionType}&symbol=${tic}&apikey=${env}`;
- const response = UrlFetchApp.fetch(url);
- const json = JSON.parse(response.getContentText());
- const values = Object.values(json)
- values.shift();
- output.push(values);
- })
- sheet.getRange(activeRange.getRow(), activeRange.getColumn() + 1, output.length, output[0].length).setValues(output);
- }
- function alphavantageSingle(){
- const ss = SpreadsheetApp.getActiveSpreadsheet();
- const sheet = ss.getActiveSheet();
- const activeRange = sheet.getActiveRange();
- const tickers = activeRange.getValues().flat();
- const env = PropertiesService.getScriptProperties().getProperty('TOKEN');
- const output = [];
- let functionType;
- let key;
- try {
- functionType = promptType();
- key = promptKey(functionType);
- } catch (err) {
- return;
- }
- tickers.forEach(tic => {
- const url = `https://www.alphavantage.co/query?function=${functionType}&symbol=${tic}&apikey=${env}`;
- const response = UrlFetchApp.fetch(url);
- const json = JSON.parse(response.getContentText());
- output.push([json[key]]);
- })
- sheet.getRange(activeRange.getRow(), activeRange.getColumn() + 1, output.length, output[0].length).setValues(output);
- }
- function columnheaders() {
- const ss = SpreadsheetApp.getActiveSpreadsheet();
- const sheet = ss.getActiveSheet();
- const cell = sheet.getActiveCell();
- let functionType;
- try {
- functionType = promptType();
- } catch (err) {
- return;
- }
- const url = `https://www.alphavantage.co/query?function=${functionType}&symbol=IBM&apikey=demo`;
- const response = UrlFetchApp.fetch(url);
- const headers = Object.keys(JSON.parse(response.getContentText()));
- const range = sheet.getRange(cell.getRow(), cell.getColumn(), 1, headers.length);
- range.setValues([headers]);
- range.setFontWeight('bold')
- }
- function promptType() {
- const choices = ['OVERVIEW', 'EARNINGS', 'INCOME_STATEMENT', 'BALANCE_SHEET', 'CASH_FLOW', 'LISTING_STATUS', 'EARNINGS_CALENDAR', 'IPO_CALENDAR'];
- const ui = SpreadsheetApp.getUi();
- const type = ui.prompt(`Choose: ${choices.join(' | ')}`).getResponseText();
- if (choices.includes(type)) {
- return type;
- } else {
- ui.alert('Input type does not match one of the choises')
- return new Error('No match')
- }
- }
- function promptKey(type) {
- const url = `https://www.alphavantage.co/query?function=${type}&symbol=IBM&apikey=demo`;
- const response = UrlFetchApp.fetch(url);
- const headers = Object.keys(JSON.parse(response.getContentText()));
- const ui = SpreadsheetApp.getUi()
- const key = ui.prompt(`Choose: ${headers.join(' | ')}`).getResponseText();
- if (headers.includes(key)) {
- return key;
- } else {
- ui.alert('Input type does not match one of the choises')
- return new Error('No match')
- }
- }
- function setToken() {
- const ui = SpreadsheetApp.getUi();
- const response = ui.prompt('Set token');
- PropertiesService.getScriptProperties().setProperty('TOKEN', response.getResponseText());
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement