CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
user882670
user882670

Reputation:

How to write an array to Google Spreadsheet?

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, ...]

Upvotes: 2

Views: 18276

Answers (4)

John
John

Reputation: 916

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

Upvotes: 2

Serge insas
Serge insas

Reputation: 46794

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

Upvotes: 1

KRR
KRR

Reputation: 4917

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!

Upvotes: 0

rpm
rpm

Reputation: 583

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

Upvotes: 3

Related Questions