Non-contiguous column copy from one spreadsheet to another in google apps

Question

How would I format non-contiguous columns to be copied to another sheet? I know (thanks Serge) that you can do contiguous columns with the following!

.getRange("A2:C")

say I need to do column A, C, K, AD, BB for example.

Is there a simpler way than assigning all columns you need different variables, getting them all individually, and putting them in the sheet you need?

Thanks for the help!

Answer 1

My answer is really a little redundant/academic, as the =QUERY() function will allow you to do the what you want. eg =QUERY(A1:D31,"Select C, A, B") I've also given an example of using it on the example sheet (linked below). QUERY can also be used with =IMPORTRANGE() explanation from @AdamL. I've included that functionality in my function too to demonstrate. Finally, my function can be used in a spreadsheet, or in a script with no modifications. There are examples of using QUERY(IMPORTRANGE()) and my function copyColumns at my example spreadsheet.

---

I have included some validation so that the function can be used by less technical folks using spreadsheets. Hopefully it's useful to you too. I have made liberal use of JS functionality including RegExp, Array.map, and the Conditional Operator please ask for any clarity you need in the comments here.

The basics: It takes a string of the form "SheetName!A,C,B" where the SheetName! is optional. And it can take a starting row, with a default of 1. It can also deal with non local spreadsheets by being given a sheetKey (with or without starting row).

For example: =copyCoumns("MyDataSheet!C,A,W",8) Will copy the columns C, A and W in that order starting with row 8.

Here's the function! Enjoy!

function copyColumns(sourceRange,start,sheetKey) {
  // Initialize optional parameter
  if(!sheetKey && typeof start !== "number") {
    sheetKey = start;
    start = 1;
  } else {
    start = start || 1;
  }
  // Check SourceRange Input
  var inputRe = /^((.*?!)(?=[a-z],?|[a-i][a-z]))?[a-i]?[a-z](,[a-i]?[a-z])*$/i;
  if(!inputRe.test(sourceRange))
    throw "Invalid SourceRange: " + sourceRange;

  // Check Start Row
  if(typeof start !== "number")
    throw "Starting row must be a number! Got: " + start;
  if(start % 1 !== 0)
    throw "Starting row must be an integer! Got: " + start;
  if(start < 1)
    throw "Starting row can't be less than 1! Got: " + start;

  // Get the Source Sheet
  try {
    var ss = sheetKey
           ? SpreadsheetApp.openById(sheetKey)
           : SpreadsheetApp.getActiveSpreadsheet();
  } catch(err) {
    throw "Problem getting sheet" + sheetKey + " - " + err;
  }
  var sheetName = sourceRange.match(/^.*?(?=!)/);
  var sheet = sheetName
            ? ss.getSheetByName(sheetName[0])
            : ss.getActiveSheet();

  // Check that everything is still valid
  if(!sheet)
    throw "Could not find sheet with name: " + sheetName;
  if(start > sheet.getLastRow())
    throw "No data beyond row: " + start + " Last row: " + sheet.getLastRow();

  // Get the values
  var lastCol = sheet.getLastColumn();
  var lastRow = sheet.getLastRow()-start+1;
  var values = sheet.getRange(start,1,lastRow,lastCol).getValues();

  // Get the desired columns from the string
  var desiredColMatch = sourceRange.match(/([a-i]?[a-z](,[a-i]?[a-z])*)$/i);
  var desiredColumns = desiredColMatch[0].toUpperCase().split(",");

  // In case the column we are trying to grab doesn't exist in the sheet
  var lastColId = sheet.getMaxColumns() - 1; // Array is 0 indexed, Sheet is 1

  // Get the numerical values of the passed in Column Ids
  var columns = desiredColumns.map(function(colId){
    var num = colId.length - 1; // 0 or 1
    var colNum = colId.charCodeAt(num)-65+num*26*(colId.charCodeAt(0)-64);
    if(colNum > lastColId)
      throw "Invalid Column: " + colId + " - Column not in: " + sheetName;
    return colNum;
  });

  //Map the values to a new array of just the columns we want
  return values.map(function(row){
    return columns.map(function(col){
      return row[col]
    })
  });
}

Answer 2

Probably not simpler, but I would say better performance, to get one big range encompassing all the columns you need with .get(Data)Range().getValues(), use Javascript to strip down the array to only the columns you need, and use setValues() to paste the values in one hit:

function copyValuesOnly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var values = ss.getSheetByName('Source').getDataRange().getValues();
  values.shift(); //remove header row
  var columns = [0, 2, 10, 29, 53]; //array of zero-based indices of required columns
  var output = [];
  for (var i = 0, length = values.length; i < length; i++) {
    output[i] = [];
    for (var j = 0, width  = columns.length; j < width; j++) {
      output[i][j] = values[i][columns[j]];
    }
  }
  ss.getSheetByName('Destination').getRange(2, 1, length, width).setValues(output);
}

The issue would be if you required copying formats and formulae as well, in which case the best option might be copy-pasting each column individually, as you mentioned.