X5P4vBaX

1// Get one or more API keys for free from https://iexcloud.io/cloud-login#/register/
2// and put them in the `keys` array here, in quotes, and separated by commas
3
4const keys = [
5  'api_key_1',
6  'api_key_2',
7  'api_key_3',
8  '... as many as you wany',
9]
10
11// Gets a random key from the `keys` array above
12const getToken = () => keys[Math.floor(Math.random() * keys.length)]
13
14// iexcloud.io allow a maximum of 100 tickers per request. So we need to break the
15// array of ticker symbols up into chunks of 100, and then batch the requests.
16const chunks = array => {
17  return array.reduce((resultArray, item, index) => {
18    const chunkIndex = Math.floor(index / 99)
19    if (!resultArray[chunkIndex]) {
20      resultArray[chunkIndex] = []
21    }
22    resultArray[chunkIndex].push(item)
23    return resultArray
24  }, [])
25}
26
27// This is the main function which gets triggered when you change a cells in column B
28function getData(cells) {
29  const sheet = SpreadsheetApp.getActiveSheet()
30
31  // We only want to run this function on the 'Watch List' sheet, so
32  // change this 'Watch List' string if you change the name of the sheet
33  if (sheet.getName() === 'Watch List') {
34    let rowStart = cells.range.rowStart
35    
36    // Don't do anything if we edit row 1 becuase that's the header row.
37    if (rowStart == 1) {
38      return
39    }
40      
41    const rowEnd = cells.range.rowEnd
42
43    // Get reference to the edited 
44    const bColRange = sheet.getRange(`B${rowStart}:B${rowEnd}`)
45      
46    // If all the cells in column B are empty, then we can clear the spreadsheet in that row (becuase we've deleted the tickers in that range) 
47    if (
48      (rowStart == rowEnd && !cells.value) ||
49      bColRange.isBlank()
50    ) {
51      const range = sheet.getRange(`${rowStart}:${rowEnd}`)
52      range.clear()
53    } else {
54      const colBValues = sheet.getRange('B:B').getValues().flat()
55      const values = bColRange.getValues()
56      const allTickers = values.flat()
57      const tickers2d = chunks(allTickers)
58      sheet
59        .getRange(`C${rowStart}:C${rowEnd}`)
60        .setValue(
61          `=IF(ISBLANK(B${rowStart}),,GOOGLEFINANCE(B${rowStart}, "price"))`
62        )
63      let tickerString = ''
64      tickers2d.map(tickers => {
65        tickerString = ''
66        tickers.forEach(ticker => {
67          if (ticker) {
68            if (tickerString === '') {
69              tickerString += `${ticker}`
70            } else {
71              tickerString += ',' + ticker
72            }
73          }
74        })
75
76        // Get out random token
77        const token = getToken()
78
79        try {
80          let url = `https://cloud.iexapis.com/stable/stock/market/batch?symbols=${tickerString}&types=company,stats&range=next&token=${token}`
81          let response = UrlFetchApp.fetch(url)
82          let data = JSON.parse(response.getContentText())
83          Object.entries(data).forEach(([ticker, info]) => {
84            let row = colBValues.indexOf(ticker) + 1
85            Logger.log('row')
86            Logger.log(row)
87            if (row > 0) {
88              sheet.getRange(`A${row}`).setValue(info.stats.companyName)
89              sheet.getRange(`D${row}`).setValue(`=IF(ISBLANK(H${row})+ISBLANK(J${row}),,H${row}/J${row})`)
90              sheet.getRange(`E${row}`).setValue(info.stats.peRatio)
91              sheet.getRange(`F${row}`).setValue(info.stats.week52high)
92              sheet.getRange(`G${row}`).setValue(info.stats.week52low)
93              sheet.getRange(`H${row}`).setValue(info.stats.ttmDividendRate)            
94              sheet.getRange(`I${row}`).setValue(info.stats.dividendYield)
95              sheet.getRange(`J${row}`).setValue(info.stats.ttmEPS)
96              sheet.getRange(`K${row}`).setValue(info.stats.exDividendDate)
97              sheet.getRange(`L${row}`).setValue(info.stats.nextDividendDate)
98              sheet.getRange(`M${row}`).setValue(info.stats.nextEarningsDate)
99              sheet.getRange(`N${row}`).setFormula(`=HYPERLINK("https://seekingalpha.com/symbol/${ticker}/dividends/history","Seeking Alpha")`)
100              sheet.getRange(`O${row}`).setValue(info.company.sector)
101            } else {
102              sheet.getRange(`A${row}`).setValue("IEX Doesn't know this ticker")
103              sheet.getRange(`E${row}`).setValue('')
104              sheet.getRange(`F${row}`).setValue('')
105              sheet.getRange(`G${row}`).setValue('')
106              sheet.getRange(`I${row}`).setValue('')
107              sheet.getRange(`J${row}`).setValue('')
108              sheet.getRange(`K${row}`).setValue('')
109              sheet.getRange(`L${row}`).setValue('')
110              sheet.getRange(`M${row}`).setValue('')
111              sheet.getRange(`N${row}`).setValue('')
112              sheet.getRange(`O${row}`).setValue('')
113            }
114          })
115        } catch (error) {
116          Logger.log(`Error getting data with token ${token}`)
117          Logger.log(error)
118        }
119      })
120    }
121  }
122}
123
124// The filter for the second sheet:
125// =filter('Watch List'!A2:O, 'Watch List'!I2:I > A2, 'Watch List'!D2:D > A4, 'Watch List'!D2:D < A6, 'Watch List'!E2:E < A8, 'Watch List'!J2:J > A10)