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

Question

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.

Sheet is here: https://docs.google.com/spreadsheets/d/19r6IbgdYCPOZjNr92MA0vAKQ_R2Fx5JohuC0DE8ekPo/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();
};

AMolina AMolina · Accepted Answer · 2019-09-23T08:06:56

Try this:

function test2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var today = ss.getSheetByName("Today");
  var program = ss.getSheetByName("Program");
  var date = today.getRange("B4").getValue();
  var targetDates = program.getRange("D4:KL4").getValues();
  var column;

  // Get the target column.
  for (var i = 0; i < targetDates[0].length; i++){
    if (targetDates[0][i].toString() == date.toString()){
      column = i + 4; // To account for the first cols.
      Logger.log("found the date " + targetDates[0][i] + " at column " + column + " with i = " + i);
      break;
    }
    else continue;
  }

  // Move the values.
  today.getRange("B11:G11").moveTo(program.getRange(11, column)); // Sq w/ Belt
  today.getRange("J11:O11").moveTo(program.getRange(16, column)); // 2ct Paused Squat
  today.getRange("R11:W11").moveTo(program.getRange(21, column)); // Pin Squat
  today.getRange("Z11:AE11").moveTo(program.getRange(26, column)); // 3-0-3 Tempo Squat
  today.getRange("AH11:AM11").moveTo(program.getRange(31, column)); // Sq no Belt

  today.getRange("B19:H19").moveTo(program.getRange(37, column)); // 1 Ct Paused Bench Press
  today.getRange("J19:O19").moveTo(program.getRange(42, column)); // Overhead Press w/ Belt
  today.getRange("R19:W19").moveTo(program.getRange(47, column)); // Close Grip Bench Press
  today.getRange("Z19:AE19").moveTo(program.getRange(52, column)); // 2 Ct Paused Bench Press
  today.getRange("AH19:AM19").moveTo(program.getRange(57, column)); // 3 Ct Paused Bench Press
  today.getRange("AP19:AU19").moveTo(program.getRange(62, column)); // Pin Bench Press

  today.getRange("B27:H27").moveTo(program.getRange(68, column)); // Deadlift w/ Belt
  today.getRange("J27:O27").moveTo(program.getRange(73, column)); // Rack Pull - mid shin
  today.getRange("R27:W27").moveTo(program.getRange(78, column)); // 2 ct Paused Deadlift
  today.getRange("AH27:AM27").moveTo(program.getRange(83, column)); // Pendlay Rows
}

A couple things, I used moveTo() (documentation here), which cuts/pastes the values to the target, the only issue is that it overwrites the style format. When working with Sheets it's always better to use getValues() and setValues() to avoid making multiple calls to the API. In this case, however, since your data was organized in such a particular way it was better to work like this for complexity's sake. Please let me know if you have any doubts. I tested it on a copy of the sheet you provided and it worked as intended.

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

1 Answers