ARTICLE AD BOX
In Office Scripts you shouldn’t hardcode the destination range. Instead, you can determine the last used row of column Z and then use that row number to build the autofill range dynamically.
Column Z already contains the data that defines how far the formulas should go, so the correct approach is:
Get column Z as a range.
Find the last row that actually contains data.
Use that row index to construct the autofill destination range.
Autofill only down to that row.
Office Scripts provides getUsedRange() and getRowCount() for this Once you calculate the last used row in column Z, you can autofill columns AA–AD to match it, no matter if that row is 50 or 5,000.
function main(workbook: ExcelScript.Workbook) const sheet = workbook.getActiveWorksheet(); sheet.getRange("AA26:AD27").setFormulas([ ["FAK", "FK", "PN", "STEEL"], [ "=S27&R27", "=XLOOKUP(AA27,'[FAKEFILENAME.xlsx]FAKE VENDOR'!$A:$A,'[FAKEFILENAME.xlsx]FAKE VENDOR'!$B:$B)", "=\"PIPE SPOOL \"&U27", "=XLOOKUP(AC27,'[FAKEFILENAME.xlsx]PARTS'!$B:$B,'[FAKEFILENAME.xlsx]PARTS'!$F:$F)" ] ]); // Add this const colZUsed = sheet.getRange("Z:Z").getUsedRange(); if (!colZUsed) return; const lastRowZ = colZUsed.getRowIndex() + colZUsed.getRowCount(); const endRow = Math.max(lastRowZ, 27); sheet .getRange("AA27:AD27") .autoFill(`AA27:AD${endRow}`, ExcelScript.AutoFillType.fillDefault); }