CODEWITHSUNDEEP
google-apps-script
Eddylol
Eddylol

Reputation: 13

Google apps script select specific columns to upload into target sheet

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!

Upvotes: 1

Views: 6698

Answers (1)

ziganotschka
ziganotschka

Reputation: 26816

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.

Additional information:

If you would like to paste your data into an adjacent range starting with column 1, one possible way based on the sample above would be to redefine the loop as following:

  selectedColumns.forEach(function(column,i ){
    let data = sheet.getRange(1,column, sheet.getLastRow(),1).getValues();
    sheetRawData.getRange(1,1+i, sheet.getLastRow(), 1).setValues(data);    
  })

Thereby the counter variable i is introduced and the notation of the destination column is modified from column to 1+i, meaning that the columns will be set starting with column 1 and increasing by 1 for each loop iteration.

Upvotes: 1

Related Questions