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