0

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();
};
  • The is something weird with the linked spreadsheet. When I open the "Program" sheet it shows the recalculation progress bar but it doesn't show any progress, after a few minutes it displays the sheet content. Because there could be problems like this with external content, all the relevant information should be included on the question itself. By the other hand the structure of the spreadsheet is complex, please make a minimal complete example (remove any unnecessary content like the charts, minimize the number of columns / shows to include only a couple of examples) – Rubén Sep 20 at 21:25
  • Hi Rubén: I appreciate you having a look. I made a very simple example sheet and updated the link above. The sheet is editable... All help sincerely appreciated! – Benjamin Bulloch Sep 21 at 1:55
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.