asked on

Google Scripts - copying dynamic named range.

Hi all,

I'm trying to learn Google Scripts, and have hit an impasse of sorts...

I have a simple Sheet that has some named ranges. Two of them are: Dogs, Cats.

The named ranges can grow dynamically (A1:A), for example.

I'm trying to write code that takes all the dogs from the named range "Dogs" (A1:A) and write them to another named Range in Column D.

But the code I wrote seems to only pick up the first value and repeat it. Is this a case where I need some kind of Array? Here is the code I wrote, and thanks for your cosideration:

/////////////////////////////////////////////////////////

function myTest() {

var ss = SpreadsheetApp.getActiveSpreadsheet(); // defines the active spreadsheet
var sheet = ss.getActiveSheet(); // defines the active sheet
var myNamedRange = 'dogs'; // defines the named range that you want to copy. do we need to set up an array???
var myDest = 'destiny'; // defines the named range that you want to *write* to
var myRange = sheet.getRange(myNamedRange).getValue(); // grab the value(s)
var range = sheet.getRange(myDest); // goes to the destination range name address on the active sheet

range.setValue(myRange); // writes to the range

}

ASKER CERTIFIED SOLUTION

Koen Van Wielink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Go-Bruins

ASKER

Thank you. The reason behind using so many variable is that I can re-use the code (e.g. Cats range, etc.). I don't know if my reasoning holds water, and I certainly like to keep code to a minimum :)

I don't know if I can specify that the "from" range will always be the same as the "to" range. I imagine the Dogs list growing as I go. is it just a matter of watching the list and always manually checking that the Dogs range and the Destiny range are always the same size in terms of absolute number of cells?

Or can I have the code check the Dogs range and then dynamically growing the Destiny range to match? I wouldn't even have the foggiest notion of where to start. I suppose it would be some kind of for-loop?

Koen Van Wielink

What you want to do is identify the last cell in the range of dogs that has a value. You then know the length, which you can use to set the target range. I don't think you'd need a loop in this case. I'd work it out for you but unfortunately I fell sick this morning so my mind's not really working.

Go-Bruins

ASKER

Thank you.