CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Admin - R K Pareek Co
Admin - R K Pareek Co

Reputation: 53

Auto Move Data of Specific Date

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

Upvotes: 1

Views: 101

Answers (1)

Marios
Marios

Reputation: 27350

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

Upvotes: 1

Related Questions