Move rows on 2 sheets older than 24 hours to archive

Question

I have been trying to figure this one out for a while now and would really appreciate some assistance.

I have a Google Sheet that is being updated by a Google Form. Due to this I needed to mirror the data onto another tab to be able to format it. Unfortunately, this makes this auto-archiving process a bit more tricky.

demo

The information comes in on the 'request form'-tab, and then with a Query is shown on to the 'Today'-tab. Both these sheets have red columns which will be hidden from the users with times to assist with the conditional formatting. What I would like to happen is that when a request is more than 24 hours old it copies to a new line on the 'archive'-tab and then remove the line from both the 'request form' and 'today'-tab.

I am not a programmer by any means and usually get snippets of code and manipulate them and put them together and that usually seems to work, but this one is beyond my copy+paste skills. ;)

The below code seems to work, but what needs to be added is the removal of the same line on the 'Today'-tab.

function onOpen() {
SpreadsheetApp.getUi()
    .createMenu('EDITION')
    .addItem('Archive', 'archiveRows')
    .addToUi();
}

function archiveRows() {
var ss = SpreadsheetApp.getActive();
var s = ss.getSheetByName("Request Form");
var target = ss.getSheetByName("Archive");

    s.getRange("J:J").setNumberFormat('dd MM yyyy hh:mm:ss');

var today = new Date().getTime()
var val = filterArray(s.getDataRange()
    .getValues());
for (var len = val.length - 1, i = len; i >= 0; i--) {
    if (val[i][9].getTime() < today) {
        target.appendRow(val[i])
        s.deleteRow(i + 2)
        }
    }
}

function filterArray(array) {
var filteredArray = [];
array.forEach(function (r) {
    if (r[8] !== '') {
        filteredArray.push(r);
    }
});
return filteredArray;
}

Answer 1

Looks that we have here an XY Problem: you are trying to solve problem of keep at sight current entries (X), and you think using a script to archive old entries (Y) would work , but instead of asking about X when you run into trouble, you ask about Y.

To keep at sight current entries that you capture from a Google Form the demo spreadsheet shows that =query('Request Form'!A:F) is used on the Today sheet but also use formulas on the Request Form sheet to take data from the Today sheet using formulas like the following

=arrayformula(iferror(VLOOKUP($A$1:$A$3888,Today!$A:$N,11,true),""))

The above model is very risk because any change on the Request Form could make that the data on columns G to N be misplaced.

Let say that you run first the archiveRows() function. This will make that the data on columns G to N be misplaced. If you do this the other way, deleting first the rows on Today sheet, then the formulas on the Request Form sheet will return "".

• My recommendation is that instead of doing Form -> Request Form -> Today do Form -> Today by using a script. This will make your spreadsheet simpler and less risky. Then using this you will have don't have to delete rows o two sheets, just on the Today sheet.

For this, you could keep the Request Form but instead of using =query(...) use an script triggered by the on form submit event. Then you could adapt the archiveRows() function to work on the Today date by making slights changes to it.