Increase Office Script Run Speed

[excelhelp2280]

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

}

[#2]

@excelhelp2280 

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()
  )
}

[#3]

@SergeiBaklan 

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?

[#4]

@excelhelp2280 

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.

[#5]

@SergeiBaklan Sorry im getting this error 

 

[#6]

@excelhelp2280 

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 

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 < numberOfNames; ++i) {
    targetNames
      .getCell(0, i)
      .copyFrom(
          workbook
            .getWorksheet("Sheet1")
            .getRangeByIndexes(
              0,
              source.indexOf( headers[i] ),
              maxRowsInSource,
              1
            )
        .getUsedRange()
      )
  }
}

[#7]

@excelhelp2280 

Just in case attached is the file on which I tested

[#8]

Thank you, ive got that to work. Do you think this will work better/more efficient than using the formulas?

[#9]

@excelhelp2280 

Great to know you sorted that out. Yes, performance shall be better. In addition you may adjust conditional formatting, but that's more cosmetic. Something like

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("A1:BC1")

  const numberOfNames = targetNames
    .getCellCount()

  const headers = targetNames
    .getValues()[0]

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

    const duplicateRule: ExcelScript.ConditionalPresetCriteriaRule = {
        criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues
      }

    const blankRule: ExcelScript.ConditionalPresetCriteriaRule = {
        criterion: ExcelScript.ConditionalFormatPresetCriterion.blanks
      }

    const rangesBlank = selectedSheet
      .getRanges("F2:F3000, I2:I3000")

    const rangesDuplicate = selectedSheet
    .getRanges("F2:F3000")

    rangesBlank.clearAllConditionalFormats()
    rangesDuplicate.clearAllConditionalFormats()

    const conditionalFormatBlanks = rangesBlank.addConditionalFormat(
      ExcelScript.ConditionalFormatType.presetCriteria);
    const conditionalFormatDuplicate = rangesDuplicate.addConditionalFormat(
    ExcelScript.ConditionalFormatType.presetCriteria);

    const presetFormatBlank:
      ExcelScript.PresetCriteriaConditionalFormat = 
        conditionalFormatBlanks.getPreset();
      presetFormatBlank.getFormat().getFill().setColor("lightyellow");

    const presetFormatDuplicate:
      ExcelScript.PresetCriteriaConditionalFormat =
        conditionalFormatDuplicate.getPreset();
      presetFormatDuplicate.getFormat().getFill().setColor("red");
      presetFormatDuplicate.getFormat().getFont().setColor("white");

    presetFormatBlank.setRule(blankRule);
    presetFormatDuplicate.setRule(duplicateRule);

}