How to separate multiple columns from a range in an array?

Question

I have a range of data in a Google Sheet and I want to store that data into an array using the app script. At the moment I can bring in the data easily enough and put it into an array with this code:

  var sheetData = sheet.getSheetByName('Fruit').getRange('A1:C2').getValues()

However, this puts each row into an array. For example, [[Apple,Red,Round],[Banana,Yellow,Long]].

How can I arrange the array by columns so it would look: [[Apple,Banana],[Red,Yellow],[Round,Long]].

Thanks.

JPV · Accepted Answer · 2018-12-27 11:27:46Z

1

It looks like you have to transpose the array. You can create a function

function transpose(data) {

return (data[0] || []).map (function (col , colIndex) {
    return data.map (function (row) {
      return row[colIndex];
      });
    });
 }

and then pass the values obtained by .getValues() to that function..

var sheetData = transpose(sheet.getSheetByName('Fruit').getRange('A1:C2').getValues())

and check the log. See if that works for you?

answered Dec 27, 2018 at 11:27

JPV

27.4k4 gold badges37 silver badges50 bronze badges

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

Comments

tehhowch · Accepted Answer · 2018-12-27 14:38:51Z

Use the Google Sheets API, which allows you to specify the primary dimension of the response. To do so, first you must enable the API and the advanced service

To acquire values most efficiently, use the spreadsheets.values endpoints, either get or batchGet as appropriate. You are able to supply optional arguments to both calls, and one of which controls the orientation of the response:

const wb = SpreadsheetApp.getActive();
const valService = Sheets.Spreadsheets.Values;
const asColumn2D = { majorDimension: SpreadsheetApp.Dimension.COLUMNS };
const asRow2D = { majorDimension: SpreadsheetApp.Dimension.ROWS }; // this is the default

var sheet = wb.getSheetByName("some name");
var rgPrefix = "'" + sheet.getName() + "'!";

// spreadsheetId, range string, {optional arguments}
var single = valService.get(wb.getId(), rgPrefix + "A1:C30");
var singleAsCols = valService.get(wb.getId(), rgPrefix + "A1:C30", asColumn2D);

// spreadsheetId, {other arguments}
var batchAsCols = valService.batchGet(wb.getId(), {
  ranges: [
    rgPrefix + "A1:C30",
    rgPrefix + "J8",
    ...
  ],
  majorDimension: SpreadsheetApp.Dimension.COLUMNS
});
console.log({rowResp: single, colResp: singleAsCols, batchResponse: batchAsCols});

The reply will either be a ValueRange (using get) or an object wrapping several ValueRanges (if using batchGet). You can access the data (if any was present) at the ValueRange's values property. Note that trailing blanks are omitted.

You can find more information in the Sheets API documentation, and other relevant Stack Overflow questions such as this one.

Collectives™ on Stack Overflow

How to separate multiple columns from a range in an array?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related