0

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
  • What does it mean 'move'? You want to activate a range or to move the data? – contributorpw Sep 1 at 18:44
  • Take the row that is being deleted and add it into archive. – user226383 Sep 1 at 19:23
  • Welcome. As per site guidelines when an answer addresses your question, you should accept it so others can benefit as well -. (Why vote?) – marikamitsos Sep 2 at 3:41
1

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

  • I got the script by googling "Delete expired rows in google sheets" somewhere. I am not a programmer but tuned it a bit to my knowledge and it worked. I appreciate the answer, tried your first script and it works exactly like I want it! Thank you! – user226383 Sep 2 at 0:34
  • Thanks @marikamitsos. Some snippets already have this. See github.com/oshliaer/google-apps-script-snippets/blob/master/… I am currently trying to iterate over this repository and update it for ease of use. – contributorpw Sep 2 at 3:29
  • @user226383 if this solves you problem I think it can be marked as 'best'. – contributorpw Sep 2 at 3:33
  • @marikamitsos be free to do good things with me =) – contributorpw Sep 2 at 3:34
  • 1
    @contributorpw That is really useful. – marikamitsos Sep 2 at 3:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.