CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetscustom-function
johnjohn1
johnjohn1

Reputation: 55

Active recalculation

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?

Upvotes: 1

Views: 82

Answers (3)

Wicket
Wicket

Reputation: 38346

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

  1. should not use names of built-in functions
  2. 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:

  1. In A1 write 2
  2. In A2 write 2
  3. In A3 write =mySum(A1,A2)
  4. 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.

  1. Change A1 from 2 to 3

A3 and A4 will show 5

  1. Change A2 from 2 to 1

A3 and A4 will show 4

Related

Upvotes: 1

Jacques-Guzel Heron
Jacques-Guzel Heron

Reputation: 2598

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.

Upvotes: 0

Raphael Castro
Raphael Castro

Reputation: 1148

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

Upvotes: 0

Related Questions