Append an array in gSheets?

Question

Firstly I am very inexperienced here so apologies if this is obvious.

I have an array of data that updates automatically and I wish to copy this data out so I have an archive of it.

I have managed to find the following script which works for what I want:

 function saveInstaPostData() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("xxx");
var data0 = sheet.getRange("xxx!A2:AQ21").getValue();
var data1 = sheet.getRange("xxx!B2").getValue();
var data2 = sheet.getRange("xxx!C2").getValue();
var data3 = sheet.getRange("xxx!D2").getValue();
 sheet.appendRow([data1,data2,data3,...]);
}

However I have a range of 860 cells, so doing it one by one isn't too feasible.

Reading up on the appendRow method I realise that it (seems) to only be able to append one row at a time. I only have 20 rows so this should still be doable.

I then tried using

function saveInstaPostData() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Insta faction post data");
 var values = sheet.getSheetValues(2, 1, 1, 43);
 sheet.appendRow([values]);

However this outputs the following in the first cell of the new row: [Ljava.lang.Object;@247f2c9a

This seems to be the array I'm trying to append (java: what is this: [Ljava.lang.Object;?) however I can't get it to work!

[I have also tried using

function saveInstaPostData() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Insta faction post data");
 var range = sheet.getRange(2,1,20,43);
 var values = range.getValues();  
 sheet.appendRow([values]);

This outputs ' Range ' in the first cell of the new row. ]

Any pointers would be really appreciated,

Thanks,

@JamesDonnellan I was not sure if there is other problem and I was in a rush — Pierre-Marie Richard, Jul 20 '17 at 14:12

score 0 · Accepted Answer · answered Jul 20 '17 at 14:11

0

On your example, you try to append a tri dimensionnal array:

var values = sheet.getSheetValues(2, 1, 1, 43);

or

var range = sheet.getRange(2,1,20,43);
var values = range.getValues();

Your "values" variable contains a bi dimensionnal array, that you've put in a mono dimensionnal array:

sheet.appendRow([values]); // [values] put bi dimensionnal array in a mono dimensionnal array

To append your data, you need to append each row of your array, one after a other like :

for(var i=0; i<values.length;i++){
  sheet.appendRow(values[i]);
}

answered Jul 20 '17 at 14:11

Pierre-Marie Richard

1,914
2
19
25

Thanks, unfortunately it returns the same `[Ljava.lang.Object;@33c113bd` Is it possible that the function can't list an array?! – user8337890 Jul 20 '17 at 14:35
the function append a new row with the mono dimensionnal array give as parameters. Could you provide a sample of your sheet ? – Pierre-Marie Richard Jul 20 '17 at 14:38
I realised I made a simple syntax error when implementing your suggestion - it does actually work ! Thanks – user8337890 Jul 20 '17 at 15:08

Append an array in gSheets?

1 Answers1