Google-sheets – Move rows on 2 sheets older than 24 hours to archive

google sheetsgoogle-apps-script

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;
}

Best Answer

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.

Related Topic