CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetserror-handlingcoinmarketcap
Austin Mourot
Austin Mourot

Reputation: 3

Google Sheets - Copied sheet and script but encountered error on new sheet?

I recently created a crypto portfolio sheet that pulls data from CMC API to give real time prices. I thought I did such a good job that I would create a sample sheet and share the link with people so they could create their own API KEY and make their own portfolio...

However when I tested it out, by copying the sheet, adding API, my script is giving an error. Even though it still works perfectly fine on the previous sheet?

This is the error I am getting:

Error
TypeError: Cannot read properties of null (reading 'getRange') coin_price @ Code.gs:17

It seems to fail to read random functions, as previously I was getting error:

Error
TypeError: Cannot read properties of null (reading 'quote') coin_price @ Code.gs:31

Here is my code:

function coin_price() { const myGoogleSheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Portfolio'); 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': 'MY-API-KEY' }, // Replace 'insert api' with your API key json: true, gzip: true, }

let myCoinSymbols = []; const getValues = myGoogleSheetName.getRange('A6:A').getValues(); // Start reading symbols from A6 const numRows = getValues.filter(symbol => symbol[0] !== "").length; // Get the number of non-empty cells in column A const coinSymbols = getValues.slice(0, numRows);

// Let's iterate for (let i = 0; i < coinSymbols.length; i++) { const ticker = coinSymbols[i][0]; const coinMarketCapUrl = https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${ticker}; const result = UrlFetchApp.fetch(coinMarketCapUrl, coinMarketCapAPICall); const txt = result.getContentText(); const d = JSON.parse(txt); const row = i + 6; // Start inputting prices from row 6

// Puts a column of current market prices in dollars into the sheet at B6 and onwards.
myGoogleSheetName.getRange(row, 2).setValue(d.data[ticker].quote.USD.price);

} }

I tried rewriting script, nothing, I tried changing the name of the sheet hoping it would somehow reboot everything, nothing I tried adding CMC API using extension API Connector and it worked, but I have custom functions in my script that still aren't working (i.e. its not the API that's the problem) I tried adjusting the range of where it would read symbols, nothing

Upvotes: 0

Views: 112

Answers (1)

Cooper
Cooper

Reputation: 64100

I tried cleaning you code up a bit but I did not wish to paste it into you code because I found it so disorganized that I wasn't sure I was doing it justice.

function coin_price() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Portfolio');
  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': 'MY-API-KEY' },
    json: true, gzip: true
  }
  const coinMarketCapUrl = https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${ticker}; 
  const vs = sh.getRange('A6:A' + sh.getLastRow()).getValues().flat();  
  const coinSymbols = vs.slice(0, vs.length);
  for (let i = 0; i < coinSymbols.length; i++) {
    let ticker = coinSymbols[i];
    let result = UrlFetchApp.fetch(coinMarketCapUrl, coinMarketCapAPICall);
    let txt = result.getContentText();
    let d = JSON.parse(txt);
    let row = i + 6; 
    sh.getRange(row, 2).setValue(d.data[ticker].quote.USD.price);
  }
}

Upvotes: 0

Related Questions