Active recalculation

Question

I am trying to figure out how to actively recalculate cell values when using script editor function the same way as googlesheet does.

For example when we have number 2 in Cell A1 and number 2 in A2 then if we do in cell A3 "=A1+A2" and then we dive the result in cell A4, if we change eiher A1 or A2 both A3 and A4 will actively update.

So in script editor if we do:

Function sum_cells(){
 var sheet=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 sheet.getRange("A1").setValue(2);
 sheet.getRange("A2").setValue(2);
 var cell1=sheet.getRange("A1").getValue();
 var cell2=sheet.getRange("A2").getValue();
 sheet.getRange("A3").setValue(cell1+cell2);
 var cell3=sheet.getRange("A3").getValue();
 sheet.getRange("A4").setValue(cell3/4)}

Is it possible to actively update the result in A4 and A3 if the cell values in A1 and A2 change?

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Yes, it's possible by using a custom function. For details please read https://developers.google.com/apps-script/guides/sheets/functions

A very simple custom function that use the JavaScript + operator and two operands look like this:

/**
 *
 * @customfunction
 */
function mySum(a,b){
  return a + b;
}

NOTES:

Custom functions

should not use names of built-in functions
can only return a value of an 2D array of values, they can't change the values of other cells and can't execute methods that requires authorization, like sending an email.

When the spreadsheet be opened and every time that a or b change the custom function will be recalculated.

Also it could be possible by using an on edit simple or installable triggers but I think that using a custom function it's the more appropriate way.

For example when we have number 2 in Cell A1 and number 2 in A2 then if we do in cell A3 "=A1+A2" and then we dive the result in cell A4, if we change eiher A1 or A2 both A3 and A4 will actively update.

To use the above code example to do what was described in above quote from the question, do the following:

In A1 write 2
In A2 write 2
In A3 write =mySum(A1,A2)
In A4 write =A3

A3 and A4 will show 4.

Then when the values entered in A1 or A2 change, the values in A3 and A4 will change.

Change A1 from 2 to 3

A3 and A4 will show 5

Change A2 from 2 to 1

A3 and A4 will show 4

Custom Functions and Recalculation

Would mine posting the whole code? How do you pass the values of the two cells in the function and how do you assing the custom function to a certain cell? I want to do it only by using script editor — johnjohn1, Jan 14 '20 at 20:24
is it possible to assign a function to a cell through script editor? like sheet.getRange().setValue(mysum(A1,A2) ? It does not work for me.... — johnjohn1, Jan 22 '20 at 20:57

score 0 · Answer 2 · answered Jan 11 '20 at 19:13

if you write a function in your GS file like this:

function onEdit(e) {
//Logic Goes Here
}

it will trigger every time someone edits a cell, more importantly it will give you a reference object that you can use to determine where and when the change took place. use Logger.log(e) inside of the function to see what the change consists of by running the fuction and looking in view>logs

score 0 · Answer 3 · answered Jan 13 '20 at 13:09

After reading your request, I understood the following:

You want to update some cells when other cells are modified.
You request the former in a automatically way.

In that case you can use the following example to fulfill your requests:

CODE

function cellUpdater(e) {
  var sheet = e.source;
  if (e.range.getA1Notation() == "A1" || e.range.getA1Notation() == "A2") {
    var A3 = sheet.getRange("A1").getValue() + sheet.getRange("A2").getValue();
    var A4 = A3 / 4;
    sheet.getRange("A3").setValue(A3);
    sheet.getRange("A4").setValue(A4);
  }
}

BEHAVIOUR

That code will run at every modification in the sheet. If the modification is done in the A1 and A2 cells, it will update A3 and A4 based on your definition.

OBSERVATIONS

You shall create an installable trigger using the OnEdit() type. You can add the installable trigger from the App Script editor, using the menu Edit Current project's triggers. There you must click + Add Trigger and select the following parameters:
- Choose which function to run: cellUpdater
- Choose which deployment should run: Head
- Select event source: From spreadsheet
- Select event type: On edit
- Failure notification settings: as you wish
- You can find more information about this process on the managing triggers manually documentation
This function will work in every sheet of the spreadsheet.
Due to triggers limitation, this function won't activate if the modification is done through scripts.

ALLUSIONS

Please take this as one of the possible solutions to your issue, and don't hesitate to write me back with any additional doubts or requests to further clarifications.

Active recalculation

3 Answers3

CODE

BEHAVIOUR

OBSERVATIONS

ALLUSIONS