0

I am experienced with VBA but not Google Apps Script / Javascript.

I'm doing a lot of data entry for which filling down with CTRL+D is super-useful but I want to create a macro that will copy down the value from the row above while also adding 1.

I've had a few attempts and I can't get it to work.

So far, I have only successfully achieved it by the rather obtuse two-step method of copying down with CTRL+D followed by this code to increment the value:

  var c = SpreadsheetApp.getActive().getCurrentCell();
  c.setValue(c.getValue()+1);

How do I get the value of the cell above and add 1 to it in a single step?

0

1 Answer 1

1

Macro

/** @OnlyCurrentDoc */

function myFunction() {
  const cell = SpreadsheetApp.getActive().getCurrentCell();
  cell.setValue(cell.offset(-1, 0).getValue()+1);
  cell.offset(0, 1).activate();
}
  1. Sets the current cell's value to 1 plus the value of the cell that is offset -1 rows and 0 columns (directly above it).
  2. Activates the cell that is offset 0 rows and 1 column from the current cell (directly to the right).

Running Macro

Use either of the following approaches.

  1. Set up a keyboard shortcut using Ctrl+Alt+Shift+[0-9]. You select the specific digit when you first setup the Macro or you can set it up or modify it in the Manage macros section. Access it at:
      →  Menu:        Extensions > Macros > Manage macros
      →  Keyboard:  Alt+Shift+N M M
  2. Run the macro directly from the menu:
      →  Menu:        Extensions > Macros > Your_macro_name
      →  Keyboard:  Alt+Shift+N M Enter
           If the macro is not 1st in the list you'll need more down arrows
2
  • 1
    Thanks. This does almost achieve what I wanted to do, but it still leaves a formula in the cell rather than a value, which for various reasons is not suitable for my needs. Your suggestion of CTRL+C then CTRL+SHIFT+V will of course convert the formula to its value, but I am trying to find a way to have the macro write the incremented value (rather than to formula) to the cell in the first place. Commented May 12 at 18:47
  • @DanielAustin answer has been modified to return a value instead of a formula.
    – Blindspots
    Commented May 13 at 5:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.