CODEWITHSUNDEEP
if-statementgoogle-apps-scriptgoogle-sheetslogic
JoeW
JoeW

Reputation: 147

Copying certain rows to duplicated sheet using macro

I've got a google sheet which I'm using as a monthly handover sheet with a to-do list at the bottom.

The to-do list has a checkbox then a couple of columns of information (a date, the task and who it's assigned to)

To-Do List

I've created a button that runs a macro to duplicate the sheet to create a copied sheet at the end of the month.

I'd like to copy over any tasks in the to-do list which haven't been 'ticked' as completed but I'm not very good at logic in google apps script.

Could anyone help me write the if statement to either copy unticked rows to the duplicated sheet or do duplicate all rows then delete the ticked ones.

Thanks, Joe

UPDATE: after playing around for a couple of hours, this is what I've got:

function ToDoCopy() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var duplicateSheet = ss.getSheetByName("JUNE");
  var originalSheet = ss.getSheetByName("MAY");
  
  var lastRow = originalSheet.getLastRow() + 1;
  
  for(var j = 24; j <  lastRow; j++)
  {
    if(originalSheet.getRange(j,2).getValue() ==false && originalSheet.getRange(j,3).getValue() != 4)
    {
      var nextRow = duplicateSheet.getLastRow() +1;
      var getCopyRange = originalSheet.getRange('B' + j + ':P' + j);
      getCopyRange.copyTo(duplicateSheet.getRange(nextRow, 2));
    }
  }
}

It's almost working but it adds the copied items underneath the to-do table on the new sheet rather than adding them to the top. I can't work out how to fix this!

(p.s. the && originalSheet.getRange(j,3).getValue() != 4 part is a hidden row which I use for auto-sorting the table, '4' means it's empty basically.

Upvotes: 0

Views: 59

Answers (1)

Cooper
Cooper

Reputation: 64100

function ToDoCopy() {
  const ss = SpreadsheetApp.getActive();
  const ssh = ss.getSheetByName("MAY");//source sheet
  const sshsr = 24;//start row
  const srg = ssh.getRange(sshsr, 1, ssh.getLastRow() - sshsr + 1, ssh.getLastColumn());
  const svs = srg.getDisplayValues();//useful for me when using checkboxes
  const dsh = ss.getSheetByName("JUNE");//destination sheet
  if (dsh.getLastRow() - sshsr + 1 > 0) {
    dsh.getRange(sshsr, 1, dsh.getLastRow() - sshsr + 1, dsh.getLastColumn()).clearContent().setDataValidation(null);//Clear destination range if not already cleared
  }
  let a = 0;//row add counter
  svs.forEach((r, i) => {
    if (r[1] == 'FALSE' && r[2] != 4) {
      ssh.getRange(sshsr + i, 1, 1, ssh.getLastColumn()).copyTo(dsh.getRange(sshsr + a++, 1));
    }
  });
}

Upvotes: 1

Related Questions