How to increment a number using a macro in Google Sheet

Question

I have a spreadsheet (in Google Sheets) that I use to track the evolution of some habits. Each time I complete or perform a habit, I mark it in green and increment a number in that habit's count. If I fail to do so, I mark it in red and deduct a number from the count.

The idea is for me to keep an eye on my evolution over the last 5 days and gradually improve. Some examples:

I tried to create a macro to make it easier and faster to mark a habit as done or not done, but the script is not perfect. I created this macro using the basic macro creation tool, where the software reads our every move. Here is the resulting code:

function Positive() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setBackground('ACCENT2');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().offset(1, 0, 4, 1).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getCurrentCell().offset(4, 0).activate();
  spreadsheet.getActiveRangeList().setBackground('ACCENT2');
  spreadsheet.getCurrentCell().setFormulaR1C1('=R[-1]C[0]');
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

However, when I run this code, the cell that should contain the incremented number appears blank.

I have a very basic knowledge of scripts and I can see that the problem is exactly when I'm going to copy and paste the value of the last cell (which used to be a formula and can't stay that way so as not to disturb the other numbers in the future).

If I split this script in two, separating the function of copying and pasting the value, the process works. The two functions would look like this:

function Part1() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRangeList().setBackground('ACCENT2');
  spreadsheet.getCurrentCell().offset(0, 1).activate();
  spreadsheet.getCurrentCell().offset(1, 0, 4, 1).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getCurrentCell().offset(4, 0).activate();
  spreadsheet.getCurrentCell().setFormulaR1C1('=R[-1]C[0]+1');
};

function Part2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

Can anyone help me to solve this problem?

score 1 · Answer 1 · answered Jun 22 '22 at 17:26

My immediate thought is you could implement some type of button to increase or decrease the total after completing each action.

I have provided a link below to an article that will walk you through the steps of creating an incremental button as well as some of the code needed.

https://productivityspot.com/spin-button-google-sheets/

Increase by one

function increment() {
 SpreadsheetApp.getActiveSheet().getRange('C5').setValue(SpreadsheetApp.getActiveSheet().getRange('C5').getValue() + 1);
}

Decreased by one

function decrement() {
 SpreadsheetApp.getActiveSheet().getRange('C5').setValue(SpreadsheetApp.getActiveSheet().getRange('C5').getValue() - 1);
}

How to increment a number using a macro in Google Sheet

1 Answers1