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); }
Note:
- I think that when the formulas are put using
setFormulas
, the process cost can be reduced a little.