CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Daniel Weigel
Daniel Weigel

Reputation: 1137

Can't paste array into range with setValue()

I mapped a function to an array which is supposed to give me Yes or No depending on the input when I Logger.log(costsfinalised) the array I get this:

[No, No, No, No, Yes, No, No, Yes, Yes, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No, No]

But when I try to paste it with setValues(costsfinalised), I get an error saying:

'Cannot convert Array into Object'

When I do setValue(costsfinalised) instead, it does work but only with Nos (it is not really what is in the log. Why? How can I paste this array into a range?

var costsfinalised=sourcevalues.map(costs);

  //Logger.log();
  Logger.log(costsfinalised);

destinationsheet.getRange(2,7,costsfinalised.length,1).setValues(costsfinalised);

Upvotes: 0

Views: 114

Answers (1)

0Valt
0Valt

Reputation: 10355

Problem

You are trying to pass an illegal argument into the method, setValues() accepts only two-dimensional Array instances. You should treat the two-dimensional Array as cell mapping, where elements represent rows and their elements columns:

//pseudo-code;
[
 row 1 : [col1, col2],
 row 2 : [col1, col2]
]

Solution

You can modify your code to generate this Array for you (judging from your code, you want to populate one column, right? Regardless of that, if you add a second loop to generate column values, you will be able to populate Range with custom number of rows and columns):

var col = destinationsheet.getRange(2,7,costsfinalised.length,1);

var temp = [];

for(var i=0; i<costsfinalised.length; i++) {
  var row = [ costsfinalised[i] ];
  temp.push(row);
}

col.setValues(temp);

Alternatives

Alternatively, you can use the map method on your Array (in your case you can simply reference the cost instead of costsfinalised[i] - just don't forget to wrap it as well - return [ cost ]):

var col = destinationsheet.getRange(1,1,costsfinalised.length,1);

var output = costsfinalised.map(function (cost,i) { return [ costsfinalised[i] ]; } );

col.setValues(output);

Useful links

  1. setValues() method reference;
  2. Array built-in reference on MDN;
  3. map() method reference on MDN;

Upvotes: 3

Related Questions