Copy data from one sheet to another spreadsheet ex

问题:

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

回答1:

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

回答2:

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