CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jacob Heath
Jacob Heath

Reputation: 77

Copy columns to another spreadsheet

I am trying to copy specific columns from one sheet to another. I know I am a noob, but I have tried searching a lot for the answer.

as you can see, I am trying to copy the values from specific columns (in reality there is more), and then copy them to another spreadsheet in an order I control.

It's my own take on it. First, I am looping through the columns A, C, D, F, taking the values one by one, and for each one, I wish to paste them to the otherspreadsheet looping through the columns A, B, C, D as the target. But it gives me an error "TypeError: Cannot call method "getRange" of null. (line 11, file "test")"

function copySheet() { 
 var sourceSheet  =
 SpreadsheetApp.getActiveSpreadsheet().getSheetByName("source");`
 var destSheet  = 
 SpreadsheetApp.getActiveSpreadsheet().getSheetByName("target");`

 var columns_to_be_copied = [1, 3, 4, 6];
 var columns_to_be_pasted = [1, 2, 3, 4];
 var data = []
 for (i = 0; i < columns_to_be_copied.length; i++) {
  var sourcerange = sourceSheet.getRange(2, columns_to_be_copied[i], 
  sourceSheet.getLastRow()-1,1).getValues();
  for (t = 0; t < columns_to_be_copied.length; i++) {
   var targetrange = destSheet.getRange(2, columns_to_be_pasted[t], 
   destSheet.getLastRow()+1,1);
   targetrange.setValues(sourcerange);    
  }
 }
}

Also, I was wondering if anyone know of any way where I can getRange from A:AD (example) where the values in column D, which is formatted as datetime, is before =today()-90? I haven't been able to find the answer anywhere. Do anyone have a reference for this question?

Upvotes: 1

Views: 2867

Answers (1)

Brian
Brian

Reputation: 4354

If you're just copying data, you don't need to get the values. You can copy a range directly from one sheet to another. This modified function accomplishes your first goal of copying specific columns from a source into sequential columns in a destination sheet.

function copySheet() { 
 var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet7");
 var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet8");

 var columns_to_be_copied = [1, 3, 4, 6];

 for (var i = 0; i < columns_to_be_copied.length; i++) {
  var sourcerange = sourceSheet.getRange(2, columns_to_be_copied[i], sourceSheet.getLastRow()-1,1)
  sourcerange.copyTo(destSheet.getRange(1,(i+1)))
 }
}

As to your second question, you can use a helper function to check dates:

  // count back n days
  Date.prototype.modDays = function(days) {
    var date = new Date(this.valueOf());
    date.setDate(date.getDate() - days);
    return date;
  }

In your function, you would use something like (untested):

var today = new Date()
var future = today.modDays(90)

Upvotes: 3

Related Questions