Copy data from one sheet to another spreadsheet exclude specific columns

Question

I found the script and have successfully copy values from master sheet to another sheet named "Cooked". The trouble is it copies all the data from master sheet, I just want to copy values from specific columns, like column A,C,D,F(Except B&E) and auto arrange to destination sheet.

My coding exp is limited but I'm able to learn(copy) to understand it from you :)

function copyTo(source,destination) {
  var sourceSheet = source.getSheet();
  var destSheet = destination.getSheet();
  var sourceData = source.getValues();
  var dest = destSheet.getRange(
    destination.getRow(),        // Top row of destination
    destination.getColumn(),     // left col of destination
    sourceData.length,           // # rows in source
    sourceData[0].length);       // # cols in source (elements in first row)
  dest.setValues(sourceData);
  SpreadsheetApp.flush();
}

function copySheet() {
   var sourceSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Total");
   var destSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cooked");

  var source = sourceSheet.getRange("A:N");
  var destination  = destSheet.getRange("A:N");
  copyTo(source,destination );
}

You can see it takes the range A:N. You'll only have to change this part to get desired result. In you case, you might have to do this multiple times. Also to re-arrange in destination sheet, select range as you desire. — sudo bangbang
Does does apply when Google Sheets is openend or when you hit a specific menu-item? So basically what I am asking is, is this automated or manual? — MSD
@sudobangbang How can I make the range like this: (" A4:A - U4:U") ?? — MSD

sudo bangbang sudo bangbang · Accepted Answer · 2016-04-01T05:29:40

In your copySheet function, you can see it takes Range A:N for copying. If you change this part, you can copy desired ranges.

As you wanna copy a set of non-contiguous columns, I'd suggest something like this.

function copySheet() {
  var sourceSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Total");
  var destSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cooked");

  var columns_to_be_copied = ['A', 'C', 'D', 'F'];
  var columns_to_be_pasted = ['A', 'B', 'C', 'D'];

  for (column in columns_to_be_copied) {

    var copy_range_string = columns_to_be_copied[column] + ':' + columns_to_be_copied[column];
    var paste_range_string = columns_to_be_pasted[column] + ':' + columns_to_be_pasted[column];

    var source = sourceSheet.getRange(copy_range_string);
    var destination  = destSheet.getRange(paste_range_string);
    copyTo(source,destination );
  }
}

We store all columns to be copied in a variable, iterate through them and generate copy_range_strings and paste_range_strings of the formant A:A, C:C etc. Then call copyTo method on those ranges. Now you can edit columns_to_be_copied and columns_to_be_pasted to make changes on data from which column should go to which column

Copy data from one sheet to another spreadsheet exclude specific columns

2 Answers