0

How can I add more cells/ranges to a formula via Apps Script?

The formula looks like this:

=TEXTJOIN(", ",true, G11:G1007)

It should look like this after running the script:

=TEXTJOIN(", ",true, G11:G1007, I11:I1007)

I have assigned the following script (via Macro Recorder) to a button in Google Sheets to automatically select a range and add the values from I11:I1007 to the formula in cell A12:

function add_range_values_to_A12() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A12').activate();
  select_range_in_column_I();
  spreadsheet.getRange('A12').activate();
  spreadsheet.getCurrentCell().setFormula('=TEXTJOIN(", ",true, I11:I1008)');
  spreadsheet.getRange('A13').activate();
};

Now I have a script that selects the additional range. The values in I11:I1007 should be added, so that I get the following formula in A12:

=TEXTJOIN(", ",true, **G11:G1007**, **I11:I1007**)

What I get instead is an override of the formula:

=TEXTJOIN(", ",true, K11:K1007)

A possible solution to add cells to the already existing formula would save the world. T

Twilight
  • 1,399
  • 2
  • 11
  • Another thing that you might do is to record again the macro by following the suggestions in my answer to https://stackoverflow.com/q/73327526/1595451, then adapt that new macro to your needs. – Rubén Nov 14 '22 at 23:15
  • Thanks a ton for your suggestions, @Rubén. P.S. I never claimed to be the author of the code, but I will adjust my question accordingly and give the MacroRecorder a deserved mention. –  Nov 14 '22 at 23:30
  • "I never claimed to be the author of the code" Actually you did that when you didn't give the proper attribution. – Rubén Nov 14 '22 at 23:59
  • It is clear to me, even as a newbie, that setFormula overwrites =TEXTJOIN in this case. Is there a solution like adjustFormula, or appendtoFormula? Do you understand my question, @Rubén? I want to press a button and from many ranges interactively add a certain range to A12 via Apps Script. –  Nov 15 '22 at 00:00
  • Please bear in mind that this site is for questions from people who writes code. Do you understand the code in the question? Are you able to write code? Do you already know the JavaScript basics? Do you know how to to declare a variable and how to concatenate strings? – Rubén Nov 15 '22 at 00:03
  • I thought this community was for learning from shared practical examples. Still, thanks for pointing out that I should go learn - I would never have figured it out without your help. –  Nov 15 '22 at 00:06
  • See [What is the proper way to approach Stack Overflow as someone totally new to programming?](https://meta.stackoverflow.com/q/254572/1595451) and [my most recent answer](https://meta.stackoverflow.com/a/421471/1595451) to a post about basic and general questions in [meta]. – Rubén Nov 15 '22 at 00:10

0 Answers0