CODEWITHSUNDEEP
javascriptgoogle-apps-script
dericcain
dericcain

Reputation: 2300

Delete row of data based on condition

I have a spreadsheet that I want to use to store project timelines. I have the ability to add projects and their start dates and also their length until completion - this is on the "Projects" sheet. This is reflected on a separate sheet by having cells colored a specific color for each project, depending on what date they are set - this is the "Timeline" sheet. I am having a problem when I want to delete a project. I want to click on the project name's cell and click "Delete Project", from the menu I created, and then have the project row deleted on both the "Projects" sheet and the "Timeline" sheet. The loop is perplexing me and I am not fully understanding it. Should I use an array to store the project names on both sheets and then use that to reference each other, or...??? Here is what I have so far.

function clearProject() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('Projects');
  var sourceRange = sourceSheet.getActiveRange();
  var height = sourceRange.getValues().length;
  var rowPosition = sourceRange.getRowIndex();

  var targetSheet = ss.getSheetByName('Timeline');
  var targetSheetValues = targetSheet.getRange(3, 1, 15, 49);
  var targetProjectNames = targetSheet.getRange(3, 1, 15, 1).getValues();
  var sourceProjectNames = sourceSheet.getRange(2, 1, 15, 1).getValues();

  var targetHeight = targetSheet.getRange(3, 1, 15, 1).length;
  var targetRowPosition = targetSheet.getRange(3, 1, 15, 1).getRowIndex();

  for(var i=0; i > sourceProjectNames.length; i++){
    if(i == targetProjectNames){
      targetSheet.deleteRows(targetRowPosition, targetHeight);
        }
    else{
      break;
    }
  }


  //This deletes the row of the active cell
  //ss.getActiveSheet().deleteRows(rowPosition, height);

  Logger.log(i);

}

Also, if you know any resources that explain loops in plain English, please shoot me a link. Thank you for your help!

EDIT: Changed the comparison from = to == in the "if" statement. EDIT 2: Changed the comparison in the for loop. Also, changed what the if statement compared.

The loop is still not working properly. When I check the logs for i I get 0.0 . Here is the new code...

 function clearProject() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('Projects');
  var sourceRange = sourceSheet.getActiveRange();
  var height = sourceRange.getValues().length;
  var rowPosition = sourceRange.getRowIndex();

  var targetSheet = ss.getSheetByName('Timeline');
  var targetSheetValues = targetSheet.getRange(3, 1, 15, 49);
  var sourceRangeValues = sourceSheet.getRange(2, 1, 15, 1);
  var targetProjectNames = targetSheet.getRange(3, 1, 15, 1).getValues();
  var sourceProjectNames = sourceSheet.getRange(2, 1, 15, 1).getValues();

  var targetHeight = targetSheet.getRange(3, 1, 15, 1).length;
  var targetRowPosition = targetSheet.getRange(3, 1, 15, 1).getRowIndex();

  for(var i=0; i < sourceRangeValues.length; i++){
    if( targetProjectNames[i][0] == sourceRange[i][0]){
      targetSheet.deleteRows(targetRowPosition, targetHeight);
    Logger.log(i);    
    }
  }


  //This deletes the row of the active cell
  //ss.getActiveSheet().deleteRows(rowPosition, height);

  Logger.log(i);

}

Upvotes: 0

Views: 132

Answers (1)

Srik
Srik

Reputation: 7965

  1. I don't think this loop will run even once at all

for(var i=0; i > sourceProjectNames.length; i++)

it should have been 

for(var i=0; i < sourceProjectNames.length; i++)

2.You are comparing an integer to a 2-D array which is incorrect

if(i == targetProjectNames)

Instead, you should be doing something like this.

if( targetProjectNames[i][0] == sourceProjectNames[i][0])

3.Your break; statement will end the loop if the first comparison fails. So remove the else portion altogether

These are few things I could notice upfront. Give this a try and edit your question with more information if you still see problems.

Upvotes: 1

Related Questions