CODEWITHSUNDEEP
arraysgoogle-apps-script
Simon Breton
Simon Breton

Reputation: 2876

Build the proper array with push method to send data to google sheet in the right format

For a very specific goal, for which I won't go into detail here, I need to grab some data from one spreadsheet and send these data to another one. However I must use the push method somewhere in my code.

Concretely here is what I'm trying to do :

function myFunction() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Raw_data");
    var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet10");
  var Row_count = sheet.getLastRow()
  var raw_data = sheet.getDataRange().getValues();

   var data = [];

  for (var i = 0; i < Row_count ; i++) {
    data.push(raw_data[i].toString())
    }

sheet2.getRange(1, 1,1, 307).setValues([data]);
  }

So basically I would like to be able to breakdown the getValues(); output and recompose it with the push method.

Right now all my data are being send in sheet2 on the same row. Using the push method how do I go to get the following output :

Header1,Header2,Header3,Header4,etc...
Data11,data12,data13,data14
Data21,data22,data23,data24
Data31,data32,data33,data34,
etc...

thanks !

Upvotes: 0

Views: 50

Answers (1)

T.J. Crowder
T.J. Crowder

Reputation: 1073988

According to the documentation, the range you're pushing that data into should be the same shape as the data you're pushing, but it isn't if I'm reading getRange correctly. There are two differences: Your range is only one row high, and your range is 307 columns wide. But your data is Row_count rows high, and one column wide (with that column containing the result of converting the cells into a single comma-delimited string).

So I'd think changing your getRange at the end would be sufficient:

sheet2.getRange(1, 1, Row_count, 1).setValues([data]);
// -------------------^^^^^^^^---^

In a comment, you've said you want the data you pass to setValues to be the same shape as raw_data. Right now it isn't, raw_data is a two-dimensional structure; you're building a one-dimensional array with string-joined values and then wrapping it in another array — so it ends up being two-dimensional, but it's one row with Row_count columns.

You'll need nested loops if you want to build the same structure:

var data = [];
var row;
var dataRow;
var rowIndex;
var Col_count = raw_data[0].length;
var colIndex;
for (rowIndex = 0; rowIndex < Row_count; rowIndex++) {
    row = raw_data[i];
    dataRow = [];
    data.push(dataRow);
    for (colIndex = 0; colIndex < colCount; colIndex++) {
        dataRow.push(row[colIndex]);
    }
}
sheet2.getRange(1, 1, Row_count, Col_count).setValues(raw_data);

Upvotes: 1

Related Questions