CopyTo where source and destination can change each time

[Martin Callaghan]

I have question, I know exactly how to do this in excel VBA but I am really struggling to do this in Google Scripts, so I am going ask it in a way to relate to excel VBA, but obviously I need the code for Google Scripts to work in Google sheets.

In excel VBA it looks like this:

·         masterRow = Sheets("Input").Range("C11").Value

·         masterColumn = Sheets("Input").Range("C15").Value

·         Sheets("Input").Range("E4").Select

·         Range(Selection, Selection.End(xlDown)).Copy

·         Sheets("Master").Select

·         Sheets("Master").Cells(masterRow, masterColumn).Select

·         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False

      There are two sheets, “Input” and “Master”. I want to always, start in E4 in the “Input” sheet, then copy from that cell to the last cell in that column which has data (This can be different each time, dynamic range that changes), that’s why I use xlDown.
Then lookup the destination cell that I want in the sheet "Master", which is displayed by two values in the “Input” sheet; again these are always changing, dynamic. Row is in cell “C11”, column is in cell “C15” I get these values from those cells in sheet “Input” Then move to sheet Master, select the cell, and paste special, values only. That’s it.

       I just cannot work out how to write script for Google, where I do not want to tie down a destination range, I want it to be changeable. I just want to say where to the start, I do not want to have to specify the full source range. I am sure there is a way to do this with lastRow, but lastRow always seems to be last row of the whole sheet, not just a single column. I have tried fixing this by havin a third sheet that has a copy of all data from "Input" sheet into Column A:A in this new sheet, and that works, but still not there.
Days of looking at StackOverflow and Youtube have got me close, but no cigar.

I hope someone can help me?

Many Thanks in Advance

MartinWobblyCat

[cwl...@gmail.com]

I created what I think you are trying to do. See if this works for you. I could have declared all the variables to make it more readable, 

but essentially, the "getRange()" method is:  getRange( startingRow,  startingColumn,  numberofRows,  numberofColumns ). 

So in your case:
startingRow,:   inputSheet.getRange("C11").getValue() (assumes this is a numeric value)

startingColumn:   inputSheet.getRange("C15").getValue()

numberOfRows:    data.length

numberOfColumns:  1

function copyData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()

  var inputSheet = ss.getSheetByName("Input")

  var masterSheet = ss.getSheetByName("Master")

  var data = inputSheet.getRange(4,5,inputSheet.getLastRow()-3,1).getValues() //uses "getLastRow() -3" (-3 since the first three rows are skipped)

  masterSheet.getRange(inputSheet.getRange("C11").getValue(),inputSheet.getRange("C15").getValue(),data.length,1).setValues(data)

}

[Kyle Holdridge]

TRY THIS 

You’ll need to use in app script

function myFunction() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var inputSheet = spreadsheet.getSheetByName('Input');

  var masterSheet = spreadsheet.getSheetByName('Master');

  var masterRow = inputSheet.getRange('C11').getValue();

  var masterColumn = inputSheet.getRange('C15').getValue();

  var range = inputSheet.getRange('E4:E');

  var data = range.getValues();

  // Removes empty rows in array

  data = data.filter(function (r) {

    return r[0] !== '';

  });

  masterSheet.getRange(masterRow, masterColumn, data.length, data[0].length).setValues(data);

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3df074ef-2196-4977-941e-f7eb26983cb8n%40googlegroups.com.