javascript - How to write an array to Google Spreadsheet? - Stack Overflow

admin管理员组

文章数量:1289515

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

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

• javascript
• arrays
• google-apps-script
• google-sheets

Share Improve this question Follow edited Dec 2, 2014 at 21:13 KRR 4,94722 gold badges1616 silver badges1414 bronze badges asked Dec 2, 2014 at 18:16 user882670user882670 1

• 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 Commented Dec 2, 2014 at 20:59

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 3

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

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

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

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!

本文标签： javascriptHow to write an array to Google SpreadsheetStack Overflow