Google Apps Script - Copy rows from one sheet containing today's date into another

0
votes

What I'm trying to do is within one spreadsheet, look at sheet "Source", if a cell in column B contains today's date, then copy cells between A:B for that cell and paste onto next empty row in sheet "Destination".

Repeat for all cells containing today's date.

This works if I'm looking for a text but essentially I'm trying to make this run automatically everyday when the date changes and I can't get the date function to work properly.

Essentially at the turn of each day it would copy today's data from another automated sheet, and paste it in the next row down from yesterday's data in another sheet.

I'm quite new to all of this and any help would be greatly appreciated.

function copy_test() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Source"); //Source sheet
  var ds = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Destination"); //Destination sheet
  var testrange = ss.getRange('B:B'); //Column to check for today's date
  var testvalue = (testrange.getValues()); //What value to check
  var today = Utilities.formatDate(new Date(), 'GMT-0', 'dd/MM/yyyy');
  var data = [];
  var j = [];

  //Condition to check in B:B, if true, copy the same row to data array 
  for (i=0;i<testvalue.length;i++) {
    if (testvalue[i] == today) {
    data.push.apply(data,ss.getRange(i+1,1,1,3).getValues());
  //Copy matched ROW numbers to j
    j.push(i);
}  
}
  //Copy data array to destination sheet
  ds.getRange(ds.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}

The error I get is:

TypeError: Cannot read property "length" from undefined. (line 20, file "First Test")

Picture of the type of data I want to copy

2
google-apps-scriptgoogle-sheets
I doubt that you ever got any data because today in your code included hours, minutes, seconds and milliseconds and it's doubtful that any of your dated matched exactly. – Cooper

2 Answers

0
votes

Try this:

function copy_test() {
  var ss=SpreadsheetApp.getActive();
  var ssh=ss.getSheetByName("Source");
  var dsh=ss.getSheetByName("Destination");
  var rg=ssh.getRange(1,1,ssh.getLastRow(),3);//I just took the first three columns instead
  var vA=rg.getValues();
  var dt=new Date();
  var today=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();//midnight
  for(var i=0;i<vA.length;i++) {
    var d=new Date(vA[i][1]);//column B
    if (new Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf() == today) {
      dsh.appendRow(vA[i]);
    }  
  }
}
0
votes

In case anyone comes across this problem in the future. I messed around with the original code some more and managed to get it to work:

function copyrange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Source'); //source sheet
  var testrange = sheet.getRange('B:B');
  var testvalue = (testrange.setNumberFormat("@").getValues());
  var ds = ss.getSheetByName('Destination'); //destination sheet
  var data = [];
  var j =[];
  var dt = new Date();
  var today = Utilities.formatDate(new Date(), 'GMT-0', 'dd/MM/yyyy')

  //Condition to check in B:B, if true, copy the same row to data array 
  for (i=0;i<testvalue.length;i++) {
    if (testvalue[i] == today) {
    data.push.apply(data,sheet.getRange(i+1,1,1,3).getValues());
  //Copy matched ROW numbers to j
    j.push(i);
}  
}
  //Copy data array to destination sheet
  ds.getRange(ds.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}

Specifically var testvalue where I added in setNumberFormat for the dates as text because that's the only way it would work for if (testvalue[i] == today)

Thanks @Cooper for taking a shot at this.