CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Omar
Omar

Reputation: 181

How to print the data of an array into a Google sheet with Google Apps Script

So I have got this array that is called sourceData that contains elements and each element has 10 values so basically i want to print 252 rows and each row has a value in the cell (10 cells) starting from row 3. So what I did is that I created a for loop but its not working and its printing the week number "370" (which is the first value of the array) instead, and it was printed 503 times.

I also think that creating a for loop is going to take too long to print the data since there is a big number of data, so was wondering if there is a code that would be better than that one .

var sourceData =sourceData.sort(function(a,b){ return a[2] > b[2] ? 1 : -1; });
  sourceData = sourceData.reverse()
  
  for(let x=3; x<sourceData.length+3; x++){//   x is number of the row
  var y=0;
  thisSheet.getRange(parseInt(x),1 ,sourceData.length,10).setValue(sourceData[y]);
  y++;

I'm not gonna include the code how i got the sourceData since I dont think it's necessary

If you need more explanation please let me know.

Thank you.

Upvotes: 0

Views: 3008

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

Provided you want to set a 2D array into a spreadsheet

If sourceData is a nested array with rows and cells, you can set in into the sheet with one call: setValues() instead of setValue() -no looping necessary.

Sample:

sourceData = sourceData.reverse();
thisSheet.getRange(3,1, sourceData.length,sourceData[0].length).setValues(sourceData);

Side note:

The main problem with your approach is that you set y at the beginning of each loop iteration back to 0 which resets your y++ request. If you want to set the data row by row - eihter define var y=0; before the loop or skip it completely and use x instead:

for(let x = 0; x < sourceData.length -1; x++){//   x is number of the row
  thisSheet.getRange(parseInt(x) + 3, 1, 1, 10).setValues(sourceData[y]);
}

Upvotes: 1

Related Questions