Google apps script select specific columns to upload into target sheet-CodePudding

Total beginner here. I am trying to select columns A,B,C from spreadsheet B which has around 40K rows and 25 columns, to spreadsheet A which is a blank document. So, I have two spreadsheets. This code works fine but I want to select only the columns I need (columns including all the data, not just the headers).

function importdata() {  
let ss = SpreadsheetApp.openById("ID"); //SPREADSHEET TO COPY FROM  
let sheet = ss.getSheetByName("name"); //SHEET (TAB) TO COPY FROM  
sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();

let ssThis = SpreadsheetApp.getActiveSpreadsheet();
let sheetRawData = ssThis.getSheetByName("name"); //SHEET IN THE TARGET SPREADSHEET TO COPY TO
sheetRawData.getRange(1,1,sheetRawData.getLastRow());
}

Any idea as to how I can add this into the code above? Many thanks!

CodePudding user response：

You can create an array of selected columns and get / set the values for the ranges corresponding to those columns:

function importSelectedColumns() {  
  
  let selectedColumns = [1, 3, 5];
  
  let ss = SpreadsheetApp.openById("ID"); //SPREADSHEET TO COPY FROM  
  let sheet = ss.getSheetByName("name"); //SHEET (TAB) TO COPY FROM  
  let ssThis = SpreadsheetApp.getActiveSpreadsheet();
  let sheetRawData = ssThis.getSheetByName("name"); //SHEET IN THE TARGET SPREADSHEET TO COPY TO
  
  selectedColumns.forEach(function(column){
    let data = sheet.getRange(1,column, sheet.getLastRow(),1).getValues();
    sheetRawData.getRange(1,column, sheet.getLastRow(), 1).setValues(data);    
  }) 
}

Explanation:

forEach() is one of several possibilities to loop through several columns subsequently.
getRange(row, column, numRows, numColumns) lets you specify the number and amount of columns to retrieve.
setValues(values) allows you to import the data from one range into another one.
Note: The range dimensions of the origin and destination ranges need to match.

CodePudding user response：

I'd propose the native approach with range.copyTo(range):

function copy_cols() {
  var cols = ['a', 'c', 'd', 'x'];                // columns to copy

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src_sheet = ss.getSheetByName('Sheet1');    // source sheet
  var dest_sheet = ss.getSheetByName('Sheet2');   // destination sheet

  for (var col of cols) {
    var range = col   ':'   col;                  // a -> 'a:a', b -> 'b:b', etc
    var src_range = src_sheet.getRange(range);
    var dest_range = dest_sheet.getRange(range);
    src_range.copyTo(dest_range);
  }
}