Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

Checking Google Sheet Row by Row for Qualified Listings

I have a Sheet with imported data in rows. (Always) Starting with A1 contains a name of a person. If that person is on a waiting list, the list continues to another person in the next row. If a person is not on a waiting list the row following their name has the length of time to get on the waiting list in format "Not currently on our waiting list. Estimated time of wait: 1 month". This could be 1 month or any other length like 3, or 24.

I'm trying to loop down row by row of the entire sheet (sometime just a few people, sometimes a hundred) and check for anyone on the waiting list, or someone who is notand convert and move the value of time waiting to the same row as their name and then delete the row with the wait time. I haven't been able to get the loop the work.

Right now TestifFuture() works by running one cell at a time. I'd like to start at A1 and TestifFuture() for each row and then move down to the next row to the end.

I've broken each action into Functions for easy Debugging as I learn more scripting.

function unmergecells() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveRange().breakApart();
};

function del_row() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow(), 1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveSheet().deleteRows(spreadsheet.getActiveRange().getRow(), spreadsheet.getActiveRange().getNumRows());
};


function convertMonths() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var range = SpreadsheetApp.getActiveRange();
  var cell = range.getCell(1, 1); 
  var cellValue = cell.getValue();
  var array1 = [{}];
  var array2 = [{}];
  array1 = cellValue.slice(66)
  array2 = Number(array1.slice(1,3))
  spreadsheet.getCurrentCell().offset(-1, 6).setValue(array2);
};  

function Move1Down() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().offset(1, 0, 1, 7).activate();
};

function TestifFuture() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var range = SpreadsheetApp.getActiveRange();
  var cell = range.getCell(1, 1); 
  var cell2 = spreadsheet.getActiveCell();
  cellValue = cell.getValue();
  var searchKeystr = "Not currently on our wait list";
  var cellNo = cellValue.split(",");
  var myIndex = cellValue.indexOf(searchKeystr);
  if (myIndex == 0) 
    {
     convertMonths();
     del_row();
  }
  else
  {
    Move1Down();
    }
};

function FindLastRow() {
 var wb= SpreadsheetApp.getActiveSpreadsheet();
 var sh1=wb.getActiveSheet();
 var range = SpreadsheetApp.getActiveSheet().getLastRow();
  for(i=1;i<=range-1;i++)
  {
    var cell=sh1.getRange(i, 1);
    Move1Down();
  }
}

Comments