How to make the cell reference in Google App Script dynamic?

Question

I am creating a macro for my repetitive work.

What I want to do:

1. Copy a current cell in Sheet1
2. Paste it to Sheet2
3. There will be a =filter formula running at the bottom, and I need to navigate to the bottom and copy the range of outputs
4. Have the cell return back to its original A1 cell position in Sheet2
5. Paste the outputs to Sheet3, and move down one row
6. Loop it until it meet an empty cell in the row of Sheet1

Challenges I face:

• In Sheet1: The macro I created only refer to the cell I first run the macro with (even after I start from a different cell, the macro still copying the same initial cell)
• In Sheet2: The outputs will possibly be a row or multiple rows of output, so it seems like Ctrl+A during the macro may not quite do the work.
• For Loop: The macro only me to run once, but I will need it run repetitively until it meets an empty cell in Sheet1.

Challenge 1 and 2 are my main challenges, I can manually use hotkeys to run the macro if I can't get the macro to loop, but will be definitely grateful if someone can teach me how to loop it.

function CleanUp6() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.getRange('\'Sheet1\'!C2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getActiveRange().getDataRegion().activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.UP).activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet3'), true);
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  spreadsheet.getRange('\'Sheet2\'!A8:D8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

Answer 1

Using macro recorder to try to develop a script is not a good idea. It is recording mouse and keyboard actions, not code logic. I think what you want is something like this. Can't be sure but try it on a copy of your spreadsheet. Add a menu option with the onOpen() trigger.

function onOpen() {
  var menu = SpreadsheetApp.getUi().createMenu("File Picker");
  menu.addItem("Test","test");
  menu.addToUi();
}

function test() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getActiveSheet();
    if( sh.getName() !== "Sheet1") {
      SpreadsheetApp.getUi().alert("Change to Sheet1");
      return;
    }
    if( sh.getActiveCell().getA1Notation() !== "A1" ) {
      SpreadsheetApp.getUi().alert("Change to cell A1");
      return;
    }
    // get the values in A:A
    var values = sh.getRange(1,1,sh.getLastRow(),1).getValues();
    sh = ss.getSheetByName("Sheet2");
    // copy values from Sheet1 to Sheet2
    // assumes the formulas are outside of the range values
    sh.getRange(1,1,values.length,1).setValues(values);
    // get the last row
    values = sh.getRange(sh.getLastRow(),1,1,sh.getLastColumn()).getValues();
    sh = ss.getSheetByName("Sheet3");
    // get the row following the last row .getLastRow()+1
    sh.getRange(sh.getLastRow()+1,1,values.length,values[0].length).setValues(values);
  }
  catch(err) {
    console.log(err);
  }
}