Use google script to put 2 columns into a single multi dimensional array

Question

I'm looking for a way to take 2 columns in a google spreadsheet and merge them into a single array in hopes that I can take these 2 columns and use setValues on a new sheet.

Why?

I'm eventually taking 2 different sheets and basically doing a large scale vlookup and transferring all results and desired columns into a single, new sheet. I can get the full dataRange, loop through each array, grabbing the values I want and pushing them to a new array. But is there an easier way? If I can look through just row1 and get the headers and their index, can I just put all of column A and column D in a multi-dimensional array?

Example

Header1 | H2 | H3

I want H1 and H3 and their rows so I can put them in a new sheet as such

Multi-Dimensional Array:

[ [H1, H3], [dataH1,dataH3] ]

Current Code

  var freqArr = new Array(); //Array with sheet data
  var myArray = new Array(); //Blank array to house header index
  var freqSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KEY_test_test');
  var freqData = freqSheet.getDataRange(); //all data
  var freqNumRows = freqData.getNumRows(); //number of rows
  var freqNumCol = freqData.getNumColumns(); //number of columns
  freqArr = freqSheet.getRange(1, 1, freqNumRows, freqNumCol).getValues();
  for (i = 0;i<1;++i){
    for (j = 0;j<freqNumCol;++j){
      if (freqArr[i][j].toString() == 'Header1' || freqArr[i][j].toString() == 'Header3'){
      myArray.push([j]);
      }
    }
  }
  Logger.log(myArray);

Where I'm Stuck

What I'm doing right now is looping through the first row to get the header indexes I want (should look like this [ 0, 2 ]) but all that is returning in my log is []. I plan to use this array of indexes to loop through my freqData and grab the indexes of each nested array.

Any advice would be great. I'm just starting to learn google script and I'm teaching myself. Thanks

UPDATE TO CODE:

It turns out that .toString() == 'Header1' will not return a match but after more google fu, I found .toString().match('Header1') == 'Header1' will return what I need. See below for update

  var freqArr = new Array(); //Array with sheet data
  var myArray = new Array(); //Blank array to house header index
  var freqSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('KEY_test_test');
  var freqData = freqSheet.getDataRange(); //all data
  var freqNumRows = freqData.getNumRows(); //number of rows
  var freqNumCol = freqData.getNumColumns(); //number of columns
  freqArr = freqSheet.getRange(1, 1, freqNumRows, freqNumCol).getValues();
  for (i = 0;i<1;++i){
    for (j = 0;j<freqNumCol;++j){
      if (freqArr[i][j].toString().match('Header1') == 'Header1' || freqArr[i][j].toString().match('Header3') == 'Header3'){
      myArray.push(j);
      }
    }
  }
  Logger.log(myArray);

will return [ 0.0 , 2.0 ].

But still, my question remains, is there a faster way to get 2(n) columns that are not side-by-side and put them into an array so that you can use .setValues?

Wicket · Accepted Answer · 2016-11-07 19:41:22Z

Answer

But still, my question remains, is there a faster way to get 2(n) columns that are not side-by-side and put them into an array so that you can use .setValues?

Yes, there are many ways. One of them is the use of a JavaScript method: array.prototype.forEach()

Code

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var array = [];
  data.forEach(function(row){
      array.push([row[0],row[5]]);
    });
  sheet.getRange(1,10,array.length,2).setValues(array);
}

Explanation

Get the active sheet

  var sheet = SpreadsheetApp.getActiveSheet();

Get the all the values on sheet

  var data = sheet.getDataRange().getValues();

Initialize a variable to hold the array

  var array = [];

Get the values of the first and sixth columns (A and F) (zero based index)

  data.forEach(function(row){
      array.push([row[0],row[5]]);
    });

Return the values to a range starting on J1 and ending on column K and the required row (one based index)

  sheet.getRange(1,10,array.length,2).setValues(array);

Karl_S · Accepted Answer · 2016-11-07 14:57:56Z

1

Take a look at the getRowsData() function on the Simple Mail Merge tutorial. It will get all the data in a sheet and return it as objects. You could then access the data as myData[i].header1 It will remove spaces and "normalize" the header. So a header such as My Header name will be myData[i].myHEaderName

You could limit the returned data to only the columns you need if you wish.

answered Nov 7, 2016 at 14:57

Karl_S

3,5922 gold badges24 silver badges38 bronze badges

Collectives™ on Stack Overflow

Use google script to put 2 columns into a single multi dimensional array

Why?

Example

Current Code

Where I'm Stuck

UPDATE TO CODE:

2 Answers 2

Answer

Code

Explanation

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Why?

Example

Current Code

Where I'm Stuck

UPDATE TO CODE:

2 Answers 2

Answer

Code

Explanation

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related