Answer the question
In order to leave comments, you need to log in
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
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);
}
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);
});
}
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question