How To Set A1notation For All Row (Just Set Column Initial)

February 17, 2023

I want to Set Formula for some cell, depend on how much data I have.
The problem was, I can’t write script to make Range without Row Initial ( e.g Range(A:AA)).
To make just Column Initial, I try to replace Parameter : lastCol and lastRow with blank string (”) , and Zero Number (0), but not work.
Thanks

function recap() {

var   sheet             =   SpreadsheetApp.getActiveSpreadsheet()
var   sheetForm         =   sheet.getSheetByName('METER')
const sheetPrint        =   sheet.getSheetByName('CETAK TAGIHAN')
const n                 =   5
var   lastRow           =   sheetForm.getLastRow()
var   lastCol           =   n+4
const startRow          =   7 
const currentCol        =   3 

for (let i = 0 ; i < n; i++){

// FORMULA =vlookup((max(METER!A:A)),METER!A:I,5)

// HOW TO SET ALL COLUMN RANGE :(A:I) WITHOUT ROW NUMBER

sheetPrint.getRange(i+startRow,currentCol).setFormula('vlookup((max(METER!A:A)),METER!'+sheetForm.getRange(1,1,lastRow,lastCol).getA1Notation()+','+(5+i)+')');

}

}

>Solution :

In your situation, how about the following modification?

Modified script:

function recap() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheetForm = sheet.getSheetByName('METER')
  const sheetPrint = sheet.getSheetByName('CETAK TAGIHAN')
  const n = 5

  const lastColumn = sheetForm.getLastColumn();
  const columnIndexToLetter_ = index => (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : ""; // ref: https://stackoverflow.com/a/53678158
  const columnLetter = columnIndexToLetter_(lastColumn - 1);
  const formulas = [...Array(n)].map((_, i) => [`=vlookup((max(METER!A:A)),METER!A:${columnLetter},${i + 5})`]);
  sheetPrint.getRange(7, 3, formulas.length).setFormulas(formulas);
}

When this script is run, the following formulas are put to the cells "C7:C11".

  =vlookup((max(METER!A:A)),METER!A:I,5)
  =vlookup((max(METER!A:A)),METER!A:I,6)
  =vlookup((max(METER!A:A)),METER!A:I,7)
  =vlookup((max(METER!A:A)),METER!A:I,8)
  =vlookup((max(METER!A:A)),METER!A:I,9)

If the value of METER!A:I is constant, I think that the following modification might be able to be used.

function recap() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheetForm = sheet.getSheetByName('METER')
  const sheetPrint = sheet.getSheetByName('CETAK TAGIHAN')
  const n = 5
  const formulas = [...Array(n)].map((_, i) => [`=vlookup((max(METER!A:A)),METER!A:I,${i + 5})`]);
  sheetPrint.getRange(7, 3, formulas.length).setFormulas(formulas);
}

As another approach, the following modified script might be able to be used. In this case, the number values are removed from a1Notation.

function recap() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheetForm = sheet.getSheetByName('METER')
  const sheetPrint = sheet.getSheetByName('CETAK TAGIHAN')
  const n = 5
  const a1Notation = sheetForm.getRange(1, 1, 1, sheetForm.getLastColumn()).getA1Notation().replace(/\d/g, "");
  const formulas = [...Array(n)].map((_, i) => [`=vlookup((max(METER!A:A)),METER!${a1Notation},${i + 5})`]);
  sheetPrint.getRange(7, 3, formulas.length).setFormulas(formulas);
}