CODEWITHSUNDEEP
google-apps-script
Simon Breton
Simon Breton

Reputation: 2876

How to select only specific columns when copying data from one sheet to another?

I'm copying some data from one sheet to another sheet with just few lines of code :

for (var i = date2.length; i < date1.length; i++){
    var rng1Array = sheet1.getRange(i+1,1,1,sheet1.getLastColumn()).getValues();
    sheet2.getRange(i+1,1,1,sheet1.getLastColumn()).setValues(rng1Array);
                }
              }

with the variable rng1Array I'm selecting entire row of data, however I would like to be able to select only some elements in each row.

What is the best way to do this ? Do I have to select each cells one by one by using something like this :

sheet1.getRange(1, 1, sheet1.getLastRow(),sheet1.getLastColumn()).getValues()[x][x])

In other words, if I have the following data set :

Data1 Data2 Data3 Data4 Data5 Data6

What is the best way to only select Data2, Data4 and Data6 for example ?

If needed here is the full script :

function Copy_data() {

var sheet1 = SpreadsheetApp.openById("ID").getSheetByName("Sheet1");
var sheet2 = SpreadsheetApp.openById("ID").getSheetByName("Sheet2");

var date1 = sheet1.getRange(1, 1, sheet1.getLastRow()).getValues(); 
var date2 = sheet2.getRange(1, 1, sheet2.getLastRow()).getValues(); 

  for (var i = date2.length; i < date1.length; i++){
        var rng1Array = sheet1.getRange(i+1,1,1,sheet1.getLastColumn()).getValues();
        sheet2.getRange(i+1,1,1,sheet1.getLastColumn()).setValues(rng1Array);
    }
  }

Upvotes: 1

Views: 3681

Answers (1)

Cooper
Cooper

Reputation: 64082

Although this may be a bit more trouble in terms of writing, it is quite a bit more versatile. You can increment your column index in the standard j++ or perhaps j += 2 or you can use the standard j++ and have it be an index to another array which allows you to easily manipulate the order and selection of output columns. In the end the size of the destination range is determined by the final size of the destination array. So it meets the requirements for setValues and should work pretty realiably. I was using only four columns of data so you may wish to modify the size and number of columns in colA to select the columns of input data that you want. Also you could avoid the need for subtracting off 1 by simply thinking of srngA indices rather that column...Your call.

function Copy_data() 
{
  var src = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("source");
  var des = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("destination");
  var srng = src.getDataRange(); 
  var srngA = srng.getValues(); 
  var drngA = [];
  var colA = [1,3,2,4];//indirect data column selection
  for (var i = 0; i < srngA.length;i++ )
  {
    var k = 0;
    drngA[i]=[];
    for(var j=0;j<srngA[i].length;j++)
    {
        drngA[i][k++] = srngA[i][colA[j]-1];
    }
    drng = des.getRange(1,1,drngA.length,drngA[0].length);//destination array controls size of destination range.
    drng.setValues(drngA);
  }
}

I'm guessing with a little thought you can take this much further in the direction of your thinking.

Upvotes: 1

Related Questions