CODEWITHSUNDEEP
javascriptloopsgoogle-apps-scriptgoogle-sheets
cday
cday

Reputation: 11

Looping through range of cells looking for values

I have a data sheet that contains a information which is then routed to another sheet based on an option selected in a drop down menu. I have created a script that does this by removing the row based on the string selected in the dropdown and copies it to a new sheet when a button is pressed on the data sheet. However, once it meets a string that doesn't equal the string I'm looking for, it stops.

Below is a sample of the code I have so far.

function sendUpdates()
{
  var sheetNameToWatch = "Data Report";
  var sheetNameToDelete = "Hidden - Data Report;
  var valueToWatch = "Send to XXX";
  var sheetNameToMoveTheRowTo = "Hidden - New - Data Report";

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var range = sheet.getRange("O2:O50");
  SpreadsheetApp.setActiveRange(range);

  for (;range.getValue() == valueToWatch;)
  {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1); 
    var deleteSheet = ss.getSheetByName(sheetNameToDelete)
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).copyTo(targetRange, {contentsOnly: true});
    sheet.deleteRow(range.getRow());
    deleteSheet.deleteRow(range.getRow()); 
  }


}

I would like for it to be able to continuing through the range until the end if possible? Any help really appreciated.

Upvotes: 1

Views: 11343

Answers (2)

Francesc Roma-i Frigole
Francesc Roma-i Frigole

Reputation: 11

instead of 

if (range.getValue() == valueToWatch) {}

try

if( dataRange.getCell(0, i).getValue() == valueToWatch ) {}

Upvotes: 1

Alan Wells
Alan Wells

Reputation: 31310

Change your for loop from:

for (;range.getValue() == valueToWatch;)

To:

var i = 0,
    numberOfRowsInRange = range.getNumRows();

for (i=0;i<numberOfRowsInRange;i+=1)

Upvotes: 0

Related Questions