Forum Discussion
Increase Office Script Run Speed
Hi I have set up multiple office scripts to automate mapping data.
We have a "Master sheet" which is the source to all of the data required. I have then used formulas to pull over the relevant information based on the column title and align it etc. There are a lot of demands and it has been working fine but i now need this to run up to row 5000 and this has obviously slowed down the script.
As a result I now need to revise the full script to maximise efficiency where I can. Im very new to script hence why i'm using formulas to do a lot of the fetching and the work where i'm sure there is another solution.
any help would be appreciated. Script is below:
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);
// Rename Agent Field
selectedSheet.getRange("C1").setValue("Service Type/Branch");
// Populate Column C - Agent
selectedSheet.getRange("C2").setFormulasR1C1("=Trim(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("C2").autoFill("C2:C3000", ExcelScript.AutoFillType.fillDefault);
// Rename Monthly Payment Required
selectedSheet.getRange("E1").setValue("Rent Amount");
// Populate Column E - Rent Amount
selectedSheet.getRange("E2").setFormulaR1C1("=IFERROR(VALUE(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("E2").autoFill("E2:E3000", ExcelScript.AutoFillType.fillDefault);
// Rename Property Account Field
selectedSheet.getRange("f1").setValue("Agreed Float Amount");
// Populate Column F - Agreed Float Amount
selectedSheet.getRange("F2").setFormulaR1C1("=IFERROR(VALUE(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("F2").autoFill("F2:F3000", ExcelScript.AutoFillType.fillDefault);
// Populate Column M Address 1
selectedSheet.getRange("M2").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("M2").autoFill("M2:M3000", ExcelScript.AutoFillType.fillDefault);
// Populate Column N - Address 2
selectedSheet.getRange("N2").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("N2").autoFill("n2:n3000", ExcelScript.AutoFillType.fillDefault);
// Populate Column O - Address 3
selectedSheet.getRange("o2").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("o2").autoFill("o2:o3000", ExcelScript.AutoFillType.fillDefault);
// Populate Column P - City
selectedSheet.getRange("p2").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("p2").autoFill("p2:p3000", ExcelScript.AutoFillType.fillDefault);
// Populate Column Q - County
selectedSheet.getRange("q2").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("q2").autoFill("q2:q3000", ExcelScript.AutoFillType.fillDefault);
// Populate Column R - Post Code
selectedSheet.getRange("r2").setFormulasR1C1("=Trim(Upper(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("r2").autoFill("r2:r3000", ExcelScript.AutoFillType.fillDefault);
// Populate Column S - Country
selectedSheet.getRange("s2").setFormulasR1C1("=Trim(Upper(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("s2").autoFill("s2:s3000", ExcelScript.AutoFillType.fillDefault);
let conditionalFormatting: ExcelScript.ConditionalFormat;
// Highlight Property Name If Duplicate
conditionalFormatting = selectedSheet.getRange("B2:B3000").addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
conditionalFormatting.getPreset().setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues });
conditionalFormatting.getPreset().getFormat().getFill().setColor("#ffc7ce");
conditionalFormatting.getPreset().getFormat().getFont().setColor("#9c0006");
conditionalFormatting.setStopIfTrue(false);
conditionalFormatting.setPriority(0);
// Highlight Monthly Amount If Blank
conditionalFormatting = selectedSheet.getRange("E2:F3000").addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
conditionalFormatting.getPreset().setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks });
conditionalFormatting.getPreset().getFormat().getFill().setColor("#ffc7ce");
conditionalFormatting.getPreset().getFormat().getFont().setColor("#9c0006");
conditionalFormatting.setStopIfTrue(false);
conditionalFormatting.setPriority(0);
// Highlight Property Acc Minimum Balance If Blank
conditionalFormatting = selectedSheet.getRange("F2:F3000").addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
conditionalFormatting.getPreset().setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks });
conditionalFormatting.getPreset().getFormat().getFill().setColor("#ffc7ce");
conditionalFormatting.getPreset().getFormat().getFont().setColor("#9c0006");
conditionalFormatting.setStopIfTrue(false);
conditionalFormatting.setPriority(0);
// Highlight Country If Blank
conditionalFormatting = selectedSheet.getRange("S2:S3000").addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
conditionalFormatting.getPreset().setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks });
conditionalFormatting.getPreset().getFormat().getFill().setColor("#ffc7ce");
conditionalFormatting.getPreset().getFormat().getFont().setColor("#9c0006");
conditionalFormatting.setStopIfTrue(false);
conditionalFormatting.setPriority(0);
// Set alignment to left
selectedSheet.getRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);
// Indent set to 0 Left
selectedSheet.getRange().getFormat().setIndentLevel(0);
// Set Header alignment to Middle
selectedSheet.getRange("1:1").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
selectedSheet.getRange("1:1").getFormat().setIndentLevel(0);
// Indent set to 0 for Header alignment to Middle
selectedSheet.getRange("1:1").getFormat().setIndentLevel(0);
// Set font name to "Calibri"
selectedSheet.getRange("A1:U3000").getFormat().getFont().setName("Helvetica Neue");
// Set font size to 11
selectedSheet.getRange("A1:U3000").getFormat().getFont().setSize(11);
// Copy Paste Values All
selectedSheet.getRange().copyFrom(selectedSheet.getRange(), ExcelScript.RangeCopyType.values, false, false);
}
8 Replies
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.
