search/match a date on Google Sheets using Google script and copy a source range to a destination range OFFSET from that date

Question

UPDATE: I FIGURED IT OUT.

I'm struggling with some simple copy/paste scripting in Google 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 a range on Today
2. search the date row in "Program" to Match whatever date is on the "Today" data entry page.
3. Finally paste data to a range on Program xx rows down from that identified date in step 2

Essentially I want to INDEX/MATCH in reverse. Rather than return a cell in INDEX/MATCH, I want to copy to a cell.

Simplified Sheet is here with additional notes in the Sheet: https://docs.google.com/spreadsheets/d/1i66I4kI733dTzoYFgsgDLRl4XWhFQc7gI-vAYKfC1Jo/edit?usp=sharing

Below is as far as I've gotten. I can cut/paste from a specific cell to another cell. But need to figure out "var destination" somehow.

function copydata2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange('Today!b11:g11');

  source.copyTo(ss.getRange('Program!d11:i11'), {contentsOnly: true});
  source.clear();
};

Answer 1

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);
};