Search code examples
google-apps-scriptgoogle-sheetsmacro-recorder

Copy 12 cells from user selected row in 1 sheet to sheet 2


I am trying to copy a user selected row in sheet Responses 1 to Sheet 2. The auto record macro function always copies an absolute location instead of a relative one even thought relative is selected. I've tried a number of ways to alter it with no success, I used to like programing in basic and dbase may years ago but not done anything since, so i have a lot of learning to do, thank you in advance.

function relative1() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().offset(0, 0, 1, 12).activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
  spreadsheet.getRange('\'Form Responses 1\'!A3:L3').copyTo(spreadsheet.getActiveRange(), 
SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Form Responses 1'), true);
  spreadsheet.getCurrentCell().offset(1, 0).activate();
};

Solution

  • Copy row from one tab to another

    function copy() {
      const ss = SpreadsheetApp.getActive();
      const ssh = ss.getActiveSheet();
      const tsh = ss.getSheetByName("Sheet1");
      const r = ssh.getActiveRange().getRow();
      const vs = ssh.getRange(r,1,1,ssh.getLastColumn()).getValues();
      tsh.getRange(tsh.getLastRow() + 1, 1, vs.length, vs[0].length).setValues(vs); 
    }