Google spreadsheets script editor: onEdit setValue or clearContent

Question

Im trying to make a spreadsheet to manage my money/expenses and have run into a problem trying to automate my process

I want to make a piece of code that runs every time a cell has been edited in the sheet.

When triggered, i want it to calculate ssum, lsum and betal(in the loop), and then put it into 3 different cells. The code behaves as expected, but the onedit trigger doesnt work.

This is my code:

function regnudbetalprocent() {

  var betal = 0;
  var i = 1;
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var sum = activeSheet.getRange(18, 5).getValue();
  var ssum;
  var lsum;
  var ssumori = activeSheet.getRange(3, 8).getValue();
  var lsumori = activeSheet.getRange(4, 8).getValue();
  var fuld = activeSheet.getRange(18, 2).getValue();

  while(betal < sum){

    ssum = ((ssumori - fuld / 2) / 100) * i;
    lsum = ((lsumori - fuld / 2) / 100) * i;
    betal = ssum + lsum;

    i++;
  }


    if (betal > sum) {
      var output = [
    [ssum,lsum],
    ["Samlet",betal]
  ]
       return output;
    }
}

The output variable sets the neighbouring cells accordingly from where the function is called

I've tried with setValue and clearContent, but it i cant edit outside the cell from where the function is called. I've used Edit -> current project's triggers to add an onEdit trigger, which increments each time i edit the sheet, but nothing happens.. I'm burned out

Can someone guide me? how do i get what i want?

I cannot understand about I've tried with setValue and clearContent, but it i cant edit outside the cell from where the function is called.. Can I ask you where you want to put the result value, when a cell is edited? — Tanaike
@Tanaike Thanks for your reply. I want to put 3 values, one in D21, E21 and E22 as seen in this screenshot gyazo.com/20b79f68eee379fa2d471860cf45f6b1 — Vegapunk
@Tanaike This is the message i get when i try to use setValue on cells "Exception: You do not have permission to call setValue (line 7)." — Vegapunk
Thank you for replying. From your replying, I proposed a modified script as an answer. Could you please confirm it? If I misunderstood your question and that was not the direction you want, I apologize. — Tanaike

Tanaike Tanaike · Accepted Answer · 2020-03-23T09:15:12

The values for using with the calculation are the constant cells of "B18", "E18", "H3" and "H4".
You want to run the script when one of above cells is edited. You want to use OnEdit event trigger.
You want to put the result value to the cells of "D21", "E21" and "E22".
Your calculation has no issues.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

In your script, I think that the simple trigger can be used instead of the installable trigger.
From your replying, about "Exception: You do not have permission to call setValue (line 7).", I think that you might be using the function of regnudbetalprocent() as the custom function. In this case, such error occurs.
You can know the coordinate of the edited cell using the event object.

When above points are reflected to your script, it becomes as follows.

Modified script:

The function of onEdit is used as the simple trigger. So in order to run the script, please manually edit one of cells "B18", "E18", "H3" and "H4". By this, the script is run and retrieved values from the cells of "B18", "E18", "H3" and "H4", and the calculated result is put to the cells of "D21:E22".

function onEdit(e) {
  var a1Notation = e.range.getA1Notation();  // Added
  if (a1Notation != "B18" && a1Notation != "E18" && a1Notation != "H3" && a1Notation != "H4") return;  // Added

  var betal = 0;
  var i = 1;
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var sum = activeSheet.getRange(18, 5).getValue();
  var ssum;
  var lsum;
  var ssumori = activeSheet.getRange(3, 8).getValue();
  var lsumori = activeSheet.getRange(4, 8).getValue();
  var fuld = activeSheet.getRange(18, 2).getValue();
  while(betal < sum){
    ssum = ((ssumori - fuld / 2) / 100) * i;
    lsum = ((lsumori - fuld / 2) / 100) * i;
    betal = ssum + lsum;
    i++;
  }

  if (betal > sum) {  // Modified
    var output = [[ssum,lsum], ["Samlet",betal]];
    var r = e.range.getSheet().getRange("D21:E22");
    r.clearContent();  // This line might not be required.
    r.setValues(output);
    SpreadsheetApp.flush();  // This line might not be required.
  }
}

Google spreadsheets script editor: onEdit setValue or clearContent

1 Answers

Modification points:

Modified script:

References: