Search code examples
javascriptgoogle-apps-scriptgoogle-sheetscustom-function

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?

Solution

  • 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