How can I copy information from one sheet to another?

Question

I am trying to write a script to copy information from one sheet to another in Google Sheets, as a daily update. The information is by row (ex. row 5 has all the information I want to copy over, with column A being the date entered) If the date on an entry (column A) does not match today's date, then the whole row needs to be transferred over to another sheet.

I am having trouble comparing the cell that contains the date the information was entered to today's date and then capturing that cell's row number, and then setting up variables to copy over.

On the new sheet, it needs to scan the range for the next empty row, and then paste the information in the next empty row.

Currently I have it set up for one cell to copy/paste over, but I will set it up for a whole row of cells.

UPDATE: I have figured out how to copy the information over. My rows to search for start on the Copy From tab in row 5, and my code can search for and compare the dates the information was entered in Column B with today's date. The code with then copy the information in that row to a new Copy To tab.

I now would like to figure out how to delete the information from the Copy From tab, so that it only exists in my spreadsheet once, either on the Copy From tab (if the information entered is on today's date or in the future) or on the Copy To tab (if the information entered is from before today's date). I have added a line of code at the bottom to do this, but when I run my code it copies some of the information over, while deleting other pieces, and copying over information from today's date as well. Any help with this?

function funcname() {
  var Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy From");
  var Sheet2 =  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy To");        
  var ColumntoSearch = 2;
  var LastRow = Sheet.getLastRow();

  //Gets column to search for dates to compare to Today
  var Range = Sheet.getRange(5, ColumntoSearch, LastRow, 1);
  var Values = Range.getValues();

  //Sets the amount of data to copy over
  var NumberofColumns = 27;
  var NumberofRows = 1;

  var DestRow = Sheet2.getLastRow()+1; 

  //Compares all the Dates in the Copy From tab to Today
  var dt=new Date();
  var dt1=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate());
  var dv=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  var d=0;
  for (var i=0;i+d<Values.length;i++){

    //If dates are less than today, they will be copied over to the Copy To tab
    if(new Date(Values[i+d][0]).valueOf()<dv){
      var RangetoCopy = Sheet.getRange((i+5), 1, NumberofRows, NumberofColumns);
      var DestRange = Sheet2.getRange(DestRow, 1, NumberofRows, NumberofColumns);
      DestRow++;
      RangetoCopy.copyTo(DestRange);


      Sheet.deleteRows(i+7);
     }
   }
 }

Cooper Cooper · Accepted Answer · 2019-04-12T18:29:15

Try this:

This function reads in all of the dates at one time and runs through a loop comparing all of the dates to today at 12:00 midnight. Anything > or = todays date is removed from the output.

function copyDateNotEqualToToday() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Copy From");
  var sh2=ss.getSheetByName("New Sheet");
  var rg=sh.getRange(5,1,sh.getLastRow()-4,1);
  var vA=rg.getValues();
  var dt=new Date();
  var dt1=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate());
  var dv=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  var d=0;
  for (var i=0;i+d<vA.length;i++){
    if(new Date(vA[i+d][0]).valueOf()>=dv){
      vA.splice(i+d,1);
      d++;
    }
  }
  sh2.getRange(1,1,vA.length,1).setValues(vA);
}

My input sheet:

My output sheet:

What I was doing is a little different that what you are doing. I was taking all of the elements in what you call 'Values' array that were > or = the current day and removing them from the array. After looping through all of the rows I then copied all of the values into the destination sheet with a ... to use your terms setValues(Values) and I was not deleeting anything from the source sheet.

However, what you are doing is moving data from source to destination so you want to be able to delete the current row as your looping. So here's that function.

function copyIfLessThan() {
  var Sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy From");
  var Sheet2=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy To");        
  var Range=Sheet.getRange(5,2,Sheet.getLastRow(),1);
  var Values=Range.getValues();
  var dt=new Date();
  var dv=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  var d=0;
  for(var i=0;i<Values.length;i++) {
    if(new Date(Values[i][0]).valueOf()<dv){
      var RangetoCopy=Sheet.getRange(i-d+6,1,1,27); 
      var DestRange=Sheet2.getRange(Sheet2.getLastRow()+1,1,1,27);
      RangetoCopy.copyTo(DestRange);
      Sheet.deleteRows(i-d+6);//removing rows not elements of Values Array
      d++;
    }
  }
}

How can I copy information from one sheet to another?

1 Answers