Automation of Google Sheets (Google Scripts) so that when cell date is in the past the row is moved into another sheet

user_10976428 0
Viewed 53

As mentioned in the title, I am trying to set an automation using the code below that moves entire rows based on a row's specific cell value (date) being in the past. The column containing that date is "L" if that helps, I am unsure where to use this if necessary, is this within the getDataRange function? The date information I am using is in the current format: 1/25/2021 19:30:00

When I run the code below it gives me the 'Execution Completed' notice but it doesn't appear to be moving the rows across into the destination sheet (Auction Results).

To provide further context the Original sheet is called Collecting Cars - Live and today's date is saved in the sheet App Rules in B1.

function MovetoPastShows2() {
   function MovetoPastShows2() {
   var ss = SpreadsheetApp.getActive();
   var sh=ss.getSheetByName('Auctions - Live'); 
   var rg=sh.getDataRange();
   var vA=rg.getValues();
   var today=new Date(ss.getSheetByName('App Rules').getRange('B1').getValue()).valueOf();
   var dsh=ss.getSheetByName('Auction Results'); 
   var d=0;
   for(var i=0;i<vA.length;i++) {
     if (new Date(vA[i][2]).valueOf()<=today) {
       dsh.appendRow(vA[i])
       sh.deleteRow(i+1-d);
       d++;
      }
     }
    }
google-apps-script google-sheets
1 Answers

Issue:

You have a MovetoPastShows2 function inside another MovetoPastShows2 function.

  • The inside MovetoPastShows2() is never executed and therefore the code within that function is never executed as well.

  • You only execute the outer MovetoPastShows2() where its only job is to define another function with the same name. It does not execute the actual code inside that inner function though.

  • It is not a good idea to have 2 functions with the same name. Although that is not the issue here, it is a general recommendation.

Solution:

Remove the outer MovetoPastShows2():

function MovetoPastShows2() {
   var ss = SpreadsheetApp.getActive();
   var sh=ss.getSheetByName('Auctions - Live'); 
   var rg=sh.getDataRange();
   var vA=rg.getValues();
   var today=new Date(ss.getSheetByName('App Rules').getRange('B1').getValue()).valueOf();
   var dsh=ss.getSheetByName('Auction Results'); 
   var d=0;
   for(var i=0;i<vA.length;i++) {
     if (new Date(vA[i][2]).valueOf()<=today) {
       dsh.appendRow(vA[i])
       sh.deleteRow(i+1-d);
       d++;
      }
     }
 }
user_112252910
Related