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

回答1:

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.

回答2:

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

回答3:

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

回答4:

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