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

Question

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.

I don't know about your error message. Try removing parseInt. And move var y=0 above the for loop. As it is y is always 0. You didn't show the closing curly brace. Also shouldn't it be { return a[2]-b[2]; }? — TheWizEd
– TheWizEd, Commented Feb 2, 2022 at 15:20

ziganotschka · Accepted Answer · 2022-02-02 15:22:25Z

1

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

answered Feb 2, 2022 at 15:22

ziganotschka

26.9k2 gold badges18 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

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

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

Comments

Your Answer

Sign up or log in

Post as a guest

Related