Question 297807 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Adding a cell value to another cell then deleting the original cell value

I have sheet that i’m using to track servicings on a machine.

I want to have a cell that I can put in hours the machine was used for. That value would then be added to another cell tracking total hours used and subtracted from another cell tracking when the next service is due. Finally I want the original cell to be deleted.

In the picture below I would enter hours used in cell D2. This value would be added to cell B2 and subtracted from cell C2 before clearing itself.

enter image description here

simplified Example of spreadsheet

Hope someone can help, many thanks.

I’ve tried adapting scripts i’ve seen on here, but I have almost no coding skill so have had no success

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks for your help,

    I ended up with this script working for me

    function onEdit(){
  var ttis = SpreadsheetApp.getActiveSheet().getRange("B2");
  var service = SpreadsheetApp.getActiveSheet().getRange("C2");
  var value1 = SpreadsheetApp.getActiveSheet().getRange("B2").getValue();
  var value2 = SpreadsheetApp.getActiveSheet().getRange("D2").getValue();
  var value3 = SpreadsheetApp.getActiveSheet().getRange("C2").getValue();
  ttis.setValue(value1 + value2);
  service.setValue(value3 - value2);

  var ttis = SpreadsheetApp.getActiveSheet().getRange("B3");
  var service = SpreadsheetApp.getActiveSheet().getRange("C3");
  var value1 = SpreadsheetApp.getActiveSheet().getRange("B3").getValue();
  var value2 = SpreadsheetApp.getActiveSheet().getRange("D3").getValue();
  var value3 = SpreadsheetApp.getActiveSheet().getRange("C3").getValue();
  ttis.setValue(value1 + value2);
  service.setValue(value3 - value2);

  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1')
  sheet.getRange("D2:D3").clearContent()
}

  2. 0

    This should get you started:

    function onEdit(e) {
  if(e.range.getSheet() != "Your Sheet Name" || e.range.columnStart != 4 || e.range.rowStart == 1 || e.value == "")return;
  const sh = e.range.getSheet();
  const [b,c] = sh.getRange(e.range.rowStart, 2,1,2).getValues().flat()
  e.range.offset(0,-1).setValue(c - e.value);
  e.range.offset(0,-2).setValue(b + e.value);
}

    You cannot run these functions without supplying the event object. If you attempt to run from the script editor without supplying the event object you will get an error. The onEdit trigger is a simple trigger meaning that it cannot perform functions that require permission. If you need tp perform commands that require permission you must create an installable trigger either programmatically or through the editors trigger panel.

    There are several limitations running scripts this way. You will have difficulty using alert and prompt boxes. They are fairly difficult to debug because the onEdit is not easily run in debug mode so you are left having to edit the appropriate elements on the desired sheet."

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search