Google sheets How make a macro that paste a relative range

0
votes

I'm Spanish speaking. I tried to record a macro with these instructions for keyboard shortcuts.

First, I select relative references because I need that from my current row. It moves two cells left, copy from this cell to the start of the current row.

Double click left, Ctrl shift left , Ctrl c, Click in the specific sheet, Ctrl start, Ctrl down , Click down , Paste only values,

And save the macro

When I go the Script editor, this is the code that was recorded

function real() {

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getCurrentCell().offset(0, -2).activate();
  var currentCell = spreadsheet.getCurrentCell();
  spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.PREVIOUS).activate();
  currentCell.activateAsCurrentCell();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Presupuestos'), true);
  spreadsheet.getCurrentCell().offset(0, 0).activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getCurrentCell().offset(1, 0).activate();
  spreadsheet.getRange('Correlativos!A2:d2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

But always paste the same range A2:D2 and I need that copy and paste from column A to column D of my current row.

I always am located in column F of my current row, and I move two cells left because I need to copy the whole range from column A to column D of the current row.

Please, I would appreciate any help.

1
google-apps-scriptgoogle-sheets

1 Answers

1
votes

You have the cursor on a cell in Column F of Sheet = "Correlativos". You want to copy cells A:D of the current row of that to Columns A to D on the same row on sheet = "Presupuestos". However you macro ignores the cursor location and always copies range ="A2:D2".

The reason why the macro always copies "A2:D2" is because that range is hard-coded into the last line of the macro: spreadsheet.getRange('Correlativos!A2:d2')....

Instead of coding a specific range, you want the range to copy the same row as the cursor is in. Try the following script. You'll note that it is lot shorter:

  • var row = correlativos.getCurrentCell().getRow();: this line returns the row number of the current cell on sheet = "Correlativos".
  • the last line (correlativos.getRange(row,1,1,4).copyTo...)is almost identical to line fro the macro, but look at the getRange() method. The range is defined by the "row, column, numRows, numColumns".
    • row: this was captured by the variable "row",
    • column: the starting column is Column A = 1,
    • numrows: equals 1, you only want to copy data fro the current row.
    • numColumns: equals 4 (Columns A, B, C and D).
function so6068695301() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var presupuestos = ss.getSheetByName("Presupuestos");
  var correlativos = ss.getSheetByName("Correlativos");


  // the cursor will be located in Column F of Sheet = Correlativos
  // goal to copy the Range A:D of the current row from Correlativos to Presupuestos
  var row = correlativos.getCurrentCell().getRow();

  correlativos.getRange(row,1,1,4).copyTo(presupuestos.getRange(row,1,1,4),SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);

}