Google Apps Script - How do I create a trigger for a function with a parameter?

Question

I am working on a tracker for my cryptocurrencies. In the following function I fetch data from an API and put in a cell. For instance, when I want to get the value of Bitcoin in USD I put the formula =getCryptodata("bitcoin", "price_usd") in a cell and it returns the current price.

  function getCryptoData(coin, api) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var url = 'https://api.coinmarketcap.com/v1/ticker/' + coin + '/';
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var data = JSON.parse(json);

  return parseFloat(data[0][api]);

  }

I want the price to be updated every minute, so I've set a trigger for the function. All of a sudden there seems to be a problem now. Every time the function is triggered this error shows up (in my mailbox).

TypeError: Cannot read property "(class)@35c554a5" from undefined.

When I run the script from the script editor I get the same problem. Then I came up with the idea to check whether the parameters in the function are defined or not, and if not, to give them a generic value. Google seemed to have a problem with the undefined parameters (although they are defined in the sheet). So I added these lines before the lines I already had in my function.

  if (typeof coin == 'undefined') {
    var coin = "bitcoin";
  }
  if (typeof api == 'undefined') {
    var api = "price_usd";
  } 
  else {

Now the error is gone but the values are not updated when the function is triggered.

What am I missing?

score 0 · Answer 1 · answered Dec 30 '17 at 10:36

So there are a few things going on here:

You are trying to load an active spreadsheet but when you trigger a script, there is no active spreadsheet. (Why are you loading this spreadsheet? You don't seem to be doing anything with it)
Your script returns a value but if your script is triggered from a schedule (so not from another function) the returned value will be returned to nothing. You need to put this value somewhere useful.

A solution to this issue could be to directly put the value from the API in the sheet. To do this the script requires a small change.

Example:

function getCryptoData(coin, api, target) {

  var ss = SpreadsheetApp.openById("YOUR-ID-HERE"); // The script has no active spreadsheet so make sure to acquire the spreadsheet by providing the ID
  var sheet = ss.getSheets()[0];

  var url = 'https://api.coinmarketcap.com/v1/ticker/' + coin + '/';
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var data = JSON.parse(json);

  SpreadsheetApp.getRange(target).setValue(parseFloat(data[0][api])); // Write the returned value directly to the range (e.g. A1) that was specified as an argument

}

So with such a function, you may create another function that you would trigger every one in a while to update your values:

function updateCryptoData() {
  getCryptodata("bitcoin", "price_usd", "B2");
  getCryptodata("bitcoin", "price_usd", "C2");
}

You don't need to put a formula in your sheet anymore if you choose to go with a solution like this one.

Note: this solution is not optimal from a performance point of view. However, I've tried to make as little code changes to your own code to make it work. Ideally you'd only call your API once per coin type.

Alright. Thanks! The loading of the spreadsheet was not necessary indeed, but in your version it is :) So you'd say there is no solution where I can just put the formula that I had in the sheet, without having to specify the target in the script. Because that would be way more efficient of course. Now I have to write a line of code for every single call of the API. I'm quite new to this so maybe I am asking for the impossible. But isn't there a function that returns the cell in which it is called? Something like: `var target = currentCell();` — Sam Simons, Dec 30 '17 at 12:53
What you refer to would be the ActiveCell. However, as you are triggering the function there is no such thing. It would be there if you would make it an onChange/onEdit trigger. If the trigger is time based and not event based, there won't be any references available to an active sheet/range/cell. You could configure the script in such a way to read a range, read the values and then make the api call. To do this you would adjust the `getCryptoData` in such a way that you'd acquire the data from the sheet, loop over every row and then execute the API call for that row. — Casper, Dec 30 '17 at 14:58

score 0 · Answer 2 · answered Dec 30 '17 at 17:21

0

When using fetch you should be aware there are daily limits to how much data can come back from the call. Because you are calling for data every minute, it is well within the realms of possibility you will/have exceeded the daily limit.

answered Dec 30 '17 at 17:21

Jason Allshorn

1,625
1
18
27

score 0 · Answer 3 · answered Jan 04 '18 at 12:18

This will not need to reload the spreadsheet. First you have to create the trigger for specific coin and pricing.

function teste()
{
 trigger_("bitcoin","price_usd","B2");
 trigger_("litecoin","price_usd","B3");
 trigger_("bitcoin","price_usd","B4");
}

Store the data corresponding to the trigger, with reference to the trigger_id.

function trigger_(coin,api,target)
{
 var new_trigger = ScriptApp.newTrigger(getCryptoData).timeBased().everyMinutes(1).create();
 var trigger_id = new_trigger.getUniqueId();
 PropertiesService.getUserProperties().setProperty(trigger_id, coin+","+api+","+target);
}

When the trigger gets fired, it gets the corresponding data(coin,price,target) with which the api is called and the target cell gets updated.

function getCryptoData(event) 
{

  var trig = ScriptApp.getProjectTriggers();
  for(var i =0; i<trig.length; i++)
  {
   if(trig[i].getUniqueId()== event.triggerUid )
   {
    var cryptoData = PropertiesService.getUserProperties().getProperty(event.triggerUid);
    cryptoData = cryptoData.split(",");
    var coin = cryptoData[0];
    var api = cryptoData[1];
    var target = cryptoData[2];
    var ss = SpreadsheetApp.openById("YOUR_SPEADSHEET_ID").getSheets(); // change accordingly
    var sheet = ss[0];  // change accordingly
    var url = 'https://api.coinmarketcap.com/v1/ticker/' + coin + '/';
    var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
    var json = response.getContentText();
    var data = JSON.parse(json);
    Logger.log(parseFloat(data[0][api]));
    ss[0].getRange(target).setValue(parseFloat(data[0][api]));
   }
  }
}

NOTE : Daily limit to run trigger for normal user is 90min/day app script quota

Google Apps Script - How do I create a trigger for a function with a parameter?

3 Answers3