B
B
Bogdan G2021-12-02 08:48:07
Google Apps Script
Bogdan G, 2021-12-02 08:48:07

Dragging draggDownFormulas formula by App Script in google spreadsheet?

Kind time of the day
There is a simple function that "stretches" formulas down ( taken from here ) to the last filled line.
The question is, how can it be repeated, within the same function, so that not only one column is filled (for example, "J"), but also the adjacent one (for example, "K"). That is, on one call, the formula was stretched in two columns, and not one.

An attempt to write an array of the form (3, 7, 2, 2) - did not bring any result, just duplicate it with other values ​​- gives an error.

function run2() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const base = sheet.getRange('J3:J');
  const colFormula = sheet.getRange('J3');
  draggDownOneFormula_(base, colFormula);
}

function draggDownOneFormula_(base, colFormula) {
  const baseValues = base.getValues();
  const lastBase =
    baseValues.length - baseValues.reverse().findIndex(row => row[0] !== '');
  const colFormulaFormula = colFormula.getFormula();
  colFormula
    .getSheet()
    .getRange(base.getRow(), colFormula.getColumn(), lastBase + 1)
    .setFormula(colFormulaFormula);
}

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Ivanov, 2021-12-03
@Bogdan9999

Unfortunately, you will have to specify new formulas for "stretching" every time

function run() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const base = sheet.getRange('I3:I');
  const colFormula = sheet.getRange('J3');
  draggDownFormulas_(base, colFormula);
  const colFormula2 = sheet.getRange('K3');
  draggDownFormulas_(base, colFormula2);
}

Well, or a more advanced version
function runBulk() {
  const formulasCells = ['J3', 'K3', 'M3'];
  const sheet = SpreadsheetApp.getActiveSheet();
  const base = sheet.getRange('I3:I');
  formulasCells.forEach((cell) => {
    const colFormula = sheet.getRange(cell);
    draggDownFormulas_(base, colFormula);
  });
}

I am sure that with good refactoring it will be possible to "solve" some of the features of "pulling".

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question