Google-sheets – How to copy range from one sheet to another column with offset

google sheets

I have a range of data on sheet5 which needs to be copied and inserted 27 columns in from the end of sheet1. I can get it to insert the column in the right place but it doesn't copy the data. I am new to scripting so any advice would be of great help.

Here is code I am using:

function store() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();

  var source = ss.getRange ("Sheet5!D4:D80");

  var destSheet = ss.getSheetByName("Sheet1");

  var lastCol = destSheet.getLastColumn() -27;

  destSheet.insertColumnAfter(lastCol);

  source.copyTo(destSheet.getRange(lastCol + 1,1), {contentsOnly: true});

  SpreadsheetApp.getUi().alert('Played today Recorded');
}

Best Answer

Isn't it always the case. You spend hours trying to figure something out, then as soon as you ask someone the answer arrives. For anyone wondering, here is the correct code.

function store() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();

  var source = ss.getRange ("Sheet5!D4:D80");

  var destSheet = ss.getSheetByName("Sheet1");

  var lastCol = destSheet.getLastColumn() -27;

  destSheet.insertColumnAfter(lastCol);

  source.copyTo(destSheet.getRange(4,lastCol +1,80), {contentsOnly: true});

   SpreadsheetApp.getUi().alert('Played today Recorded');
}

Code

function onEdit(e) { 
  var sName = e.source.getActiveSheet().getSheetName();  
  if(sName !== "Logging") {
    var value;
    var mA1 = e.range.getA1Notation().split(":")[0];
    var time = new Date().toTimeString();    
    if(typeof(e.value) == 'string') {
      var aCell = e.source.getRange(mA1);
      value = aCell.getValue();
      var form = "'" + aCell.getFormula();
    } else {
      value = e.value;      
    }
    var data = [sName, mA1, time, value, form];
    e.source.getSheetByName("Logging").appendRow(data);
  }
}

Explained

The onEdit will only work, if the active sheet isn't named Logging. Next is to gather the wanted info and append it to the logger sheet. The range of the active cell needs to be a cell reference (A1). A formula, with other references, will mix up the A1Notation. Therefore the split(":")[0] will take the first active cell reference (if you add a = or +) as range. If the cell happens to have a formula, then the value is obtained through the e.source else through the e.value. The formula is also obtained through the e.source and stringified.

Screenshot

enter image description here

Example

I've created an example file for you: Logger Sheet

Best Answer

Related Solutions

Google-sheets – Copy subset of rows from one sheet to another, based on value in column

Google-sheets – Updating logging sheet from another sheet doesn’t work after inserting code offset

Code

Explained

Screenshot

Example

Related Topic