Google-sheets – How to copy values that have just been deleted into ‘Archive’ Sheet

google sheetsgoogle-apps-scriptgoogle-sheets-dates

Setting up so any expired date rows get deleted. But I would Like to Delete and Move to Archive tab (sheet).

Have tried taking other codes and integrating but cannot find the code.

function DeleteOldEntries() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Current'); // assumes Current is the name of the sheet
  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();
  var values = datarange.getValues(); // get all data in a 2D array
  var currentDate = new Date(new Date().getTime() - 24 * 60 * 60 * 1000); // tomorrow
  for (var i = lastrow; i >= 2; i--) {
    var tempDate = values[i - 1][1]; // arrays are 0 indexed so row1 = values[0] and col3 = [2]
    if (tempDate != NaN && tempDate <= currentDate) {
      sheet.deleteRow(i);
    } // closes if
  } // closes for loop
} // closes function

Best Answer

Try this

// As used at: https://webapps.stackexchange.com/a/133308/
function DeleteOldEntries() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Current'); // assumes Current is the name of the sheet

  var archive = ss.getSheetByName('Archive');

  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();
  var values = datarange.getValues(); // get all data in a 2D array
  var currentDate = new Date(new Date().getTime() - 24 * 60 * 60 * 1000); // tomorrow
  for (var i = lastrow; i >= 2; i--) {
    var tempDate = values[i - 1][1]; // arrays are 0 indexed so row1 = values[0] and col3 = [2]
    if (tempDate != NaN && tempDate <= currentDate) {

      archive.appendRow(values[i - 1]);

      sheet.deleteRow(i);
    } // closes if
  } // closes for loop
} // closes function

In other ways you can adopt the next snippet. This removes all rows which contains 'asdf' in B-column. All data is archived.

// As mentioned at: https://webapps.stackexchange.com/a/133308/
function deleteRowsByConditional_(sheet, condition, action) {
  var values = sheet.getDataRange().getValues();
  values.unshift([]);
  values.reverse().forEach(
    function() {
      var i = this.l - arguments[1];
      if (this.condition.apply(null, [arguments[0], i, arguments[2]])) {
        this.isContinue++;
      } else if (this.isContinue) {
        if (action) action(arguments[2], i, this.isContinue);
        this.sheet.deleteRows(i, this.isContinue);
        this.isContinue = 0;
      }
    },
    { sheet: sheet, condition: condition, isContinue: 0, l: values.length }
  );
}

function run2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var archive = SpreadsheetApp.getActive().getSheetByName('Archive');

  var action = function(values, i, i2) {
    var data = values.slice(values.length - i - i2, values.length - i);
    archive
      .getRange(archive.getLastRow() + 1, 1, data.length, data[0].length)
      .setValues(data);
  };

  var condition = function(row) {
    return row[1] === 'asdf';
  };

  deleteRowsByConditional_(sheet, condition, action);
}

Full code is here

Related Topic