How to write an array to Google Spreadsheet?

Question

I'm building an Array with integer values and trying to write it to a Google Spreadsheet in one go:

var myArray=new Array();
    for (i=1;i<=100;i++){
      myArray[i]=i;
    };

    ss.getRange(8,4,1,100).setValue(myArray);

This is writing on the right cells but the content of each cell are:

[0.0, 1.0, 2.0, 3.0, 4.0, 5.0, ...]

Solution 1

Try this, which gives you 100 cells with values from 0 to 99:

var myArray = [];

for (i=0 ; i<100 ; i++){
  myArray[i] = i;
}

sheet.getRange(8, 4, 1, 100).setValues([myArray]);

One reason this wasn't working for you is that you were using setValue() instead of setValues(). Another reason is that, had you been using setValues(), you would have been trying to force a 1D array into what is essentially a 2D array. To solve the problem, be sure to use setValues([myArray]), which makes a 2D array from the one dimensional myArray, instead of setValue(myArray).

Solution 2

You have to add myArray in to another array. Do it in following way,

var data = [];
var myArray=new Array();
    for (i=0;i<100;i++){     // You have to change index to 0 here.
      myArray[i]=i;
    };
data.push(myArray);

ss.getRange(8,4,1,100).setValues(data);

Solution 3

Depending on how you want your data to be written, you have 2 ways doing that : both will create arrays of arrays (aka matrix ou 2D arrays):

2 code examples :

function writeToSheetRows(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  var myArray=new Array();
    for (i=1;i<=100;i++){
      myArray.push([i]);
    }
  ss.getRange(8,4,myArray.length,myArray[0].length).setValues(myArray);
}
function writeToSheetCols(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  var myArray=new Array();
    for (i=1;i<=100;i++){
      myArray.push(i);
    }
  ss.getRange(8,4,1,myArray.length).setValues([myArray]);
}

Note that you have to use setValues() with an S (and not setValue())

Comments

[Serge insas]

first thing to know : do you want the data in successive rows or successive columns ? and then how do you want the numbers to be shown ?

[rpm]

Check this link soon.

[rpm]

@Paul I know. I have just added array to make myArray a 2D array. All are doing same thing here. Just a different way of doing. By the way, even though all the answer which are present here are same, I don't know who has down voted my answer without putting any comment.

[John]

Let us continue this discussion in chat.

[Admin]

This is returning: [[0.0, 1.0, 2.0, 3.0, 4.0, 5.0, ...]] in each cell.

[Admin]

Hi, thanks for your help, but the 1st function is returning: [0.0, 1.0, 2.0, 3.0, 4.0, 5.0, ...] in each cell. The 2nd is returning: [[0.0, 1.0, 2.0, 3.0, 4.0, 5.0, ...]] in each cell

[Admin]

[[0.0, 1.0, 2.0, 3.0, 4.0, 5.0, ...]] in each cell

[KRR]

I tried this code and it is adding them as individual numbers in individual cells.0 1 2 3 4 5 6 7 8 9

[Serge insas]

are you kidding me (us) ? this is non sense...like all the other answers this code is simply working (and of course tested) so if you get what you say then obviously you are making something wrong. (but frankly I can't imagine what since this is so basic...)

[Serge insas]

Edit : Actually you get this error because you omit the 'S' in setValues(), please try to write scripts CORRECTLY.

[Admin]

No kidding. I'm using SetValue instead of SetValues. The data is being written vertically although it should be horizontally. With these corrections, it works.

[John]

Please try copying and pasting the code into your script. It works as I described it.

[rpm]

@Sergeinsas Thank you for acknowledging my answer. Exactly, he is doing something strange, or every answer presented here should work.

[Serge insas]

Yeah, all this for a missing S... unfortunately he accepted the other one although you were first on this... btw, the accepted answer does not provide a valid explanation... nothing we can do I guess ;-)

[rpm]

@Sergeinsas Yup exactly. But, that's ok if he accepted my answer or not, I just wanted to clarify that my answer was not wrong as someone has down voted it. But, I am glad you acknowledged that.

[John]

You thought he was correctly using setValues(), as did I. My edited explanation is indeed correct.

[aNewb]

sheet.getRange(20, 4, 10, 10).setValues([myArray]); gets error 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 10. (line 13, file "Code") ???