CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
HSPL
HSPL

Reputation: 55

Spreadsheet Non-Adjacent Column data

I want data from a spreadsheet using app script. I am trying to get data from multipe rows of two non-adjacent colums, but I am unable to do so using following syntax. Say A1 to A19 and C1 to C19. Please tell me if something is wrong with the code. Thanks.

data = sheet.getRange("A1:A19","C1:C19").getValues();

Upvotes: 5

Views: 3349

Answers (4)

jcom
jcom

Reputation: 101

You can get all range and remove the columns you don't need. Addapted from Alex Wayne (Deleting a column from a multidimensional array in javascript)

var table = [
    ['a', 'b', '1', '5', '8'],
    ['c', 'd', '2', '6', '9'],
    ['f', 'g', '3', '7', '10']
];

var removeCol = function(arr, col1Index, col2Index) {
    for (var i = 0; i < arr.length; i++) {
        var row = arr[i];
        row.splice(col1Index, 1);
        row.splice(col2Index, 1);
    }
}

// remove more than one column - can remove as many columns as needed
// ATENTION: spreadsheet column start in 1 and arrays start in 0
// start removing from last to first columns
removeCol(table, 4, 2);

alert(table);

Upvotes: 1

Wicket
Wicket

Reputation: 38219

Recently the Class RangeList was added to the Google Apps Script Spreadsheet Service, so now it's possible to make some operations on non-adjacent cells/ranges.

Instead of

data = sheet.getRange("A1:A19","C1:C19").getValues()

You could use something like

var data = [];
var ranges = sheet.getRangeList(["A1:A19","C1:C19"]).getRanges();
for(var i = 0; i < ranges.length; i++){
  data = Array.concat(data, ranges[i].getValues());
}

Upvotes: 6

hazymat
hazymat

Reputation: 424

Presumably once you have extracted the data you want, you'll loop through it and do different things with it.

You've used .getRange, which returns an object of type "range", and .getValues, which returns a 2D array of values within that range object.

So why not just get the whole range then loop through only the values you need?

data = sheet.getRange("A1:C19").getValues();
for (i = 0; i < data[0].length; i++) {
  // do something with data[0][i]
  // do something with data[2][i]
}

Upvotes: 1

Riyafa Abdul Hameed
Riyafa Abdul Hameed

Reputation: 7973

You cannot get data from two columns which are not next to each other into a single array by a single call. But you can do something as follows:

var range1=sheet.getRange("A1:A19").getValues();
  var range2=sheet.getRange("C1:C19").getValues();
  var range=[],i=-1;
  while ( range1[++i] ) { 
  range.push( [ range1[i][0], range2[i][0] ] );
}

where range will have content from both columns. The getRange() method you have used is incorrect.

Upvotes: 2

Related Questions