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

Question

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)+')');

}

}

Answer 1

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);
  }
  

Note:

• I think that when the formulas are put using setFormulas, the process cost can be reduced a little.