Google Sheets – List Values if Date Matches Cell D1

Your task is better described as matching or lookup rather than filtering: you want at most one entry to be returned. This can be efficiently done with vlookup

=arrayformula(iferror(vlookup('SHEET 1'!A2:A, 'SHEET 2'A2:A, 1, False)))

This looks up every element of 'SHEET 1'!A2:A in the column 'SHEET 2'A2:A and returns what is found. The arrayformula part ensures that everything is handled at once.

You may still run into performance problems, in which case you should either get rid of any empty rows at the bottom of the sheet (they are being looked up, too) or to filter out the blanks. That is, replace:

• 'SHEET 1'!A2:A with filter('SHEET 1'!A2:A, len('SHEET 1'!A2:A))
• 'SHEET 2'!A2:A with filter('SHEET 2'!A2:A, len('SHEET 2'!A2:A))

I'll add some remarks on the script, although I haven't studied it much.

• Use getLastRow() instead of getMaxRows(), so that the loop runs only over the rows with data. Same for columns.
• Do not call setValue in a loop. Instead, store the values you want to insert in a suitable array output, and after the loop call setValues(output). You can see such an example here.

You want to update a new row of data each Monday.

You need to do two things:

• create this script in the script editor for your spreadsheet.
• create a time-driven trigger that will automatically execute the script every monday at a given time.

function wa_14922101() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // define the sheet to execute this script
  var sheetname = "<<substitute your sheet name>>";
  var sheet = ss.getSheetByName(sheetname)

  // get the number of rows of data in column BU
  var BUvals = sheet.getRange("BU4:BU").getValues();
  var BUlast = BUvals.filter(String).length;
  var BULR = BUlast+3;
  // Logger.log("DEBUG: the number of records is "+BUlast+", so the last row = "+BULR);

  // define the source range and get the values
  // Column BU = Column 73
  var sourcerange = sheet.getRange(1, 73, 1, 3);
  //Logger.log("DEBUG: the source range is "+sourcerange.getA1Notation());
  var sourcedata = sourcerange.getValues();
  
  // define the target range
  var targetrange = sheet.getRange(BULR+1, 73, 1, 3);
  // Logger.log("DEBUG: the target range is "+targetrange.getA1Notation());
  
  // copy the data in row#1 to the next row.
  targetrange.setValues(sourcedata);
  
  return false;
}

Trigger settings