Google App Script: Increment each value in an Array

Question

I have an array, which is a row of numbers from a sheet. I want to increment each number in the row, however my attempts simply add a value of 1 to the end of the array. Once I've got this sorted I'll need to add the array back onto the sheet, but I haven't got that far yet. I've tried this:

function turnIncrement() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastColumn = ss.getLastColumn()
var turnValuesRow6 = ss.getRange(6, 4, 1, lastColumn); 
var turnValues = turnValuesRow6.getValues(); 

for (var i=0; i < turnValues.length; i++) {
 turnValues[i] += 1;}
}

and this

function turnIncrement() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lastColumn = ss.getLastColumn()
var turnValuesRow6 = ss.getRange(6, 4, 1, lastColumn); 
var turnValues = turnValuesRow6.getValues(); 

turnValues.push(turnValues[turnValues.length - 1] + 1);
turnValues.shift();

Both of which just add 1 to the end of the array. I guess I could try and get each value individually, but that seems very inefficient.

Spencer Easton · Accepted Answer · 2016-12-20 17:47:59Z

You can iterate over an array with Array.map().

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map

Here is a simple example. map() will loop over each value in the array and the provided function will return what is to be done with the value;

[2,3,4] = [1,2,3].map(function(value){return value += 1});

Your example.

function turnIncrement() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastColumn = ss.getLastColumn();
  var turnValuesRow6 = ss.getRange(6, 4, 1, lastColumn); 
  var turnValues = turnValuesRow6.getValues(); 
  turnValues =  turnValues[0].map(function(col){ return col+=1});
  turnValuesRow6.setValues([turnValues])
}

TIP: Remember getValues returns Array[][]. So even if you only request a single row it still return a multidimensional array and setValues() is looking for a Array[][].

score 1 · Accepted Answer · 2016-12-20 17:22:07Z

1

The main thing to understand is that turnValues is a two-dimensional array, because this is the return type of getValues. It looks like [[1, 2], [3, 4]] for a 2 by 2 range. For a 1-by-2 range (1 row, 2 columns) it's [[1, 2]]. For a 1-by-1 range (a single cell) it's like [[1]].

In your specific case, with a one-row range, the fix would be

for (var i=0; i < turnValues[0].length; i++) {
 turnValues[0][i] += 1;
}

In general, you'd need a double loop

for (var i=0; i < turnValues.length; i++) 
  for (var j=0; j < turnValues[0].length; j++) {
    turnValues[i][j] += 1;
  }
}

edited Dec 20, 2016 at 17:22

answered Dec 20, 2016 at 17:17

user6655984

2 Comments

Dan Howard Over a year ago

Thanks for the response. I'm going to take the Array.map answer below. But this answer has helped me understand 2d Arrays

Dan Howard Over a year ago

Just so you know, I just had a different problem but this thing you'd said about 2d arrays solved it for me. Nice one!

Collectives™ on Stack Overflow

Google App Script: Increment each value in an Array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related