Forum Discussion
Increase Office Script Run Speed
Depends on goals, would you like to copy values from the source or populate formulae as well. If to copy values, for example from columns C:L, it could be
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Populate Column B - Property Name
/*
selectedSheet.getRange("B2").setFormulasR1C1("=Trim(Proper(IF(ISBLANK(INDEX(Sheet1!C1:C100,ROW(),MATCH(R1C,Sheet1!R1C1:R1C100,0))),\"\",INDEX(Sheet1!C1:C100,ROW(),MATCH(R1C,Sheet1!R1C1:R1C100,0)))))");
// Auto fill range
selectedSheet.getRange("B2").autoFill("B2:B3000", ExcelScript.AutoFillType.fillDefault);
*/
const source = workbook
.getWorksheet("Sheet1")
.getRange("C:L")
.getUsedRange()
selectedSheet
.getRange("B1")
.copyFrom(source)
}
or
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook
.getActiveWorksheet();
selectedSheet
.getRange("B1")
.copyFrom(
workbook
.getWorksheet("Sheet1")
.getRange("C:L")
.getUsedRange()
)
}
The issue is I am using the formula
=IF(ISBLANK(INDEX(Master!$A:$BC,ROW(),MATCH(D$1,Master!$A$1:$BC$1,0))),"",INDEX(Master!$A:$BC,ROW(),MATCH(D$1,Master!$A$1:$BC$1,0)))
for example to find the name. This is always column B on the template however it could be in any column on the master sheet.
Will your examples cater for the name potentially moving to another column?
Let me try for the one column first. Assuming name in G1 of active sheet we find the column with the same name in Sheet1 and copy it to the column G of active sheet
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook .getActiveWorksheet(); const source = workbook .getWorksheet("Sheet1") .getRange("A1:BC1") .getUsedRange() .getValues()[0] const target = selectedSheet .getRange("G1") selectedSheet .getRange("G1") .copyFrom( workbook .getWorksheet("Sheet1") .getRangeByIndexes( 0, source.indexOf(target.getValue()), 10000, 1) .getUsedRange() ) }if so based on this we may iterate all names in active sheet.
