How do you reference a column of data for input in a google sheets script editor?

Viewed 68

I found a script to download prices from coin market cap, but in the script you have to individually enter the name of the coin in the script. Since I may want to change the names often, it seems it would be easier to "fetch" the names from a list in a column. In the following code, you enter each name in line 21. Is there any way to edit the code to whatever names are listed in B2:B10 and have those names inputted in the script? so I can just edit column B instead of the script?

Thanks in advance

enter codefunction coin_price() {
const myGoogleSheetName =
  // Change "Sheet1" to the name of your sheet where you want to run this.
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')

// Call CoinMarketCap and let them know who you are.
const coinMarketCapAPICall = {
  method: 'GET',
  uri: 'https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest',
  qs: {
    start: '1',
    limit: '5000',
    convert: 'USD',
  },
  headers: { 'X-CMC_PRO_API_KEY': 'YOUR_API_KEY_GOES_HERE' },
  json: true,
  gzip: true,
}

// Put the coin symbols that you want to follow here.
const myCoinSymbols = ['BTC', 'ETH', 'FIL', 'BTT', 'AMP', 'DOT']

// Let's itereate 
for (let i = 0; i < myCoinSymbols.length; i++) {
  const coinSymbol = myCoinSymbols[i]
  const coinMarketCapUrl = `https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${coinSymbol}`
  const result = UrlFetchApp.fetch(coinMarketCapUrl, coinMarketCapAPICall)
  const txt = result.getContentText()
  const d = JSON.parse(txt)
  const row = i + 2
  // Puts a column of at symbols into the sheet at B2.
  myGoogleSheetName.getRange(row, 2).setValue(coinSymbol)
  // Puts a column of current market price's in dollars into the sheet at B3.
  myGoogleSheetName
    .getRange(row, 3)
    .setValue(d.data[coinSymbol].quote.USD.price)
1 Answers

You can put your symbols in columns B, from B2 to B7 for instance, and retrieve them by

var myCoinSymbols = myGoogleSheetName.getRange('B2:B7').getValues().join().split(',')

then remove this line myGoogleSheetName.getRange(row, 2).setValue(coinSymbol)

Related