1

I'm working in google apps script. If I start with a range like range A1:E5, that's a 5x5 array. I want to return range C1:D5, a 5x2 array. Start with a 2d array and return only selected 'columns'. That's basically it. I think it's a fundamental operation, but I'm really struggling. I have my code below, but I'm open to any options that use arrays (not ranges, so as to avoid pinging the server unnecessarily). Note that I do want to be able to pass an array parameter for columns, so [2,3,4] or [2] or [3,4], not just a single or static value. Thanks for any help.

/**
 * extracts selected 'columns' (2nd dimension) from 2d array
 *
 * @arr {array} larger 2d array to be subset
 * @cols {array} subset of columns, eg, [3,4]
 * @return 2d array with only selected cols
 * @customfunction
 */
function getCols(arr,cols) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  

  var arrRows = [];
  var arrCols = [];
  
  for(var r=0;r<arr.length;r++){
    arrCols = [];// reset snippet
  for(var c=0;c<cols.length;c++){
    arrCols.push([arr[r][cols[c]]]); // iterate to make 1xc array snippet
  }
    arrRows[r].push(arrCols); // iterate to add each row
  }
  
  return arrRows; // return new arr subset that only has requested cols
}
Jason Torpy
  • 39
  • 2
  • 9
  • I have to apologize for my poor English skill. In order to correctly understand about your question, can you provide the sample input and output values you expect? – Tanaike Jun 24 '20 at 23:43
  • I think TheMaster below has a good answer with some examples. It uses different methods than I'm used to though so I'm still reviewing it. – Jason Torpy Jun 25 '20 at 21:31
  • Thank you for replying. – Tanaike Jun 25 '20 at 22:20

2 Answers2

7

Use Array#filter with map:

/**
 * extracts selected 'columns' (2nd dimension) from 2d array
 *
 * @param {Object[][]} arr larger 2d array to be subset
 * @param {Number[]} cols Indexes of subset of columns needed starting from 1 eg, [3,4]
 * @return {Object[][]} 2d array with only selected cols
 * @customfunction
 */
function getCols(arr,cols) {
  return arr.map(row =>
    row.filter((_,i) => cols.includes(++i)))
}
console.info(getCols([[1,2,3],[4,5,6]],[1,3]));
console.info(getCols([[1,2,3],[4,5,6]],[2,3]));

Related:

What does the range method getValues() return and setValues() accept?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • Thanks. I like using the arrow functions. this is apps script, but I think the new v8 they published will run those.I'm not sure how the _ works in this. Also, if I understand, this takes an array and pulls out rows 1 and 3. My intention to get columns. I appreciate everything going on here, but I'm not sure I get all of it. Could you walk through a few of the steps for me? – Jason Torpy Jun 25 '20 at 14:17
  • @JasonTorpy Have you practiced the links, I've provided? That's step 1. It'll work in apps script v8. `_` is nothing. You can use `col` instead of `_`(but since that variable is unused anyway, I used `_`). It gets columns 1 and 3, as you can see by running the snippet. – TheMaster Jun 25 '20 at 15:24
  • I appreciate that it works. It just uses functions I never use, like map, filter, includes, and the arrow function. Even running things outside a function is not something I do. I'm self taught and have made tons of money coding apps script and VBA and other stuff, but I'm self-taught so some easy stuff I've missed over the years. I'll just dig in, but I'll have to figure out what I'm looking at to use this for what I need. Thanks for sure. – Jason Torpy Jun 25 '20 at 21:30
  • follow-up question while I have you: why console.info instead of Logger.log? Any reason or does it matter? – Jason Torpy Jun 25 '20 at 21:32
  • @JasonTorpy Most people here are self taught including me. All I'm asking is to do your homework. Read the links and practice that part of coding. If you're unfamiliar, get familiar with it. Then if you don't understand, ask a question, I'll explain. It's easier that way for both of us. About running things outside the function=> it's needed to run the snippet here. It's not needed in the actual coding scenario, where the function is called from the sheet with arguments. Here, I'm calling it from outside the function: "simulating" a 2D array and a 1D number array as per your jsdoc. – TheMaster Jun 26 '20 at 05:03
  • @JasonTorpy Prefer `console` class as it is more reliable. But as I already said, it's done to simulate/prove that it works here and not actually needed – TheMaster Jun 26 '20 at 05:05
  • I feel like I'm doing that. I help a lot of people too so I'm in your position more often than not. I am familiar with the mapping and arrow functions, but have just never been that good at it, hence the request for clarification. There's just a happy medium between helping and explaining. I was just seeking that happy medium. I'm good for now though. Thanks for your help. – Jason Torpy Jun 26 '20 at 23:38
  • @JasonTorpy If you've done your homework, and if you still have a nagging doubt/uncertainty, I'll be happy to clarify it for you. I'm just not sure what part of the function to explain it to you. For eg, if you don't understand why we're passing functions to map/filter, I would refer you to [callbacks](https://stackoverflow.com/questions/824234/what-is-a-callback-function)(this part took me a lot of time to get my head around). But I can't write a tutorial here. Stacoverflow is a 1 question-1 answer styled site. It's not meant to be a forum nor can it be used as one. – TheMaster Jun 27 '20 at 05:05
1
function myfunc() {
  const ss=SpreadsheetApp.getActive();
  var vs=ss.getActiveSheet().getDataRange().getValues().map(function(r){return [r[2],r[3]];});
  ss.getActiveSheet().getRange(ss.getActiveSheet().getLastRow()+1,1,vs.length,vs[0].length).setValues(vs);
}

This will output columns C and D right below the current data

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks. this does work for static columns, but my intent is to have a variable number of columns. It's also a bit hard to follow all squished together. Do you have any ideas for a variable-column solution? – Jason Torpy Jun 25 '20 at 14:14