moving rows to a different sheet if timestamp column is more than 90 days ago

[teresa aiki]

I'm trying to move some rows from one spreadsheet to another based on the column A 'Timestamp'. So if the Timestamp value is more than 90 days ago from today, then the row should be deleted from spreadsheet 'archive' and appended to spreadsheet 'archive archive'. I'm struggling with the coding. Here's what I have so far.

function archiveRow(){

var from = SpreadsheetApp.openById('###').getSheetByName('archive');

var to = SpreadsheetApp.openById('###').getSheetByName('archive archive');

var values = from.getDataRange().getValues();

var lastRow = from.getLastRow();

var olderthan = new Date().setDate(new Date().getDate() - 9);

for(var row=lastRow;row>1;row--)

{

var timestamp = new Date('A'+row);

if(timestamp.valueOf() < olderthan.valueOf())

{

to.appendRow(values[row]);

from.deleteRow(values[row]);

}

}

}

[Laurie Nason]

Hi Teresa,

It looks like your "for" loop might need re-writing as you are not actually retrieving the values in the contained 'if' statement - you probably want something like this:

//check that your start row is not 0 based - you may need to use 1 if you are starting on row 2

for (var row=2;row<values.length;row++){

    // check for the value in column A - I think it's a zero based array - so it will be 0 rather than 1

    if(values[row][0]<olderthan){

        //add to your archive archive sheet

        to.appendRow(values[row]);

        //remove from your archive sheet again I think you may need to check whether the delete row needs an actual row (think it does) rather then the 0 based array

        from.deleteRow(row+1);

    }

}

On Sun, Dec 6, 2020 at 4:56 AM teresa aiki <aikit...@gmail.com> wrote:

I'm trying to move some rows from one spreadsheet to another based on the column A 'Timestamp'. So if the Timestamp value is more than 90 days ago from today, then the row should be deleted from spreadsheet 'archive' and appended to spreadsheet 'archive archive'. I'm struggling with the coding. Here's what I have so far.

function archiveRows(){

var from = SpreadsheetApp.openById('###').getSheetByName('archive');

var to = SpreadsheetApp.openById('###').getSheetByName('archive archive');

var values = from.getDataRange().getValues();

var lastRow = from.getLastRow();

var olderthan = new Date(new Date().setDate(new Date().getDate() - 90));

for(var row=2;row<=lastRow;row++)

{

if('A'+row < olderthan)

{

to.appendRow(values[row]);

from.deleteRow(values[row])

}

}

}




--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/37fdeb68-8b6c-4c61-9a95-a015b735db73o%40googlegroups.com.

--

Laurie Nason 

The KAUST School – Operational Analyst

Information Technology Support
Deep in the Bowels of the School
Landline: +966-12-808-6853

Mobile: +966-54-564-3698

[aikit...@gmail.com]

I tried you suggestions but the function skips some rows that it should not. I have since modified my function to hopefully fix the comparison issues, but I am still unable to get it working properly.

[Jean-Luc Vanhulst]

Laurie,

deleteRow() needs a row number - so you would have to do deleteRow( row) . not deleteRow(value[row])

Also, be aware that once you do that, the next row is now a different number (everything shifts) So you might consider instead deleting the rows in the values array first and adding a blank row at the end of array and then doing one range.setValues() at the end?

Jean-Luc Vanhulst - President - Write2market.com
Cell: 347-453-7686

ᐧ

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/99c1370c-1ab5-47b6-9ea4-8bd412a44240n%40googlegroups.com.

[cbmserv...@gmail.com]

Teresa,

 

Jean-Luc is correct, Try this:

 

function archiveRows(){

  var from = SpreadsheetApp.openById('###').getSheetByName('archive');

  var to = SpreadsheetApp.openById('###').getSheetByName('archive archive');

  var values = from.getDataRange().getValues();

  var lastRow = from.getLastRow();

  var olderthan = new Date(new Date().setDate(new Date().getDate() - 90));

  var offset = 1;

  for(var row=2;row<=lastRow;row++)

  {

    if(values[row][0]<olderthan)

    {

     to.appendRow(values[row]);

      from.deleteRow(row+offset);

      offset--;

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAKTLZ53HVxcDjpKUd2ZSSWgk7dVYtZb%3DHz7A_N8TnHsZB2KH7g%40mail.gmail.com.

[aikit...@gmail.com]

That seems to work! I just needed to start from row 1 instead of row 2: `for(var row=1;row<=lastRow;row++)' and add an `else return;` statement to avoid the "TypeError: Cannot read property '0' of undefined " . Just for my edification, that error has something to do with the offset? What exactly does `offset--;` do? I take it, that's why the script has to run the rows from top down and fails if you try bottom up (from lastRow)?

[CBMServices Web]

The offset variable is used to provide difference in row number from spreadsheet row and the row in the array of data that you pulled using getValues().

Everytime you delete a row in the sheet, the offset is decrementd to keep the alignment for the rest of the rows to be checked. This makes sure you do not delete the wrong rows.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/606a2f9e-f799-4198-ac57-6b7804b45b09n%40googlegroups.com.

[Laurie Nason]

Oh - yes.. I forgot about the fact that you delete rows everything moves..

I remember back in the VBA days of deleting rows in databases, one trick was to start at the end of the list and remove them that way - then nothing shifted up.

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3W7d25PkgA7gF1FbTUA8B3MAUcsTuQrX12TijF7PfTXLw%40mail.gmail.com.