How do I add a cell (range) to a =TEXTJOIN formula via Apps Script in Google Sheets?

139 Views Asked by At

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

0

There are 0 best solutions below