0

edited code:

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]);

}

}

}

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 testArchive() {

var moveFrom = SpreadsheetApp.openById('###').getSheetByName('archive')

var moveRows = SpreadsheetApp.openById('###').getSheetByName('archive archive')

var col = 1;

var valueToWatch = Date()-90;

var moveFromLastRow = moveFrom.getLastRow();

var moveRowsLastRow = moveRows.getLastRow();

for(var row=moveFromLastRow;row>1;row--){

var values = moveFrom.getRange('A2:Y').getValues();

if (col[row] < valueToWatch){

moveRows.appendRow(values[row]);

}

}

}

I have taken a look at Move Row to Other Sheet Based on Cell Value which does not work for me because I do not want the function to run onEdit.

learn
  • 41
  • 1
  • 4
  • Date() is not a number new Date().valueOf() or new Date().getTime() returns the number of milliseconds since a reference. So your valueToWatch is not going to work. You need to spend some more time understanding [Date Object](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date). The fact that you're struggling is a good thing. Struggle enough and you'll figure it out. If you keep on reasking the question I'll just close them and flag your question for moderator to deal with. – Cooper Dec 06 '20 at 00:12
  • I re-ask because my question has not been resolved. If you keep closing them before I get a chance to get input from others I will never find the answer. Right now I am just banging my head against the wall which is why I am asking for help and you are preventing others from helping me. – learn Dec 06 '20 at 00:29
  • Have you read the references I've provided? They cover the very same question – Cooper Dec 06 '20 at 00:31
  • Yes, I've read them and the solutions provided do not work for me as there particular cases are different. – learn Dec 06 '20 at 00:33
  • Okay do you understand why Date()-90 makes no sense? – Cooper Dec 06 '20 at 00:36
  • `function archiveData() { const sss=SpreadsheetApp.openById('###'); const ssh=sss.getSheetByName('archive'); const srg=ssh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()); const svA=srg.getValues(); const dss=SpreadsheetApp.openById('###'); const dsh=dss.getSheetByName('archive archive')` – Cooper Dec 06 '20 at 01:11
  • `let d=0; let dt=new Date(); let dtv=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf(); svA.forEach(function(r,i){ ct=new Date(r[0]); ctv=new Date(ct.getFullYear(),ct.getMonth(),ct.getDate()).valueOf(); if((dtv-ctv/86400000)>90) { ssh.deleteRow(i+sr-d++); dsh.appendRow(r); } }); } ` – Cooper Dec 06 '20 at 01:11
  • I think so. I tried your code but get this error: `TypeError: Cannot read property 'appendRow' of null` on the last line of code: `dsh.appendRow(r); } }); } `. I've revised my code but it's still not working. – learn Dec 06 '20 at 03:41
  • If you understand that it makes no sense. Then why did you do it. If dsh is null then it's probably because dss is null. I recommend that you debug it and figure out why. – Cooper Dec 06 '20 at 05:40
  • What's wrong with your "edited code"? See and practice some of the answers [here](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) – TheMaster Dec 06 '20 at 15:48
  • From your previous comment, There is only one reason for `dsh` being `null`. It's because there is no sheet in your spreadsheet with the provided name(i.e., 'archive archive'). Check for spelling mistakes. – TheMaster Dec 06 '20 at 15:52

0 Answers0