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
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.

      • excelhelp2280's avatar
        excelhelp2280
        Copper Contributor
        Feb 06, 2024
        Thank you, ive got that to work. Do you think this will work better/more efficient than using the formulas?
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 05, 2024

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

Resources