Spreadsheet Non-Adjacent Column data

Question

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();

score 5 · Answer 1 · answered Apr 19 '18 at 04:07

5

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

answered Apr 19 '18 at 04:07

Rubén

34,714
9
70
166

It is appending data row wise, How can we achieve getting a Array with column as the Ranges mentioned? – Aashit Garodia May 07 '21 at 14:40
@AashitGarodia Please post a new question. – Rubén May 07 '21 at 14:47

score 2 · Accepted Answer · answered Mar 26 '16 at 14:20

2

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.

answered Mar 26 '16 at 14:20

Riyafa Abdul Hameed

7,417
6
40
55

Thanks a lot. This is working perfectly, Is there any limit to .push function mentioned above? I mean I might need to add upto 100 colums, I tried it with 2 columns as follows: var range1=sheet.getRange("A1:A19").getValues(); var range2=sheet.getRange("C1:C19").getValues(); var range3=sheet.getRange("D1:D19").getValues(); var range=[],i=-1; while ( range1[++i] ) { range.push( [ range1[i][0], range2[i][0], range3[i][0] ] ); } – HSPL Mar 28 '16 at 05:42
It could be possible, but would not be the most efficient way to do anything. You may as well use the ranges separately. Mark the answer as correct if it works. – Riyafa Abdul Hameed Mar 28 '16 at 08:07
I want to create a google line chart with column A containg dates and column B containing data. I will be obtaining data from a google spreadsheet.I want the chart to be dynamic such that if I mention data rows A1:A100 and B1:B100, I don't want the data to be plotted unless it is available in the sheet. i.e. if data is available only from A1:A50 and B1:B50, the chart should not contain data points A51:A100 and B51:B100. However when some data is written in row 51, the datapoint should be automatically available on the line chart for row 51. Thanks. – HSPL Mar 29 '16 at 04:43

score 1 · Answer 3 · answered Mar 28 '16 at 02:12

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

score 1 · Answer 4 · answered Jul 31 '18 at 12:03

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

`alert` is not a valid Google Apps Script core function - it is available only as a method of the UI class. — tehhowch, Jul 31 '18 at 12:21

Spreadsheet Non-Adjacent Column data

4 Answers4

Linked