ELaC9AY9

1/*
2  Created by RemcoE33
3  Docs: https://fmpcloud.io/documentation
4  Apps script qoutas: https://developers.google.com/apps-script/guides/services/quotas
5*/
6
7function onOpen(e) {
8  SpreadsheetApp.getUi().createMenu('fmpcloud')
9    .addItem('Set API key', "storeAPIkey")
10    .addItem('Run all tickers', 'getAllData')
11    .addToUi();
12}
13
14function storeAPIkey() {
15  const key = SpreadsheetApp.getUi().prompt('Enter API key:').getResponseText();
16  ScriptProperties.setProperty('apikey', key);
17}
18
19function getAllData() {
20  console.time('Timer');
21  const ss = SpreadsheetApp.getActiveSpreadsheet();
22  const tickersSheet = ss.getSheetByName('Tickers')
23  const tickers = tickersSheet.getRange(2, 1, tickersSheet.getLastRow() - 1).getValues().flat();
24  const apikey = ScriptProperties.getProperty('apikey');
25
26  const fmpcloud = {
27    Balance_Y: `https://fmpcloud.io/api/v3/balance-sheet-statement/###?limit=120&apikey=${apikey}`,
28    Balance_Q: `https://fmpcloud.io/api/v3/balance-sheet-statement/###?period=quarter&limit=400&apikey=${apikey}`,
29    Income_Q: `https://fmpcloud.io/api/v3/income-statement/###?period=quarter&limit=400&apikey=${apikey}`,
30    Income_Y: `https://fmpcloud.io/api/v3/income-statement/###?limit=120&apikey=${apikey}`,
31    CashFlow_Y: `https://fmpcloud.io/api/v3/cash-flow-statement/###?limit=120&apikey=${apikey}`,
32    CashFlow_Q: `https://fmpcloud.io/api/v3/cash-flow-statement/###?period=quarter&limit=400&apikey=${apikey}`,
33    Ratios: `https://fmpcloud.io/api/v3/ratios/###?limit=40&apikey=${apikey}`,
34    Metrics: `https://fmpcloud.io/api/v3/key-metrics/###?limit=40&apikey=${apikey}`,
35    Press: `https://fmpcloud.io/api/v3/press-releases/###?limit=100&apikey=${apikey}`,
36    News: `https://fmpcloud.io/api/v3/stock_news?tickers=###&limit=100&apikey=${apikey}`,
37    Surprises: `https://fmpcloud.io/api/v3/earnings-surpises/###?apikey=${apikey}`,
38    Transcript: `https://fmpcloud.io/api/v3/earning_call_transcript/###?quarter=3&year=2020&apikey=${apikey}`
39  }
40
41  const urlsAndSheetnames = Object.entries(fmpcloud);
42
43  tickers.forEach(tic => {
44    urlsAndSheetnames.forEach(endpoint => {
45      let [sheetname, url] = endpoint;
46      const tickerUrl = url.replace('###', tic);
47      console.log(`Calling: ${tickerUrl} to ${sheetname}`);
48      handleAPI(sheetname, tickerUrl);
49    })
50  })
51
52  console.timeEnd('Timer');
53
54}
55
56function handleAPI(sheetname, url) {
57  const ss = SpreadsheetApp.getActiveSpreadsheet();
58  const sheet = ss.getSheetByName(sheetname);
59
60  const response = UrlFetchApp.fetch(url);
61  const dataAll = JSON.parse(response.getContentText());
62  const dataRows = dataAll;
63
64  const rowHeaders = Object.keys(dataRows[0]);
65  const rows = [rowHeaders];
66  for (let i = 0; i < dataRows.length; i++) {
67    const rowData = [];
68    for (let j = 0; j < rowHeaders.length; j++) {
69      rowData.push(dataRows[i][rowHeaders[j]]);
70    }
71    rows.push(rowData);
72  }
73
74  sheet.getDataRange().clearContent();
75  sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
76
77}
78
79
80
81