0

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)

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.

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);
    });
  }
});
Brian
  • 4,274
  • 2
  • 27
  • 55
Alex Anadon
  • 77
  • 3
  • 11
  • What have you tried already? Switching to an explicit `for` loop rather than a `forEach` will give you more control over what gets imported, in addition to allowing you to save state (i.e. put the last index in cache/properties) and then resume iterating in a new call. – tehhowch Mar 01 '18 at 17:25

2 Answers2

0

I've solved this in the past by adding a new column in the spreadsheet which calculates n days past an event.

=ARRAYFORMULA(IF(ISBLANK(K2:K),"",ROUNDDOWN(K2:K - NOW())))

The core of the function is the countdown calculation. For instance, today is Thursday, March 1. Subtracting it from a date in the future like Sunday, March 4, returns a whole integer: 3. I can test for that integer (or any integer) in a simple script.

In your script, add a conditional statement before executing the rest of the function:

// ...
if(someDate === -90) {
  // do something...
}

This way, you're just checking the value of a cell rather than doing a calculation in a helper function. The only change (if you want a longer or shorter interval) is in the conditional test.

Brian
  • 4,274
  • 2
  • 27
  • 55
  • Thank you. But I can't add columns to the origin. What I have done until I get the way to do it is to import the data into another spreadsheet that does exactly what you mention. Then the destination sheet has an importrange formula that gets only the selected ones. – Alex Anadon Mar 02 '18 at 12:34
0

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 */

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.

tehhowch
  • 9,645
  • 4
  • 24
  • 42