How to stretch the formula by the script along the column "J:J", to the last filled cell, focusing on the column "C:C"?

Question

Google Apps Script

Hello, I really need your help. Need a script to determine the last filled cell in the "J:J" column, and extend the formula from this cell along the "J:J" column, to the last filled cell, focusing on the "C:C" column? For example, the formula in the "J:J" column is in the "J321" cell, and the last filled cell in the "C:C" column, "C78", means that the formula needs to be extended from the "J32" cell to "J78". Complete zero, so please, please help, I really need help.

Answer 1

You need something like this

/**
 * @file Dragg down a formula
 * @url https://qna.habr.com/q/709715
 * */

/**
 * User action. Runs the snippet
 */
function run() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const base = sheet.getRange('C3:C');
  const colFormula = sheet.getRange('J3');
  draggDownFormulas_(base, colFormula);
}

/**
 * @param {GoogleAppsScript.Spreadsheet.Range} base
 * @param {GoogleAppsScript.Spreadsheet.Range} colFormula
 */
function draggDownFormulas_(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)
    .setFormula(colFormulaFormula);
}

note

• check starts from line 3
  
• the column Cis filled one line lower than the data array
  
• copy the formula fromJ3
  
• for the code to work you need to enable V8
  

snippet

Answer 2

It is necessary that the filling be in the adjacent column . So the easiest way is to move the columns so that they are side by side. For example, column C:C can be moved or copied (duplicated) to column I:I or K:K. Then in the J:J column, you can simply double-click on the lower right corner in the stretchable cell (the place you usually stretch beyond) - and the formula itself will spread to the last row.

Answer 3

Good day!
The method with the active sheet of the table is described here: const sheet = SpreadsheetApp.getActiveSheet();
Tell me, if I need a script for a specific table and sheet (I used the variables SpreadsheetApp.openByUrl('xxxxx') and table.getSheetName('Sheet1')) before, how can I correctly access a specific sheet of the table with this script? Thank you!