2
votes

I've been working on a script (With some help from folk on SO). I have managed to create a script that imports a csv file and updates named ranges based on the imported data.

Here is an image of what I'm trying to do: enter image description here

At this point I have imported data which has been populated into columns A:G. The cells in columns H:L are formula and are based on the data in A:G. Because I have imported data the data are now longer than the formula range. If not relying on script I would just double click the small blue square at the bottom right of the highlighted cells and the formulas would copy down. This is what I'd like to have the script do once I have imported the data.

I created a range called "formula_range" which automatically updates with the length of data. "formula_range" starts in cell H3 and ends in L:N where N is the length of rows in the sheet.

"formula_range" therefore contains some populated cells with formula and then blank rows all the way to the bottom of the sheet.

I saw this SO post. So in English, my line of thinking is:

  1. Create a variable formula_range H3:L3 as a range
  2. paste formula_range to every row in formula_range
  3. Voila?

How do I get the variable formula_range if the first row in formula_range will always have the formula to be copied down. Put another way it should be fixed at H3:L3.

I tried this:

var copy_range = ss.getRangeByName("formula_range").getRange(1,5,1);
SpreadsheetApp.getUi().alert(copy_range);

My alert said "undefined" (I really wasn't sure what it would show.)

My line of thinking is not the most efficient since I am going to copy over existing formula. Perhaps there is a better way?

How do I get the first row in a named range formula_range[0]; ?

How can I use script to copy down formula in formula_range?

1

1 Answers

4
votes

I think what you're trying to accomplish could be achieved more efficiently with a built-in arrayformula:

https://support.google.com/docs/answer/3093275

Place in H3:

=ARRAYFORMULA(DATE(A3:A,B3:B,1))

for each of your formulas in H through L in row 3 surround them with array formula and extend the arguments with open-ended ranges starting with row 3 as in the above example