Increment cell value onEdit

Question

I am trying to increment a cell in column B, that is on the same row as a cell in column F, when this last cell is edited. This is what I've come up with:

function clicker(e) {
  var ss = e.range.getSheet();
  var ss_name = ss.getName();
  if ((ss_name == "Report 1") && (e.range.getColumn() == 6)) {
    var row = e.range.getRow();
    var value = e.getRange(row, 2).getValue();
    value++;
    e.range.setValue(value);
  }
}

This is the sheet: https://docs.google.com/spreadsheets/d/1AJYVX0xHwdqBbg_8aDbrS1kuOFzWs6dB7x7I-tA6vYw/edit?usp=sharing

Unfortunately, the cell value does not increment, as desired. I think that the error is in the second part of the code, within the second curly brackets, but I can't put my finger on it.

I have followed this and this as references to try and solve my issue.

`e.range.setValue(value);` you try overwrite edited range here, change it to correct range — Kos, Aug 13 '22 at 21:00
I'm pretty confused. These are the changes I made: `function clicker(e) { var ss = e.range.getSheet(); var ss_name = ss.getName(); if ((ss_name == "Report 1") && (e.range.getColumn() == 2)) { var row = e.range.getRow(); var value = e.getRange(row, 2).getValue(); value++; value.setValue(value);` However, it still doesn't work. — Yannis Volos, Aug 13 '22 at 21:11
change `var value = e.getRange(row, 2).getValue();` to `var value = ss.getRange(row, 2).getValue();`; `e.range.setValue(value);` to `ss.getRange(row, 2).setValue(value);` — Kos, Aug 13 '22 at 21:18
The question explicitly specifies that column `F` is _edited_ which to me suggests _manually edited_. It now appears that you want to increment column `B` when your `doGet()` endpoint is called, which means that a simple trigger cannot be used. Please edit the question to describe your actual use case. — doubleunary, Aug 13 '22 at 21:51
@doubleunary Please don't suggest users to edit the post to change the specifications after answers have already been posted. If the user has a new follow up request, kindly suggest them to ask a new question instead. — TheMaster, Aug 13 '22 at 22:18
Roger that. In this question, the specs are _unclear_ regarding what "this last cell is edited" means. The first two answers assume that it means "column F is manually edited" but the asker has made it clear in a comment that that is not the case. They have accepted one of the answers though, so I guess that concludes this episode. — doubleunary, Aug 14 '22 at 05:19

score 1 · Accepted Answer · answered Aug 13 '22 at 21:18

1

Increment B on edit of F

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Report 1" && e.range.columnStart == 6) {
    e.range.offset(0, -4).setValue(e.range.offset(0, -4).getValue() + 1);
  }
}

answered Aug 13 '22 at 21:18

Cooper

59,616
6
23
54

Thank you! That has done the trick. Now, as I need to run this code with an API request, and I have another function to run onEdit, would this be the trigger needed? `function onEdit(e) { ScriptApp.newTrigger('clicker(e)') }` – Yannis Volos Aug 13 '22 at 21:29
@YannisVolos that won't work in a simple trigger, and even if it worked, you would be creating a myriad new triggers unnecessarily. – doubleunary Aug 13 '22 at 21:39
@Cooper, *clicker(e)* would be the renamed *onEdit* function. I think my comment wasn't too clear. – Yannis Volos Aug 13 '22 at 21:44
@Cooper, so you suggest that I combine the other function that has to be run as onEdit in one function called onEdit? – Yannis Volos Aug 13 '22 at 21:47
If you wish to change it's name you can make an installable trigger but there is no reason in the function to do that. It does not do anything that requires permission. – Cooper Aug 13 '22 at 21:49
If you want to run another onEdit then you must put it into the same function declaration. Because all functions must have a unique name – Cooper Aug 13 '22 at 21:56
See [this sample code](https://webapps.stackexchange.com/a/155429/269219). But the `onEdit(e)` simple trigger will not run when your end point is called. – doubleunary Aug 13 '22 at 21:58

doubleunary · Answer 2 · 2022-08-13T21:25:42.440

Your code is trying to put the incremented value in column F when you probably want to put it in column A. To run the function when a cell is hand edited, use a simple trigger, like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet.'
    );
  }
  clicker_(e);
}


/**
* Increments a cell in 'Report 1'!B2:B when a cell in 'Report 1'!F2:F is
* hand edited.
*
* @param {Object} e The onEdit() event object.
*/
function clicker_(e) {
  let sheet;
  if (e.range.columnStart !== 6
    || !(sheet = e.range.getSheet()).getName().match(/^(Report 1)$/i)
  ) {
    return;
  }
  const range = sheet.getRange(e.range.rowStart, 2);
  range.setValue((Number(range.getValue()) || 0) + 1);
}

Although, the code you have supplied, has not worked for me, it may be because of the trigger. Then again, for the moment, all edition is hand made, which isn't how it is meant to be done. I'm afraid I've lost you. — Yannis Volos, Aug 13 '22 at 21:18
There was a typo in the code: an exclamation mark was missing. Fixed. — doubleunary, Aug 13 '22 at 21:26

Increment cell value onEdit

2 Answers2

Increment B on edit of F