CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheets
Ryan Richards
Ryan Richards

Reputation: 75

Google Scripts: Populating Multi-Dimensional Array With Cell Values

I am learning the basics of Google Scripting after using VBA in Excel for many years, so please forgive the basic question.

I want to take a range of cells in my sheet using getValues() and then create a multi-element array.

Here is a simple example from my code:

  var gameInfo = [];
  gameInfo = gameMasterSheet.getRange(3, 3, 1, 9).getValues();

As you can see, the defined range is 9 cells in a single row.

My goal is to create an array from these 9 cells, and have each cell be accessible via a separate array element. However, it seems all 9 cell values are being inserted into gameInfo[0], and when I reference gameInfo[2] hoping to obtain the value of the third cell in the range, "undefined" is returned.

Is there a way to use getValues() to populate an array with separate elements? If so, how is this done? If not, what is a better alternative?

Thanks for any help you all can provide!

Upvotes: 2

Views: 1255

Answers (2)

Karl_S
Karl_S

Reputation: 3574

See the script at the bottom of the Simple Mail Merge Tutorial for some useful code to retrieve data. Copy all the code from this comment down:

//////////////////////////////////////////////////////////////////////////////////////////
//
// The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
// tutorial.
//
/////////

/////////////////////////////////////////////////////////////////////////////////

// getRowsData iterates row by row in the input range and returns an array of objects.

Once you have, use getRowsData to retrieve the data as an array of objects. If I understand you correctly, this will get you want you are after.

Karl S

Upvotes: 0

Dan Oswalt
Dan Oswalt

Reputation: 2189

getValues returns a 2d array, even when it's a single row of cells. Think of it like this: gameInfo[row - 1][column - 1], so the top left is gameInfo[0][0].

All of your data is in gameInfo is in one row (gameInfo[0]), so the third element will be accessed as gameInfo[0][2] (row 1, column 3).

gameInfo[2] would be the third row, which is indeed outside of the range and undefined.

Also: to get just the values into an array from a 2d array, you could do this:

const values = [];
gameInfo.forEach(row => {
  row.forEach(column => {
    values.push(column);
  })
})

Upvotes: 1

Related Questions