0

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 );
}
sudo bangbang
  • 27,127
  • 11
  • 75
  • 77
Vũ Lê Trung
  • 1
  • 1
  • 4
  • 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 Apr 01 '16 at 04:03
  • 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 Nov 23 '16 at 12:55
  • @sudobangbang How can I make the range like this: (" A4:A - U4:U") ?? – MSD Nov 23 '16 at 16:15

2 Answers2

2

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

sudo bangbang
  • 27,127
  • 11
  • 75
  • 77
  • It worked for desire selected columns but in destination sheet it pasted into same column. How can I reorder the destination into the range A:N? One more question, is there some way this function modifies to copy specific rows that meet condition? – Vũ Lê Trung Apr 01 '16 at 08:23
  • @Vũ Lê Trung: It's just a matter of changing argument in getRange method of destination. I've made changes in code so that it is easy to instruct which column on source to map to which column on destination. – sudo bangbang Apr 04 '16 at 04:38
-1
//Copying data from sheet 2 to sheet 1

var sourceSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");

var destSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  sourceSheet.getRange("E:G").copyTo(destSheet.getRange("E:G"), {contentsOnly:true});
Ajil O.
  • 6,562
  • 5
  • 40
  • 72
Vipin Gupta
  • 1