0

I have written a custom google apps script function that is being called by a google sheet. In this function an API is called and the result of this API is being returned so that it gets displayed in the calling cell of the google sheet.

My problem now is that the sheet calls this function everytime I open the document and there are over 80.000 cells with this function.

Is it possible to save the returned value to the cell and don't call the custom function again when an value has been returned? I want the function being called only once per cell, even if I close the document and reopen it. The returned value should be saved until something else is being written into to cell. That would make my sheets document much more usable than the current state.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Cimoe
  • 580
  • 6
  • 21
  • 1
    Why don't you use a regular function and just execute it once via the script editor to fill in the cells? Custom functions are used as formulas in the google sheet. But in your case, you don't want a formula, you want a hardcopy number calculated by a function. So, instead use this function and execute it manually or via a trigger whenever you like. – Marios Sep 07 '20 at 08:50
  • Can you show the script you are using? What are you trying to do? – Jescanellas Sep 07 '20 at 13:49

2 Answers2

1

From the question

Is it possible to save the returned value to the cell and don't call the custom function again when an value has been returned? I want the function being called only once per cell, even if I close the document and reopen it. The returned value should be saved until something else is being written into to cell. That would make my sheets document much more usable than the current state.

By solely using the custom function it's not possible. There isn't a straight forward solution is to achieve this, in order to keep things simple you should look for another way to implement what is being done by the custom funtion.

One option is to use a trigger (including a custom menu / a function assined to a drawing) to check if the cell that should have the value returned by the custom function is empty and in such case fill it with the corresponding value. The best trigger to use will depend on your spreadsheet workflow.

Rubén
  • 34,714
  • 9
  • 70
  • 166
0

As specified by Ruben this is not possible, with a custom function.

In my particular case I have resorted to using an Apps Script function that is triggered by an edit event of the spreadsheet and verifies if the event is in the column where the function that I want to execute only once should be, later replacement its content with the result of calling the API.

function freezeValue(e) {
  var rangeEvent = e.range;

  var col = rangeEvent.getColumnIndex();

   if (col === 2) { #Verify column of event
     var value = rangeEvent.getValue();
      
     /*Call your api*/
     result_api = CALL_API(value)

     rangeEvent.setValue(result_api);
    }
}

Keep in mind that this implementation only works when each of the cells is edited one by one. To do the same with a row or a complete array of elements, you must go through each of the cells and follow the same procedure.

macorreag
  • 320
  • 3
  • 11