How do I filter for yesterday's date in google scripts?

Question

I'm scripting google sheets to filter for and transfer data from one workbook to another. I'd like to the transfer to be conditional based off yesterday's date (in column K).

A user provided me with code in another post that I've tweaked a little (included below) and while it is executing the copy-paste, the date filtering is off. The code keeps pulling data from 12/7/2019 (as of today on 1/5/2020) when I'd like it to pull from yesterday. Yesterday, in this case, is 1/4/2020.

I've tweaked below based off several different stackoverflow posts but can't get it to pull data from yesterday and the user who posted the response has gone silent (I've commented and had to delete comments from a couple days ago).

Can anyone help me grab yesterday's rows from Workbook 1?

function runOne() { 
  var ss=SpreadsheetApp.openById('Workbook 1 ID');
  var tsh=ss.getSheetByName('sheet1');
  var ss2=SpreadsheetApp.openById('Workbook 2 ID');
  var tsh2=ss2.getSheetByName('sheet1');
  var lastRow=tsh.getLastRow();
  var timestamps=tsh.getRange(1,11,lastRow,1).getValues();
  var d = new Date();
  d.setDate(d.getDate() - 1); // get the date from yesterday
  var yesterdayDate=new Date(d);   
  for(var i=lastRow;i>1;i--){              // get the last row from yesterday
    if(new Date(timestamps[i-1][0]).getDay()==yesterdayDate.getDay()){
     var lastRowYesterday=i;
      break;
    }
  }
  for(var j=lastRowYesterday-1;j>1;j--){  // get the first row from yesterday
    if(new Date(timestamps[j-1][0]).getDay()!=yesterdayDate.getDay()){
      var firstRowYesterday=j+1;
      break;
    }
  }
  var rowNumber=lastRowYesterday-firstRowYesterday+1;
  var columnNumber=tsh.getLastColumn();
  var toCopy=tsh.getRange(firstRowYesterday,1,rowNumber,columnNumber).getValues(); //values  from yesterday
  var lastRow2=tsh2.getLastRow();
  tsh2.getRange(lastRow2+1,1,rowNumber,columnNumber).setValues(toCopy);  //append to workbook 2
}

I think that your script retrieves the row numbers of yesterday's rows. Unfortunately, I cannot understand about your issue. So, in order to correctly understand your goal, can you provide a sample Spreadsheet including the input and output you expect? Of course, please remove your personal information. — Tanaike
Hi @tanaike - workbook 2 should be pulling the last five rows (rows 151-155) from Workbook 1 and appending them to the bottom of Workbook 2. — econobro
Thank you for replying. Can you provide the sample Spreadsheet which is not the published Spreadsheet? Because I'm not sure whether the values of column "K" is the date object and 1/4/2020 is January 4, 2020 from from your published Spreadsheet. This is due to my poor skill. I deeply apologize for this. — Tanaike
you compare the day not date, so you will get any date if the day of date is some as the day of yesterday — user11982798
Please post a copy/example of the sheets in the question in order to help you. — Andres Duarte

user11982798 user11982798 · Accepted Answer · 2020-01-06T00:43:04

change these (getday 1 ~ 7, getdate 1 ~ 31, without getday and get date, date as is):

if(new Date(timestamps[i-1][0]).getDay()==yesterdayDate.getDay()){

to

if(new Date(timestamps[i-1][0]).getDate()==yesterdayDate.getDate()){

or

if(new Date(timestamps[i-1][0])==yesterdayDate){

and you can change your if to:

if(....){
  ......;
}
else
{
  ......;
}

so your iteration for only once run not twice run

How do I filter for yesterday's date in google scripts?

1 Answers