range.getValues()在特定单元格中具有特定日期 [英] range.getValues() With specific Date in Specific Cell

问题描述

我们正在使用Google脚本将范围从其他电子表格导入到另一个电子表格. 过去这对我们有帮助，但现在数据正在增长，我们需要减少导入的数据. (超时问题)

We are using a Google Script to import a Range from other Spreadsheet to another. This helped us in the past but now the data is growing and we need to reduce the data that we import. (timeout problems)

我们需要在特定列上导入具有特定日期的行. 在这种情况下，正如您在下面的脚本中看到的那样，我们正在将range变量中的单元格从"A1"导入到"N最后一行".

We need to import the rows with a specific date on a specific column. In this case, as you can see in the script below, we are importing cells from 'A1' to 'N last row' in the range variable.

我们需要的是从该范围日期起的'H'列中使用诸如"K列中的日期> = Today()-90"之类的内容进行检查

What we need is that in the column 'H' from that range date is checked with something like "Date in column K >= Today()-90"

// iterate all the sheets
sourceSheetNames.forEach(function(sheetName, index) {
  if (EXCLUDED_SHEETS.indexOf(sheetName) == -1) {
    // get the sheet
    var sheet = sourceSpreadSheet.getSheetByName(sheetName);

    // selects the range of data that we want to pick. We know that row 1 is the header of the table,
    // but we need to calculate which is the last row of the sheet. For that we use getLastRow() function
    var lastRow = sheet.getLastRow();

    // N is because we want to copy to the N column
    var range = sheet.getRange('A1:N' + lastRow);

    // get the values
    var data = range.getValues();
    data.forEach(function(value) {
      value.unshift(sheetName);
    });
  }
});

推荐答案

要有条件地仅复制满足条件的行，如果符合条件，则需要将其推入新数组.此推送将添加到您现有的data.forEach()通话中:

To conditionally copy the only the rows that meet a criteria, you will want to push them to a new array if they qualify. This push would be added to your existing data.forEach() call:

...
var now = new Date();
var today = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), now.getUTCDate()));

var kept = [];
var data = range.getValues();
// Add qualifying rows to kept
data.forEach(function(row) {
  var colHvalue = row[7];
  var colKvalue = row[10];
  if( /* your desired test */) {
    // Add the name of the sheet as the row's 1st column value.
    row.unshift(sheetName);
    // Keep this row
    kept.push(row);
  }
});
/* other stuff with `kept`, like writing it to the new sheet */

您将必须执行特定的测试，因为您没有共享时间在H或K列中的存储方式(例如，从纪元开始的天数，等时字符串等).请确保查看日期参考.

You'll have to implement your specific test as you have not shared how time is stored in column H or K (e.g. days since epoch, iso time string, etc). Be sure to review the Date reference.

这篇关于range.getValues()在特定单元格中具有特定日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！