Help needed: Converting Excel accumulation script to Google Sheets

Other Questions Google docs
1

I’m having trouble moving my Excel spreadsheet to Google Sheets. There’s a script in Excel that adds up numbers in a row. But it’s not working in Google Sheets.

Here’s what the script does:

When I type a number in cell C3, it adds that number to the value in C4. I want this to work for the entire row. For example:

  • C4 starts with 5, D4 with 7, and E4 with 12
  • If I enter 10 in C3, then C4 becomes 15
  • If I enter 6 in D3, D4 becomes 13
  • If I enter 7 in E3, E4 becomes 19

Can anyone help me convert this to Google Sheets? Or even improve it so it handles all cells in the row? Thanks!

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var cell = e.range;
  
  if (cell.getRow() === 3 && cell.getColumn() >= 3) {
    var value = cell.getValue();
    if (typeof value === 'number') {
      var targetCell = sheet.getRange(cell.getRow() + 1, cell.getColumn());
      targetCell.setValue(targetCell.getValue() + value);
    }
  }
}
2

I’ve dealt with similar issues when transitioning from Excel to Google Sheets. The good news is, your script is pretty close to what you need! Here’s what I’d suggest based on my experience:

First, make sure you’ve added the script to the Script Editor in Google Sheets (Tools > Script editor). Then, try this modified version:

function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();

if (range.getRow() === 3 && range.getColumn() >= 3) {
var value = range.getValue();
if (typeof value === ‘number’) {
var targetCell = sheet.getRange(range.getRow() + 1, range.getColumn());
targetCell.setValue(targetCell.getValue() + value);
}
}
}

This should work for the entire row as you requested. One thing to keep in mind: Google Sheets can be a bit slower with scripts compared to Excel, so you might notice a slight delay when entering numbers.

If you’re still having issues, double-check that script authorization is enabled for your Google account. Hope this helps!

3

Your script is on the right track, but Google Sheets handles some things differently than Excel. Here’s a refined version that should work more efficiently:

function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();

if (range.getRow() === 3 && range.getColumn() >= 3) {
var value = range.getValue();
if (typeof value === ‘number’) {
var targetCell = sheet.getRange(range.getRow() + 1, range.getColumn());
targetCell.setValue(targetCell.getValue() + value);
range.clearContent(); // Clear input cell after adding
}
}
}

This script adds the entered value to the cell below and then clears the input cell, preventing accidental double-counting. Remember to set up a trigger for the onEdit function in the Script Editor for it to work automatically. Let me know if you need any further adjustments!

4

hey dancingbird, i’ve used google sheets for a while now. your script looks good, but here’s a small tweak that might help:

function onEdit(e) {
var r = e.range;
if (r.getRow() == 3 && r.getColumn() >= 3) {
var v = r.getValue();
if (typeof v == ‘number’) {
r.offset(1, 0).setValue(r.offset(1, 0).getValue() + v);
}
}
}

this should work for all cells in row 3. lmk if u need more help!