如何根据列中的日期获取Google表格中的数据数组? [英] How to get an array of data in google sheets based on date in column?

问题描述

我每天都在尝试将数据的一部分从一个Google工作表中拉到另一个工作表中.拉动是基于日期的条件，这就是我努力的原因.

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.

上下文-我有工作簿1"，它每天记录不同NBA球队的价格数据.每天都会捕获数据并将其添加到表格的底部(因此，最新的价格数据位于底部).它在K列中带有= today()的日期戳.标题为拉日期".

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".

我有一个单独的工作簿，我们将其称为NBA的工作簿2"，我想在其中包括来自工作簿1的SAME价格数据，但仅在过去的15天内(以使其更加敏捷).为此，我想让脚本每天早晨从工作簿1"到工作簿2"递增地添加昨天的值(目前，我将手动删除任何早于15天的行).

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).

目的-我在Workbook 2的表上构建了一个报告，并进行了为期两周的跟踪分析，因为Workbook 1的文件实在太大(要追溯几个月).

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).

问题-我有一个不相关的工作簿中的一些旧代码，我在其中将数据从一张纸拉到另一张纸，但这不是基于拉日期的条件.对于此脚本，我希望我的工作簿2"工作簿从工作簿1"请求数据并将其添加到工作簿2"的工作表1的底部，但仅适用于包含昨天日期的行(或= today()- 1).包含昨天日期的行的数量是动态的，并且随着时间的推移而不断缩小，因此，例如，我今天没有要移动的行数与上周相同.

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.

我的数组宽15列，但行数每天都会变化.下面是我的出发点:

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');
  ???

我不知道如何有条件地提取昨天的数据并使行计数动态化.得到正确的值后，需要将其粘贴到工作簿2"中标题为工作表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.

推荐答案

• 您的日期戳在第11列(K)
• 您想每天自动运行脚本(按时触发)
• 您要添加工作簿1表1中带有昨天时间戳记的工作表2表1项
• 编写代码以计算昨天的日期并将其与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.

这篇关于如何根据列中的日期获取Google表格中的数据数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！