importdata undefined within googlescript

Question

I have a Google Sheet where I am tracking various asset prices including crypto. The easiest way to get crypto prices is by importing from cryptocurrencies.cc:

=importdata("https://cryptoprices.cc/AION/");

However, now my spreadheet is full of references to the cryptoprices.cc website. If it ever goes down or changes domain, I would have to replace the URL everywhere in my sheet, which is quite uncontrolled. So I thought about making a wrapper function:

function cryptofinance(token) {
   return importdata("https://cryptoprices.cc/"+ token);
}

This returns an #Error! however: ReferenceError: importdata is not defined (line 94). I am assuming that you cannot call Google Sheet functions outside of the context of the Sheets cells, correct?

If not, what is the proper way to call importdata from within this function? If yes, then what is the best alternative to retrieve the same information using javascript?

Vishal choudhary · Accepted Answer · 2022-05-23 09:49:22Z

2

You cant use importdata like a formula at this place. first you need to get cell refernce then you need to put formula inside the setvalue() function;

var currentCell = SpreadsheetApp.getCurrentCell(); // Select cell here 
currentCell.setValue('=importdata("https://cryptoprices.cc/"+ token)');

for select a range of cells you can use getRange() function

SpreadsheetApp.getRange(row,col).setValue('=importdata("https://cryptoprices.cc/AION/")');

change row, col with cells range.

edited May 23, 2022 at 9:49

answered May 23, 2022 at 9:43

Vishal choudhary

3154 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pr0no Over a year ago

This gives Exception: You do not have permission to perform that action. (line 94). Line 94 is var currentCell = ... Any thoughts?

Vishal choudhary Over a year ago

you need to select the cell reference in currentCell like to access first cell of the current active sheet you can use var firstCell= SpreadsheetApp.getActiveSheet().getActiveSelection().getCell(1,1); and then you can setValue to firstcell

Collectives™ on Stack Overflow

importdata undefined within googlescript

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related