CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscustom-function
Cimoe
Cimoe

Reputation: 586

Call custom function in google sheets only once and save value to cell

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.

Upvotes: 0

Views: 2093

Answers (2)

macorreag
macorreag

Reputation: 372

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.

Upvotes: 0

Wicket
Wicket

Reputation: 38296

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.

Upvotes: 1

Related Questions