Update custom function in different cells

0
votes

I have a spreadsheet with a custom function calling a web API like this:

function myFunction(value) {

  var value = value
  ...

}

and I call this function in different cells in B column in this way:

=myFunction(A2)
=myFunction(A3)
=myFunction(A4)
...

so that the values change regarding the content of A column.

Now, I would like to update all these functions with a trigger, which could be every minute, or every hour, or at midnight. I used the built in trigger on Google Apps Script interface, like I did in the past with external scripts, but it doesn't work (I think because the trigger call the function without the "value" variable). I was thinking to add an external triggered script that update the value of another cell (let's suppose "C1"), and then use the onEdit function to update the custom functions. I searched a lot about onEdit, but I really didn't understand how to make it works in my case. Should the onEdit function recall myFunction? In what way?

Any help is appreciated, thank you

2
google-apps-scriptgoogle-sheets
This doesn't really work with custom functions; they don't recompute as their input does not change and they basically assume they are deterministic. You can have them take a dummy argument which is a cell with a random number your scheduled trigger fills in which will then force the custom function to recalculate. - Robin Gertenbach
Ok, thanks. I changed the function this way: function myFunction(value, now) { var value = value var now = data.getRange('a1').getValue(); //a cell with =now() formula ... } should I call the "now" argument in every cell containing the custom function? - user4737227

2 Answers

0
votes

You can use this function for refreshing formulas in your column:

function updateColumnBFormulas()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('test');
  var range = sheet.getRange(1, 2, sheet.getLastRow(), 1);

  // get all formulas
  var formulas = range.getFormulas();

  for (var i = 0; i < formulas.length; i++)
  {
    // if there's formula in a cell
    if (formulas[i][0])
    {
      // refresh it
      sheet.getRange(i+1, 2).setFormula(formulas[i][0]);
    }
  }
}

Then set up trigger to run it every minute (Edit - Current project's trigger):

enter image description here

0
votes

Ok, I solved with an external script that updates a cell, and then a dummy argument in my custon function that refers to that cell (as suggested by @RobinGertenbach) This is the external triggered script:

function update() {

  var number = Math.floor(new Date().getTime()/1000);
  ss.getSheetByName("Data").getRange("A1").setValue(number);

}

and my custom function now looks like:

function myFunction(value, now) {

   var value = value 
   var now = now
   ...

 }

and then it is called in column B in this way:

=myFunction(A2, $A$1)
=myFunction(A3, $A$1)
=myFunction(A4, $A$1)
...

The solution with a function for refreshing formulas posted above was not doing the job, I don't know why. Formulas were refreshing but the values didn't get updated. Thank you very much for your help!