javascript - How to get an array of data in google sheets based on date in column?

Question

I'm trying to pull sub-section of data from one google sheet to another each day. The pull is conditional based on date which is why I am struggling.

Context - I have 'Workbook 1' that logs price data each day for different NBA teams. The data is captured and added to the bottom of the table each day (so the most current price data is at the bottom). It is date-stamped in column K with =today().. header is "Pull Date".

I have a separate workbook we'll call 'Workbook 2' for the NBA in which I would like to include that SAME price data from Workbook 1, but only for a trailing 15 days (to keep it more agile). To accomplish this, I'd like to have the script incrementally add yesterday's values each morning from 'Workbook 1' to 'Workbook 2' (and for now I will be manually deleting any rows older than 15 days).

Purpose - I've build a report on the table in Workbook 2 with trailing two-week analysis because the Workbook 1 file is simply too big (goes back several months).

Issue - I have some old code from an unrelated workbook where I pull data from one sheet to another but it's not conditional based on pull-date. For this script, I'd like my 'Workbook 2' workbook to request data from 'Workbook 1' and add it to the bottom of sheet 1 on 'Workbook 2' but ONLY for rows containing yesterday's date ( or =today()-1). The amount of rows containing yesterday's date is dynamic and shrinking over time, so I don't have the same amount of rows to move today as I did last week, for ex.

My array is 15 columns wide but the row count will change daily. Below is my starting point:

function runOne() { 
  var ss=SpreadsheetApp.openById('Workbook 1 sheet id');
  var tsh=ss.getSheetByName('sheet 1');
  ???

I have no idea how to conditionally pull yesterday's data AND make the row count dynamic. After I've gotten the proper values, I would need to paste it on the bottom of tab titled "sheet 1" in the 'Workbook 2'.

I have very little experience with scripting so any help would be greatly appreciated. I tried to clarify above as much as possible but let me know if further clarification is needed.

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

• Your date stamp is in column 11 (K)
• you want to run the script every day automatically (on time trigger)
• you want to add to your workbook 2 sheet 1 entries from workbook 1 sheet 1 with the timestamp of yesterday

This is how you can implement it

• Write a code that calculates the date from yesterday and compares it against the timestamps in column K

function runOne() { 
  var ss=SpreadsheetApp.openById('Workbook 1 spreadsheet id');
  var tsh=ss.getSheetByName('sheet 1');
  var ss2=var ss=SpreadsheetApp.openById('Workbook 2 spreadsheet id');
  var tsh2=ss2.getSheetByName('sheet 1');
  var lastRow=tsh.getLastRow();
  var timestamps=tsh.getRange(1,11,lastRow,1).getValues();
  var yesterdayMs=new Date().getTime()-24*60*60*1000;  //get the date from yesterday in ms
  var yesterdayDate=new Date(yesterdayMs);   // get the date from yesterday
  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
}

• Bind to the function to an installable Time-driven trigger, selecting the time of the day to run as desired.