How to write an array to Google Spreadsheet?

2
votes

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, ...]
4
javascriptarraysgoogle-apps-scriptgoogle-sheets
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 ? – Serge insas

4 Answers

2
votes

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).

3
votes

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);
1
votes

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())

0
votes

you can do this way:

function write(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('-sheet name-');
  var myArray=new Array();
    for (i=0;i<100;i++){
      myArray[i]=i;
    }
sheet.getRange(8,4,1,100).setValues([myArray]);
}

hope that helps!