admin管理员组

文章数量:1401673

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?

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?

• javascript
• arrays
• google-apps-script
• google-sheets

Share Improve this question Follow edited Nov 7, 2016 at 19:40 Wicket 38.7k99 gold badges8080 silver badges194194 bronze badges asked Nov 7, 2016 at 14:43 GrantGrant 90311 gold badge1717 silver badges2525 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 7

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

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.

本文标签： javascriptUse google script to put 2 columns into a single multi dimensional arrayStack Overflow