Increase Office Script Run Speed

Question

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

}

sergeibaklan · Answer

excelhelp2280&nbsp;
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()
  )
}

excelhelp2280 · Answer

SergeiBaklan&nbsp;&nbsp;The issue is I am using the formula&nbsp;&nbsp;=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)))&nbsp;for example to find the name. This is always column B on the template however it could be in any column&nbsp;on the master sheet.&nbsp;&nbsp;Will your examples cater for the name potentially moving to another column?

sergeibaklan · Answer

excelhelp2280&nbsp;
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.

excelhelp2280 · Answer

SergeiBaklan&nbsp;Sorry im getting this error&nbsp;

sergeibaklan · Answer

excelhelp2280&nbsp;
Perhaps it doesn't find the name in G1 within A1:BC1 of the source sheet, when returns -1. Could be extra space in the name or like.
You may add&nbsp;
console.log( source.indexOf(target.getValue()) )
before line 14 to check. And/or compare cells directly in the grid. Perhaps something else but above is first in mind.
In addition, this script iterates target names

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook
    .getActiveWorksheet();

const source = workbook
    .getWorksheet("Sheet1")
    .getRange("A1:BC1")
    .getUsedRange()
    .getValues()[0]
  
  const maxRowsInSource = 10000

const targetNames = selectedSheet
    .getRange("F1:H1")

const numberOfNames = targetNames
    .getCellCount()

const headers = targetNames
    .getValues()[0]

for (let i = 0; i &lt; numberOfNames; ++i) {
    targetNames
      .getCell(0, i)
      .copyFrom(
          workbook
            .getWorksheet("Sheet1")
            .getRangeByIndexes(
              0,
              source.indexOf( headers[i] ),
              maxRowsInSource,
              1
            )
        .getUsedRange()
      )
  }
}

Forum Discussion

Increase Office Script Run Speed

Share

Resources