Googlesheets Script Arrays

Question

I'm quite new to scripting in Google sheets.

I'm working on a tool that will read in quite a bit of data and then will output it. So my plan is to read the data into an array. Create an array and then output the array to a sheet. (I have it working where it reads and writes cell by cell but it's painfully slow)

I'm just doing a bit of testing at the moment and I'm really struggling, I've been googling and reading other questions on here, but mine seems so basic that I can't find the answer I'm looking for.

This is the bit of code that I've written so far. I'm basically trying to but some data with 9 rows and 4 columns into an array, and then query the array and figure out how to set values (I'd rather set values of the entire array instead of just a single bit of data, but I can't even get the single bit of data working)

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test Data') 
var myArray = mySheet.getRange(1,1,9,4).getValues()
mySheet.getRange(2,10).setValue(myArray.length)
mySheet.getRange(3,10).setValue(myArray.height)
mySheet.getRange(4,10).setValues(myArray[1],[1])

My queries are: Am I actually creating a 2d array?

This screenshot is what I get when I run the code

Why does length return 18 and height return nothing? and if I enable the setValues bit of my code I get this error

Exception: The parameters (String,number[]) don't match the method signature for SpreadsheetApp.Range.setValues.

Yuri Khristich Yuri Khristich · Accepted Answer · 2021-04-27T22:30:21

var myArray = mySheet.getRange(1,1,9,4).getValues();

This way you get array of 9 arrays. Each of the sub-arrays contains 4 values.

So myArray.length is 9.

And myArray[0].length is 4.

You can address the cells of the array this way:

var cel_A1 = myArray[0][0]; // "Project / Retainer"
var cel_B1 = myArray[0][1]; // "Employee"
var cel_D9 = myArray[8][3]; // "40.00%"

etc

As for the slowness. There is a trick. You should set values for as many cells as you can at once. It's much faster. It takes about the same time for one cell or for hundreds cells. First you make a two-dimensional array with right values and sizes and then you set all the range (it should have exactly the same size) with myRange.setValues(myArray).

Googlesheets Script Arrays

1 Answers