Help with using Macros to Format/Replace

[jarod_007]

I'm not great with coding in general so apologise if I say something that doesn't make sense! Hope this request is relatively simple.

Currently, I use Googlesheets to manage a few things and I download data from a website then paste it into Googlesheets and run a macro to format it. I'm 95% there based on a Macro recording with light edits. Now I'm just trying to do two (hopefully) simple things that's easy to do.

Firstly:
- I want to search through all values in Column F of my current sheet (Excluding the header), and if the cell is Not Empty, replace it with a "Yes".

Secondly:
- Column I will be a sorted list consisting of values "Yes" and "Waiting List" with all the "Yes" values on top. I want to find the Last Row that has a "Yes" value in it, then insert 3 blank rows after it to separate the "Yes" and "Waiting List". If however, there is no "Waiting List" value for that dataset, then do nothing. However, it doesn't matter if I just add 3 blank rows after the last "Yes" instead of doing nothing.

Current Code:

function PrepareSessionSheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBorder(false, false, false, false, false, false);
  spreadsheet.getRange('A1').activate();
  spreadsheet.getRange('J:J').activate();
  spreadsheet.getActiveSheet().moveColumns(spreadsheet.getRange('J:J'), 1);
  spreadsheet.getRange('B:B').activate();
  spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getRange('H:H').activate();
  spreadsheet.getActiveSheet().moveColumns(spreadsheet.getRange('H:H'), 4);
  spreadsheet.getRange('E:E').activate();
  spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getRange('J:J').activate();
  spreadsheet.getActiveSheet().moveColumns(spreadsheet.getRange('J:J'), 6);
  spreadsheet.getRange('G:G').activate();
  spreadsheet.getActiveSheet().insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getActiveRange().offset(0, 0, spreadsheet.getActiveRange().getNumRows(), 1).activate();
  spreadsheet.getRange('I:I').activate();
  spreadsheet.getActiveSheet().moveColumns(spreadsheet.getRange('I:I'), 8);
  spreadsheet.getRange('K:K').activate();
  spreadsheet.getActiveSheet().moveColumns(spreadsheet.getRange('K:K'), 9);
  spreadsheet.getRange('J:M').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('M1'));
  spreadsheet.getActiveSheet().deleteColumns(spreadsheet.getActiveRange().getColumn(), spreadsheet.getActiveRange().getNumColumns());
  spreadsheet.getRange('B1').activate();
  spreadsheet.getCurrentCell().setValue('Bubble');
  spreadsheet.getRange('E1').activate();
  spreadsheet.getCurrentCell().setValue('Phone');
  spreadsheet.getRange('F1').activate();
  spreadsheet.getCurrentCell().setValue('Paid');
  spreadsheet.getRange('G1').activate();
  spreadsheet.getCurrentCell().setValue('Vaccinated');
  spreadsheet.getRange('A:A').activate();
  spreadsheet.getActiveSheet().setColumnWidth(1, 455);
  spreadsheet.getRange('B:B').activate();
  spreadsheet.getActiveSheet().setColumnWidth(2, 50);
  spreadsheet.getRange('C:C').activate();
  spreadsheet.getActiveSheet().setColumnWidth(3, 131);
  spreadsheet.getRange('D:D').activate();
  spreadsheet.getActiveSheet().setColumnWidth(4, 50);
  spreadsheet.getRange('E:E').activate();
  spreadsheet.getActiveSheet().setColumnWidth(5, 89);
  spreadsheet.getRange('F:F').activate();
  spreadsheet.getActiveSheet().setColumnWidth(6, 50);
  spreadsheet.getRange('G:G').activate();
  spreadsheet.getActiveSheet().setColumnWidth(7, 73);
  spreadsheet.getRange('H:H').activate();
  spreadsheet.getActiveSheet().setColumnWidth(8, 125);
  spreadsheet.getRange('I:I').activate();
  spreadsheet.getActiveSheet().setColumnWidth(9, 100);
  spreadsheet.getRange('E2').activate();
  spreadsheet.getCurrentCell().setFormula('=VLOOKUP(A2,Master!A:C,3,false)');
  spreadsheet.getRange('G2').activate();
  spreadsheet.getCurrentCell().setFormula('=VLOOKUP(A2,Master!A:D,4,false)');
  spreadsheet.getRange('E2').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('E2:E40'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('G2').activate();
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('G2:G40'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('G2:G28').activate();
  spreadsheet.getRange('1:1').activate();
  spreadsheet.getActiveRangeList().setFontWeight('bold');
  spreadsheet.getRange('B1:I1').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center');
  spreadsheet.getRange('B:B').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center');
  spreadsheet.getRange('D:D').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center');
  spreadsheet.getRange('F:F').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center');
  spreadsheet.getRange('G:G').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center');
  spreadsheet.getRange('E:E').activate();
  var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
 
  conditionalFormatRules.splice(conditionalFormatRules.length - 1, 1, SpreadsheetApp.newConditionalFormatRule()
  .setRanges([spreadsheet.getRange('E1:F1000')])
  .whenCellEmpty()
  .setBackground('#FF0000')
  .build());
  spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);
  spreadsheet.getRange('A:I').activate();
  spreadsheet.setCurrentCell(spreadsheet.getRange('I1'));
  spreadsheet.getActiveRange().offset(1, 0, spreadsheet.getActiveRange().getNumRows() - 1).sort({column: 9, ascending: false});
 
  spreadsheet.getRange('A1').activate();
};

If someone can help me how to add to this to perform those 2 additional things, that would be most helpful.