Forum Discussion

excelhelp2280's avatar
excelhelp2280
Copper Contributor
Feb 05, 2024

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 i...
Office Scripts
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 05, 2024

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()
  )
}
excelhelp2280's avatar
excelhelp2280
Copper Contributor
Feb 05, 2024

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?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 05, 2024

    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.

Resources