[Google Sheet Script - increment cell value onEdit() of specific row] #gsheet #actionscript #onedit #googlesheet #onedit #increment

readme.md

Google Sheet Script - increment cell value onEdit() of specific row

When a cell within a specific row is edited, the cell in the next column should be get a incremented value (integer)

How To use it

1. Open your Google Sheet
2. Go to Tools > Script Editor
3. Paste in the Code
4. Hit Save (cmd+s)

Options

sheetName: The name of the sheet, where the values has to be updated
columnToWatch: Has to be a number and suffixed with .0
columnToUpdate: Number of the column e.g. D == 4
incrementBy: the number the value should be incremented by

Video

Proof of Concept https://www.loom.com/share/0de5ed4b9e824911b720f66523621d66

onEdit.gs

/* 
 ** Increment a cell value by number, when edit a specific cell.
 */


/*
 **  Edit ONLY these ↓ variables 
 **/
 // The name of the sheet, where the values has to be updated
var sheetName = 'Tabellenblatt1'; 
// Number of the column where the change/update manually happens. Has to be a number and suffixed with .0
var columnToWatch = '1.0';
 // Number of the column, which should be updated automatically. e.g. D == 4
var columnToUpdate = '4';
// the number the value should be incremented by
var incrementBy = 1;
// Note content - a note is added to the automatically changed value Default Last modified by User-Email
var noteContent = 'Last modified: \n' + new Date().toLocaleString('de-DE') + '\n\nby \n' + Session.getEffectiveUser().getEmail();
/**
 **  STOP EDITING BELOW THIS.
 **/
function onEdit(e) {
  // Prevent errors if no object is passed.
  if (!e) return;
  var range = e.range;

  // Column to watch (e.g. 1.0 === A)
  if (range.getColumn() == columnToWatch) {

    // Get the active sheet.
    var activeSheet = e.source.getActiveSheet();

    // Only update in the according sheet
    if (activeSheet.getName() == sheetName) {

      var currentRow = range.getRow();
      var currentColumn = range.getColumn();

      // get current value of destinated cell
      var currentValue = activeSheet.getRange(currentRow, columnToUpdate).getValue();

      // increment current value by `incrementBy` variable
      var newValue = currentValue + incrementBy;

      // Update Cell
      activeSheet
        // Set the cell you want to update with the incremented cell.
        .getRange(currentRow, columnToUpdate)
        // Update the the cell with new incremented value.
        .setValue(newValue)
        // Add note who edited the value last
        .setNote(noteContent);
    }
  }
}