How to transfer multiple rows from one tab to another based on matching cell value

Question

I have two tabs in my sheet -- "Current" and "Futures".

Current is everything that has a date of TODAY. Futures are inputted well in advance, but when that date arrives (meaning -- input an order to take place on 3/7 on 3/1, then 3/7 arrives), I want the rows (only columns A to L) with MATCHING dates to move from FUTURES to CURRENT and append to the first blank cell in column A.

The rows should append, then be removed from FUTURES.

I have a "=TODAY" in cell Q1 of daily, and am matching column A of FUTURES to that.

My current script does not seem to make any action when I run it.

function copyIfMatched() {
  var ss = SpreadsheetApp.openById("1eRO-30eIZamB5sjBp-Mz2QMKCfGxV037MQO8nS7G7AI");
  var futures = ss.getSheetByName("Futures");
  var daily = ss.getSheetByName("Daily");
  
  var futuresData = futures.getRange("A3:L" + futures.getLastRow()).getValues();
  var dailyMatchValue = daily.getRange("Q1").getValue();
  var dailyData = daily.getDataRange().getValues();
  
  for (var i = futuresData.length - 1; i >= 0; i--) {
    if (futuresData[i][0] === dailyMatchValue) {
      dailyData.push(futuresData[i].slice(0, 12));
      futures.deleteRow(i + 3);
    }
  }
  
  daily.getDataRange().clearContent();
  daily.getRange(1, 1, dailyData.length, dailyData[0].length).setValues(dailyData);
}

The

Any advice as to what might be off?

I have a test sheet here, with a sample of the workflow.

There are two tabs, Daily & Futures - There is a date in Cell Q1 on Daily. If that cell matches any value in COLUMN A of FUTURES, that row on FUTURES should be MOVED to Daily. In other words, if the date matches, add it to the DAILY tab and remove it from the FUTURES:

https://docs.google.com/spreadsheets/d/1xWzwCwr052iHcyukirSm6wbq_WNFOoDR9ZqjOIfY85Q/edit?usp=sharing

Answer 1

I believe your goal is as follows.

• You want to copy the values from the specific rows of "A:L" in "Futures" sheet to the range of "A3:L" in "Daily" sheet by checking the values of column "A".
• From your reply of No need to worry about the background color or the "Green" column, it would just be columns A to L that I'm trying to move the data over, then the row can be deleted., you want to just move the specific rows.

In this case, how about the following sample script?

I thought that the reason for your current issue might be due to the method for comparing the date values. So, in this sample script, in order to compare the value of date, as an approach, it is compared as a string value. Of course, I think that the date object can be also used. But, in that case, it is required to modify the below script. Please be careful about this.

Sample script:

function copyIfMatched() {
  var ss = SpreadsheetApp.openById("1eRO-30eIZamB5sjBp-Mz2QMKCfGxV037MQO8nS7G7AI");
  var futures = ss.getSheetByName("Futures");
  var daily = ss.getSheetByName("Daily");

  // I modified the below script.
  var dailyMatchValue = daily.getRange("Q1").getDisplayValue();
  var range1 = futures.getRange("A3:L" + futures.getLastRow());
  var futuresData2 = range1.getDisplayValues();
  var { newValues, oldValues } = range1.getValues().reduce((o, r, i) => (o[futuresData2[i][0] == dailyMatchValue ? "newValues" : "oldValues"].push(r), o), { newValues: [], oldValues: [] });
  if (newValues.length == 0) return;

  // ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  daily.getRange(daily.get1stNonEmptyRowFromBottom(1) + 1, 1, newValues.length, newValues[0].length).setValues(newValues);
  range1.clearContent().offset(0, 0, oldValues.length, oldValues[0].length).setValues(oldValues);
}

• When this script is run, the values are copied from the "Futures" sheet to the "Daily" sheet by checking the values of column "A". In this case, the moved values are appended to the "Daily" sheet.

References:

• reduce()
• getDisplayValues()