Skip to content
Advertisement

Wrapper around importdata() in Google Sheets

I am trying to write a wrapper around Google Sheets’ importdata() in code.gs:

100 
101  function cryptofinance(token) {
102     var currentCell = SpreadsheetApp.getCurrentCell();
103     currentCell.setValue('=importdata("https://cryptoprices.cc/"+ token)');
104  }
105
106 ...

The idea is to call the function from any sheet in the spreadsheet to get the price of any crypto currency token:

    |          A
+---+-------------------------+
| 1 |  =cryptofinance("aion") |
+---+-------------------------+

I get an error though when I try this:

    |          A
+---+-------------------------+
| 1 |  #ERROR                 |
+---+-------------------------+

#ERROR Exception: You do not have permission to perform that action. (line 102).

Why is this happening and how can I solve it?

Answer

Custom functions can be used to return values, period.

Custom function can’t be used to execute any method that require authorization to run and can’t modify attributes of the cell / range that contain the formula using the custom function. Ref. https://developers.google.com/apps-script/guides/sheets/functions

You might use a UrlFetchApp service in a custom function to call external services but you still have the restrictions of custom function i.e. 30 seconds execution time limit.

Related

Advertisement