Adding the cell value to a formula for a range just uses the first value-CodePudding

What I'm trying to do is only so I can easily do a find and replace in a subsequent step. Otherwise, there is no reason for doing this.

I'm trying to take the values of a cell range and put them in a formula. For example:

=VLOOKUP($A2,IMPORTRANGE("<placeholder_value>","Sheet!A:B"),2,0)

So if I have a table like the following:

| id | group  | formula |
| -- | ------ | ------- |
| 1  | GROUP1 |         |
| 2  | GROUP1 |         |
| 3  | GROUP1 |         |
| 4  | GROUP1 |         |
| 5  | GROUP1 |         |
| 6  | GROUP2 |         |
| 7  | GROUP2 |         |
| 8  | GROUP2 |         |
| 9  | GROUP2 |         |
| 10 | GROUP2 |         |

I'd like the formula for id <=5 to be:

=VLOOKUP($A2,IMPORTRANGE("GROUP1","Sheet!A:B"),2,0)

I'd like the formula for id >5 to be:

=VLOOKUP($A2,IMPORTRANGE("GROUP2","Sheet!A:B"),2,0)

Then as I generate separate files for GROUP1 and GROUP2 I can find a replace in the MASTER with the actual document ID and link the documents via VLOOKUP such that updates in GROUP1 and GROUP2 are reflected in MASTER. That is an oversimplified example.

So what I've tried thus far is doing:

let placeholderRange = this.sheet.getRange(2, this.groupColumn, this.lastRow - 1, 1);
let formula = '='   placeholderRange.getValue();
// let formula = '='   placeholderRange.getValues(); 
// let formula = '='   placeholderRange.getDisplayValue();
// let formula = '='   placeholderRange.getDisplayValues();
formulaRange.setFormula(formula);

In the case of .getValue() and .getDisplayValue() it only uses the first value it finds, so it would put GROUP1 in every formula instead of changing to GROUP2 when it gets to those records.

In the case of .getValues() and .getDisplayValues(), it just adds an array of all the values in that range to the formula.

I can do what I'm after with a for loop, but trying to avoid that initially since that will slow the automation down.

Any suggestions for how to accomplish this?

CodePudding user response：

I believe your goal is as follows.

You want to convert the following situation by reducing the process cost.

From

  id  group   formula
  1   GROUP1
  2   GROUP1
  3   GROUP1
  4   GROUP1
  5   GROUP1
  6   GROUP2
  7   GROUP2
  8   GROUP2
  9   GROUP2
  10  GROUP2

  id  group   formula
  1   GROUP1  =VLOOKUP($A2,IMPORTRANGE("GROUP1","Sheet!A:B"),2,0)
  2   GROUP1  =VLOOKUP($A2,IMPORTRANGE("GROUP1","Sheet!A:B"),2,0)
  3   GROUP1  =VLOOKUP($A2,IMPORTRANGE("GROUP1","Sheet!A:B"),2,0)
  4   GROUP1  =VLOOKUP($A2,IMPORTRANGE("GROUP1","Sheet!A:B"),2,0)
  5   GROUP1  =VLOOKUP($A2,IMPORTRANGE("GROUP1","Sheet!A:B"),2,0)
  6   GROUP2  =VLOOKUP($A2,IMPORTRANGE("GROUP2","Sheet!A:B"),2,0)
  7   GROUP2  =VLOOKUP($A2,IMPORTRANGE("GROUP2","Sheet!A:B"),2,0)
  8   GROUP2  =VLOOKUP($A2,IMPORTRANGE("GROUP2","Sheet!A:B"),2,0)
  9   GROUP2  =VLOOKUP($A2,IMPORTRANGE("GROUP2","Sheet!A:B"),2,0)
  10  GROUP2  =VLOOKUP($A2,IMPORTRANGE("GROUP2","Sheet!A:B"),2,0)

From your script, I supposed that your script might be created as a method in a Class. If my understanding is correct, how about the following modification?

From:

let placeholderRange = this.sheet.getRange(2, this.groupColumn, this.lastRow - 1, 1);
let formula = '='   placeholderRange.getValue();
// let formula = '='   placeholderRange.getValues(); 
// let formula = '='   placeholderRange.getDisplayValue();
// let formula = '='   placeholderRange.getDisplayValues();
formulaRange.setFormula(formula);

To:

const range = this.sheet.getRange(2, this.groupColumn, this.lastRow - 1, 1);
const formulas = range.getValues().map(([v]) => [`=VLOOKUP(\$A2,IMPORTRANGE("${v}","Sheet!A:B"),2,0)`]);
range.offset(0, 1, formulas.length, 1).setFormulas(formulas);

In this modified script, when this.groupColumn is the column of "group", the formula of =VLOOKUP(\$A2,IMPORTRANGE("${v}","Sheet!A:B"),2,0) is created using the values of the column "group".

From:

To:

References: