Question 338410 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – google script array map function transversing output

I have an array of numerical values in a google sheet that all need to have the sign changed on them using app script. For example, 6000 becomes -6000 and so on down the line. I am trying to use an array map function to do this.

When I complete the script, it seems to have transversed the array. My one column and multiple rows has become one row and multiple columns. I’ve tried so many different examples on this site and I can’t seem to make them work. I am new to this, so arrays still seem confusing.

For now, I have simplified and I am testing this on a known (and fixed) range. I’ll add the flexibility to the ranges once I can solve the transversion problem.

    function changesign() {
      const test_sht = monthly_expense.getSheetByName("Test");
      var sample1 = test_sht.getRange("B2:B6");
      var values1 = sample1.getValues();
      Logger.log(values1);


      var newValues = values1.map(function(value) {
      return value * -1;
      });
      Logger.log(newValues);
      test_sht.getRange("G1:G5").setValues([newValues]); 
    }

Result from logger:
10:49:13 PM Info [[6000.0], [6000.0], [7000.0], [525.0], [23.93]] 10:49:13 PM Info [-6000.0, -6000.0, -7000.0, -525.0, -23.93] 10:49:13 PM Error Exception: The number of rows in the data does not match the number of rows in the range. The data has 1 but the range has 5.

Tags:

2

Answers


  1. 0

    values1 is an array of arrays.

    So in map(…), each time, value is an array that you multiply by -1. Yes, JS is a weird language that lets you multiply an array by a number and gives a number as result… That’s why you get returned a number, instead of an array with a number in it.

    Let’s do it right:

    return [ value[0] * -1 ];

    First, we unwrap the number, then multiply it by -1 and wrap it back in a new array. In the returning result we have an array containing negative number. Then, after map(…) did its job on each value, it returns a new array of arrays newValues.

    Login or Signup to reply.
  2. 0

    You’re trying to set a column of data (G1 to G5), but the values you’re trying to set are in the form of populating a row of data. The .setValues() method takes an array in the shape of values[row][column] (you’re trying to set it as if it accepts an array in the shape of values[column][row], so things get misaligned with the range you’ve specified)

    Instead, you should use a nested .map() to create a 2D array in the below shape, which will populate the column:

    [
// v-- column
  [-6000.0], // row
  [-6000.0], // row
  [-7000.0], // row
  [-525.0],  // row
  [-23.93]   // row
]

    Here’s how the double-map would look:

    var newValues = values1.map(function(row) {
  return row.map(function(value) {return value * -1;});
});

test_sht.getRange("G1:G5").setValues(newValues); // set the newly mapped values (not `[newValues]` wrapped in an array).
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search