Auto Move Data of Specific Date

Question

Link of My sheet is :

https://docs.google.com/spreadsheets/d/1czJbRU5ELNft1IfGq1cABGe30j8BWjnffVCEa8A_AeY/edit?usp=sharing

I am trying to move data if N is equal to today. I have set the trigger. This script runs on time driven between 8 PM to 9 PM. It copies the data in Row 8 when column K onwards there is noting mentioned. In the current Payment Approval Sheet, while running the script it copies the data in 1500th row.

The script I am using is as below:

function copyrange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Complete Invoice Sheet'); //source sheet
  var testrange = sheet.getRange('N:N');
  var testvalue = (testrange.setNumberFormat("@").getValues());
  var ds = ss.getSheetByName('Payment Approval Sheet'); //destination sheet
  var data = [];
  var j =[];
  var dt = new Date();
  var today = Utilities.formatDate(new Date(), 'GMT-0', 'dd/MM/yyyy')

  //Condition to check in N:N, if true, copy the same row to data array 
  for (i=0;i<testvalue.length;i++) {
    if (testvalue[i] == today) {
    data.push.apply(data,sheet.getRange(i+1,1,1,13).getValues());
  //Copy matched ROW numbers to j
    j.push(i);
}  
}
  //Copy data array to destination sheet
  ds.getRange(ds.getLastRow()+1,1,data.length,data[0].length).setValues(data);
}

If N of I am trying to move data if N is equal to today. is the column "N" of the sheet "Complete Invoice Sheet", when I saw your shared Spreadsheet, there are no values in the column "N" of the sheet. How about this? If you can do, can you add the Spreadsheet corresponding to your question in your question as an image? By this, I think that it will help users think of the solution. — Tanaike
Thank you for replying and adding more information. Now I noticed that an answer has already been posted. I would like to respect the existing answer. I think that it will resolve your issue. — Tanaike

soMario soMario · Accepted Answer · 2020-09-28T09:16:21

Issue:

Your current solution considers the last row of your destination sheet Payment Approval Sheet. However, in that sheet, checkboxes are populated in column N until the bottom of the sheet. Therefore, getLastRow() returns the row at the bottom of column N which is not what you want.

Explanation:

Instead of using getLastRow(), calculate the number of elements after cell A7 by using the filter() operation and then use this as a starting point when you copy & paste the data to the destination sheet:

  var start_row=ds.getRange('A8:A').getValues().filter(String).length +7; //calculate max row

  ds.getRange(start_row+1,1,data.length,data[0].length).setValues(data);

Solution:

function copyrange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Complete Invoice Sheet'); //source sheet
  var testrange = sheet.getRange('K:K');
  var testvalue = (testrange.setNumberFormat("@").getValues());
  Logger.log(testvalue);
  var ds = ss.getSheetByName('Payment Approval Sheet'); //destination sheet
  var data = [];
  var j =[];
  var dt = new Date();
  var today = Utilities.formatDate(new Date(), 'GMT-0', 'dd/MM/yyyy')

  //Condition to check in N:N, if true, copy the same row to data array 
  for (i=0;i<testvalue.length;i++) {
    if (testvalue[i] == today) {
    data.push.apply(data,sheet.getRange(i+1,1,1,13).getValues());
  //Copy matched ROW numbers to j
    j.push(i);
}  
}
  //Copy data array to destination sheet
  
  var start_row=ds.getRange('A8:A').getValues().filter(String).length +7; //calculate max row

  ds.getRange(start_row+1,1,data.length,data[0].length).setValues(data);
}

Auto Move Data of Specific Date

1 Answers

Issue:

Explanation:

Solution: