Share via

Office Script performance improvement with inserting rows

Ipoole 0 Reputation points
2023-01-23T18:45:44.6233333+00:00

I have an office script I am working through with a Power Automate that I am trying to make run faster.

The particular bit that slows the whole thing down is below, though I have included the rest at the bottom. My goal here is to look for the ID of the individual and then capture all their rows (which is variable per person), then create a subtotal row that I can then update with data protection.

There is probably a much better way than using offsets to do this, but as my first attempt I couldn't figure out a better one. I am trying to improve both in terms of efficiency and in terms of error catching.

The timeout included is because it is being called from Power Automate and if it goes longer than 2 minutes it will always fail.

  if (sheet2.getUsedRange().getCellCount() > 1) {

    var uidrows = uidrowsstrings.split(",")

  } else {

    var uidrows = uidrowsstrings.split(" ")

  }

  for (const value of uidrows) {

    let duration = new Date().getTime() - start

    let elapsed = duration / 1000

    if (elapsed > 70) {

      var lastuid = value

      break

    } else {

      var lastuid = ""

    }

    let foundUID = newgrantsheet.findAll(value, { completeMatch: true })

    let rowbelow = foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(1, 0)

    rowbelow.getEntireRow().insert(ExcelScript.InsertShiftDirection.down)

    let workedrow = foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(1, 0)

    workedrow.getCell(0, 0).setValue(value)

    workedrow.getCell(0, 1).setValue("Total")

    let sumformula = "=sum(" + foundUID.getAreas()[0].getOffsetRange(0, 7).getAddress() + ":" + foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(0, 7).getAddress()

    let topprojectedfirst = foundUID.getAreas()[0].getOffsetRange(0, 13).getAddress()

    let bottomprojectedfirst = foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(0, 13).getAddress()

    let bottomprojectedlast = foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(0, 24).getAddress()

    let firstprojectedrange = topprojectedfirst.replace(topprojectedfirst.replace(/\D/g, ""), "") + "$" + topprojectedfirst.replace(/\D/g, "") + ":" + bottomprojectedfirst.replace(bottomprojectedfirst.replace(/\D/g, ""), "") + "$" + bottomprojectedfirst.replace(/\D/g, "")

    let allprojected = topprojectedfirst + ":" + bottomprojectedlast

    let datavalidationformula = "=sum(" + firstprojectedrange + ")<=1"

    let cellarray = Array.from(Array(foundUID.getCellCount()).keys())

    newgrantsheet.getRange(allprojected).getDataValidation().setRule({ custom: { formula: datavalidationformula } })

    newgrantsheet.getRange(allprojected).getDataValidation().setPrompt({ showPrompt: true, message: "Enter projected percentage as decimal number", title: "Enter Projected Percentage" })

    newgrantsheet.getRange(allprojected).getDataValidation().setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.stop, message: "Projections can't go over 100%, please correct projections.", title: "Projections not over 100%" })

    newgrantsheet.getRange(allprojected).getDataValidation().setIgnoreBlanks(true);

    // Unlock the fields necessary then protect the whole sheet

    newgrantsheet.getRange(allprojected).getFormat().getProtection().setLocked(false)

    let sumsstart = workedrow.getOffsetRange(0, 7).getCell(0, 0)

    sumsstart.setValue(sumformula)

    let sumsend = workedrow.getOffsetRange(0, 24)

    sumsend.getExtendedRange(ExcelScript.KeyboardDirection.left).copyFrom(sumsstart)

  }

Below is the main script. It includes an example to attempt to redo the data.

function main(workbook: ExcelScript.Workbook, csv: string): string {
  //CSV to Excel part:
  const start = new Date().getTime();
  let sheet = workbook.getWorksheet("Sheet1");
  // Remove any Windows \r characters.
  //below used for testing. delete comments to utilize
  csv = "AN_ID,EMPLOYEE,Grant,Funded_Program,Employee_Group,Funds_Center,SP_Valid_To,End_Date_of_Payroll_Period__For_Period_,SumPercentage\r\nID 1,Person 1,50206317,9500308260,Student Payee,1202050000,7/31/2022 12:00:00 AM,7/1/2022 12:00:00 AM,1\r\nID 1,Person 1,50208984,9500312068,Student Payee,1202050000,8/31/2025 12:00:00 AM,8/1/2022 12:00:00 AM,1\r\nID 1,Person 1,50208984,9500312068,Student Payee,1202050000,8/31/2025 12:00:00 AM,9/1/2022 12:00:00 AM,1\r\nID 1,Person 1,50208984,9500312068,Student Payee,1202050000,8/31/2025 12:00:00 AM,10/1/2022 12:00:00 AM,1\r\nID 1,Person 1,50208984,9500312068,Student Payee,1202050000,8/31/2025 12:00:00 AM,11/1/2022 12:00:00 AM,1\r\nID 1,Person 1,50208984,9500312068,Student Payee,1202050000,8/31/2025 12:00:00 AM,12/1/2022 12:00:00 AM,1\r\nID 2,Person 2,50206213,9500308087,Temporary Employee,1202050000,3/31/2023 12:00:00 AM,7/1/2022 12:00:00 AM,1\r\nID 2,Person 2,50206213,9500308087,Temporary Employee,1202050000,3/31/2023 12:00:00 AM,8/1/2022 12:00:00 AM,0.25\r\nID 2,Person 2,50209957,9500313454,Temporary Employee,1202050000,6/30/2023 12:00:00 AM,8/1/2022 12:00:00 AM,0.75\r\nID 2,Person 2,50209957,9500313454,Temporary Employee,1202050000,6/30/2023 12:00:00 AM,9/1/2022 12:00:00 AM,1\r\nID 2,Person 2,50209957,9500313454,Temporary Employee,1202050000,6/30/2023 12:00:00 AM,10/1/2022 12:00:00 AM,1\r\nID 3,Person 3,50208850,9500311846,AcadRsrch-EE,1202050000,3/31/2023 12:00:00 AM,7/1/2022 12:00:00 AM,1\r\nID 3,Person 3,50208850,9500311846,AcadRsrch-EE,1202050000,3/31/2023 12:00:00 AM,8/1/2022 12:00:00 AM,1\r\nID 3,Person 3,50208850,9500311846,AcadRsrch-EE,1202050000,3/31/2023 12:00:00 AM,9/1/2022 12:00:00 AM,1\r\nID 3,Person 3,50208850,9500311846,AcadRsrch-EE,1202050000,3/31/2023 12:00:00 AM,10/1/2022 12:00:00 AM,1\r\nID 3,Person 3,50208850,9500311846,AcadRsrch-EE,1202050000,3/31/2023 12:00:00 AM,11/1/2022 12:00:00 AM,1\r\nID 3,Person 3,50208850,9500311846,AcadRsrch-EE,1202050000,3/31/2023 12:00:00 AM,12/1/2022 12:00:00 AM,1\r\nU04131362,MARIA KAMENETSKA,50209550,9500312861,FY-Accrual 9/12,1202050000,1/31/2027 12:00:00 AM,7/1/2022 12:00:00 AM,0.383354456902808\r\nU04131362,MARIA KAMENETSKA,GRANTNR,FUNDED_PROGRAM_NR,FY-Accrual 9/12,,,7/1/2022 12:00:00 AM,0.616645543097192\r\nU04131362,MARIA KAMENETSKA,GRANTNR,FUNDED_PROGRAM_NR,FY-Accrual 9/12,,,8/1/2022 12:00:00 AM,1\r\n"
  csv = csv.replace(/\r/g, "");
  // Split each line into a row.
  let rows = csv.split("\n");
  //	let rows = sheet.getRange("A1").getExtendedRange(ExcelScript.KeyboardDirection.down);
  /*
   * For each row, match the comma-separated sections.
   * For more information on how to use regular expressions to parse CSV files,
   * see this Stack Overflow post: https://stackoverflow.com/a/48806378/9227753
   */
  const csvMatchRegex = /(?:,|\n|^)("(?:(?:"")
  rows.forEach((value, index) => {
    if (value.length > 0) {
      let row = value.match(csvMatchRegex);

      // Check for blanks at the start of the row.
      if (row[0].charAt(0) === ',') {
        row.unshift("");
      }

      // Remove the preceding comma.
      row.forEach((cell, index) => {
        row[index] = cell.indexOf(",") === 0 ? cell.substr(1) : cell;
      });

      // Create a 2D array with one row.
      let data: string[][] = [];
      data.push(row);

      // Put the data in the worksheet.
      let range = sheet.getRangeByIndexes(index, 0, 1, data[0].length);
      range.setValues(data);
    }
  });

  //ConcatofVariables to unique:

  sheet.getRange("J1:J2").setFormulasLocal([["ConcatofVariables"], ["=A2&B2&C2&D2&E2&F2&G2"]]);
  sheet.getRange("J2").autoFill();
  sheet.getRange("H1").getExtendedRange(ExcelScript.KeyboardDirection.down).replaceAll("T*", "", { completeMatch: false, matchCase: false });

  //Pivot Table using sort unique functions

  // Set range U1:V2 on sheet
  sheet.getRange("N1:O2").setFormulasLocal([[null, "= TRANSPOSE(SORT(UNIQUE(H2:H99999)))"], ["=sort(unique(J2:J99999))", "=sumifs(I2:I99999,J2:J99999,N2#,H2:H99999,O1#)"]]);


  //Create Table Data:
  let newgrantsheet = workbook.addWorksheet("GrantProjections");
  newgrantsheet.getRange("A1").copyFrom(sheet.getRange("A1:G99999"), ExcelScript.RangeCopyType.all, false, false);
  // Remove duplicates from range L1:S375 on sheet
  newgrantsheet.getRange("A:G").removeDuplicates([0, 1, 2, 3, 4, 5], true);
  // Paste to range U1 on sheet from range AT2:AY116 on sheet
  newgrantsheet.getRange("H1").copyFrom(sheet.getRange("O1:AF99999"), ExcelScript.RangeCopyType.values, false, false);

  //Create finalized table:
  sheet.delete();
  newgrantsheet.getRange("H:Y").setNumberFormatLocal("0.00");
  let sheet2 = workbook.addWorksheet("TempTable");
  // Set range A1:B2 on sheet2
  sheet2.getRange("A1:B2").setFormulasLocal([["-6", "=A1+1"], ["=CONCAT("Actual ",MONTH(EDATE(TODAY(),A1)),"/",YEAR(EDATE(TODAY(),A1)))", null]]);
  // Auto fill range
  sheet2.getRange("B1").autoFill("B1:F1", ExcelScript.AutoFillType.fillDefault);
  // Auto fill range
  sheet2.getRange("A2").autoFill("A2:F2", ExcelScript.AutoFillType.fillDefault);
  // Paste to range H1 on sheet from range A2:F2 on sheet2
  newgrantsheet.getRange("H1").copyFrom(sheet2.getRange("A2:F2"), ExcelScript.RangeCopyType.values, false, false);

  // Set range C1:D2 on sheet2
  sheet2.getRange("A3:B4").setFormulasLocal([["0", "=A3+1"], ["=CONCAT("FORECASTS ",MONTH(EDATE(TODAY(),A3)),"/",YEAR(EDATE(TODAY(),A3)))", null]]);
  // Auto fill range
  sheet2.getRange("B3").autoFill("B3:L3", ExcelScript.AutoFillType.fillDefault);
  // Auto fill range
  sheet2.getRange("A4").autoFill("A4:L4", ExcelScript.AutoFillType.fillDefault);
  // Paste to range H1 on sheet from range A2:F2 on sheet2
  newgrantsheet.getRange("N1").copyFrom(sheet2.getRange("A4:L4"), ExcelScript.RangeCopyType.values, false, false);
  newgrantsheet.getFreezePanes().freezeAt(newgrantsheet.getRange("A1:G1"));

  newgrantsheet.getRange("N1:Y1").copyFrom(newgrantsheet.getRange("M1"), ExcelScript.RangeCopyType.formats, false, false);
  newgrantsheet.getRange("A1:Z1").getFormat().autofitColumns();
  let arows = newgrantsheet.getUsedRange().getRowCount()
  let somerange = "" + arows + ":" + arows;
  newgrantsheet.getRange(somerange).delete(ExcelScript.DeleteShiftDirection.up)
  //clear header creation from sheet2 to allow subtotaling
  sheet2.getUsedRange().clear()
  //subtotaling new sheet and building out grant sheet
  let buidcolumn = newgrantsheet.getRange("A1").getOffsetRange(1, 0).getExtendedRange(ExcelScript.KeyboardDirection.down).getAddress()
  let buiduniqueformula = "=UNIQUE(" + buidcolumn + ")"
  // Set range A1 on sheet2
  sheet2.getRange("A1").setFormulaLocal(buiduniqueformula);
  // Paste to range A1 on sheet2 from extended range obtained by extending down from range A1 on sheet2
  sheet2.getUsedRange().copyFrom(sheet2.getUsedRange(), ExcelScript.RangeCopyType.values, false, false)
  //  sheet2.getRange("A1").find("0", { completeMatch: true, matchCase: false, searchDirection: ExcelScript.SearchDirection.forward }).setValue("");
  var uidrowsstrings = sheet2.getUsedRange().getValues().toString()
  if (sheet2.getUsedRange().getCellCount() > 1) {
    var uidrows = uidrowsstrings.split(",")
  } else {
    var uidrows = uidrowsstrings.split(" ")
  }
  for (const value of uidrows) {
    let duration = new Date().getTime() - start
    let elapsed = duration / 1000
    if (elapsed > 70) {
      var lastuid = value
      break
    } else {
      var lastuid = ""
    }
    let foundUID = newgrantsheet.findAll(value, { completeMatch: true })
    let rowbelow = foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(1, 0)
    rowbelow.getEntireRow().insert(ExcelScript.InsertShiftDirection.down)
    let workedrow = foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(1, 0)
    workedrow.getCell(0, 0).setValue(value)
    workedrow.getCell(0, 1).setValue("Total")
    let sumformula = "=sum(" + foundUID.getAreas()[0].getOffsetRange(0, 7).getAddress() + ":" + foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(0, 7).getAddress()
    let topprojectedfirst = foundUID.getAreas()[0].getOffsetRange(0, 13).getAddress()
    let bottomprojectedfirst = foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(0, 13).getAddress()
    let bottomprojectedlast = foundUID.getAreas()[foundUID.getCellCount() - 1].getOffsetRange(0, 24).getAddress()
    let firstprojectedrange = topprojectedfirst.replace(topprojectedfirst.replace(/\D/g, ""), "") + "$" + topprojectedfirst.replace(/\D/g, "") + ":" + bottomprojectedfirst.replace(bottomprojectedfirst.replace(/\D/g, ""), "") + "$" + bottomprojectedfirst.replace(/\D/g, "")
    let allprojected = topprojectedfirst + ":" + bottomprojectedlast
    let datavalidationformula = "=sum(" + firstprojectedrange + ")<=1"
    let cellarray = Array.from(Array(foundUID.getCellCount()).keys())
    newgrantsheet.getRange(allprojected).getDataValidation().setRule({ custom: { formula: datavalidationformula } })
    newgrantsheet.getRange(allprojected).getDataValidation().setPrompt({ showPrompt: true, message: "Enter projected percentage as decimal number", title: "Enter Projected Percentage" })
    newgrantsheet.getRange(allprojected).getDataValidation().setErrorAlert({ showAlert: true, style: ExcelScript.DataValidationAlertStyle.stop, message: "Projections can't go over 100%, please correct projections.", title: "Projections not over 100%" })
    newgrantsheet.getRange(allprojected).getDataValidation().setIgnoreBlanks(true);
    // Unlock the fields necessary then protect the whole sheet
    newgrantsheet.getRange(allprojected).getFormat().getProtection().setLocked(false)
    let sumsstart = workedrow.getOffsetRange(0, 7).getCell(0, 0)
    sumsstart.setValue(sumformula)
    let sumsend = workedrow.getOffsetRange(0, 24)
    sumsend.getExtendedRange(ExcelScript.KeyboardDirection.left).copyFrom(sumsstart)
  }
  // Delete worksheet sheet2  
  sheet2.delete()
  let protectionOptions: ExcelScript.WorksheetProtectionOptions = {
    allowAutoFilter: true,
    allowSort: true
  }
  newgrantsheet.getAutoFilter().apply(newgrantsheet.getRange("A1"))
  newgrantsheet.getProtection().protect(protectionOptions);
  let duration = new Date().getTime() - start
  let elapsed = duration / 1000
  let emailsheet = workbook.getWorksheet("Send Sheet in for Email")
  let extrasheet = workbook.addWorksheet("Sheet1")
  extrasheet.setPosition(0)
  emailsheet.setPosition(2)
  newgrantsheet.setPosition(1)
  newgrantsheet.activate()
  console.log(lastuid)
  return lastuid
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,263 questions
0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.