cut/paste scripting - copy a range then paste to a destination range that matches to row/column address criteria - INDEX/MATCH but cut n paste

[benbulloch]

I'm struggling with some simple copy/paste scripting in Excel/Sheets. I'm a complete scripting newbie. Advanced at Excel/Sheets.
I have a "Today" data entry sheet. I have a "Program" sheet that stores data in a column under a Date header. I want a macro/script that will:

1. cut data from an "Actual" range on Today for a specific exercise
2. search the date row in "Program" to Match whatever date is on the "Today" data entry page for the destination column address
3. search the exercise category column in "Program" to Match whatever exercise is on the "Today" data entry page for the destination row address
4. Finally paste source range from Today to destination "Actual" range on Program to cell address identified in Step 2 and 3
5. Repeat 20 times for different exercises

Essentially I want to INDEX/MATCH in reverse. Rather than return a cell in INDEX/MATCH, I want to copy to a cell.
Simplified example Sheet is here with additional notes embedded: https://docs.google.com/spreadsheets/d/1i66I4kI733dTzoYFgsgDLRl4XWhFQc7gI-vAYKfC1Jo/edit?usp=sharing
Full sheet is here but rather complex: https://docs.google.com/spreadsheets/d/1FRbZDhqfMJ8mXJs6BJKveMstZ9Q0s-2BIv85d48Y7mY/edit?usp=sharing

I need eventually in Sheets but I'm sure I can figure out translating from VBA if easier.

Huge thanks in advance. This has been making me crazy.

 

[benbulloch]

I figured it out. Not the most elegant solution but it works. The "var col = s.getRange(4,1).getValue();" is just a MATCH function. I'd still prefer to have the row lookup without hardcoding the row number but this works until I add stuff. The bit at the end just resets the data entry spaces to match the "recommended" amounts by formula.

function MyFunction() {
  var ds = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Program');
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Today');
  var col = s.getRange(4,1).getValue();
  
  //Sq w/ Belt
  s.getRange('B11:G11').copyTo(ds.getRange(11, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //2ct Paused Squat
  s.getRange('J11:O11').copyTo(ds.getRange(16, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //Pin Squat
  s.getRange('R11:W11').copyTo(ds.getRange(21, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //3-0-3 Tempo
  s.getRange('Z11:AE11').copyTo(ds.getRange(26, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //Sq no Belt
  s.getRange('AH11:AM11').copyTo(ds.getRange(31, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
 
  
  //1 Ct Paused Bench Press
  s.getRange('B19:G19').copyTo(ds.getRange(37, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //Overhead Press w/ Belt
  s.getRange('J19:O19').copyTo(ds.getRange(42, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //Close Grip Bench Press
  s.getRange('R19:W19').copyTo(ds.getRange(47, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //2 Ct Paused Bench Press
  s.getRange('Z19:AE19').copyTo(ds.getRange(52, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //3 Ct Paused Bench Press
  s.getRange('AH19:AM19').copyTo(ds.getRange(57, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //3 Ct Paused Bench Press
  s.getRange('AP19:AU19').copyTo(ds.getRange(62, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
  


  //Deadlift w/ Belt
  s.getRange('B27:G27').copyTo(ds.getRange(68, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //Rack Pull - mid shin
  s.getRange('J27:O27').copyTo(ds.getRange(73, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //2 ct Paused Deadlift
  s.getRange('R27:W27').copyTo(ds.getRange(78, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  
    //Pendlay Rows
  s.getRange('Z27:AE27').copyTo(ds.getRange(83, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);


  //get formula back in cells to overwrite manual input


  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B11').activate();
  spreadsheet.getCurrentCell().setFormula('=IF(b9="","",b9)');
  spreadsheet.getRange('B11:G11').activate();
  spreadsheet.getRange('B11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('J11').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('R11').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('Z11:AE11').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('AH11').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('B19').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('J19').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('R19').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('Z19').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('AH19').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('AP19').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('B27').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('J27').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('R27').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('Z27').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('B11').activate();
  spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
};