Increment (and Decrement) values in a [Google Sheet] column

Question

The image (attached) is a simple "AppScript" adapted from a popular YouTube ...that allows "Values" (in a column) to be increased by 1 - by clicking an "Arrow" (button) - that I've linked to the script

By Clicking the "Up" Arrow, all the values will change from 6 ...to 7 (ie: go "Up" by one value)

QUESTION(s): How do I 'adjust' the Script to:

Assess (or Evaluate) the Values in the column
Ignore any values that are "Zero" (0)
Increment (by 1) ...all the other "Non-Zero" values

And finally, if a Value is 'different' than the other values (eg: 5 ...instead of 7)

Increment a value of 5 to 6 ...and the 'other' values of 7 to 8

Currently, the Script will change 5 to an 8 (along with all the 7's)

I'd like the Script to change the 5 to a 6 (and the 7's can increment to 8's)

DuuEyn · Accepted Answer · 2024-02-13 16:42:21Z

0

Suggestion:

You can have the script iterate through each of the value of the range and increment it if the value isn't equal to 0.

Do try this script:

function incrementA2A5() {
  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var range = sourceSheet.getRange("A2:A5");
  var values = range.getValues();

  //The forEach loop below iterates through each value of the range.
  //If the value isn't equal to zero, the loop will increment it by one.

  values.forEach(x => x[0] != 0 ? x[0] += 1 : x[0]);

  range.setValues(values); //Sets the updated values on the range
}

.GIF demo of the script:

edited Feb 13, 2024 at 16:42

answered Feb 13, 2024 at 16:20

DuuEyn

7471 gold badge5 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jayson Chabot Over a year ago

The Script works (like a charm) - Wow 😲 I'm only familiar Excel's < or > logic (algorithms) ...so, any advice on how: x[0] ...and !=0 ...and =1 : x[0] ...works, would be greatly appreciated 🎓 Thanks again for your help 👍

DuuEyn Over a year ago

You're welcome! The statement you're referring to's utilizing Javascript's Ternary operator The statement x[0] != 0 ? translates to if x[0] is not equal to zero The next part, x[0] += 1, adds 1 to the value if the if statement is true. : x[0] is essentially an else statement and means that the value is returned without any modification if the if statement is false. x[0] is just a placeholder referring to the first and, in the example, only value of the row being evaluated.

Jayson Chabot Over a year ago

Follow-up ...re: adding multiple columns (or ranges) to increment. I'd like to increase the values in Columns/Rows A2:A5 & C2:C5 & E2:E5

Collectives™ on Stack Overflow

Increment (and Decrement) values in a [Google Sheet] column

1 Answer 1

Suggestion:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Suggestion:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related