How to perform VLOOKUP using Office Scripts across multiple workbooks in Excel?

Question

I have two workbooks in Excel: "Job_Salary" and "Deduction_Sheet". In the "Job Salary" workbook, I have a list of employees with their salaries. In the "Deduction_Sheet" workbook, I want to insert each employee's salary from the "Job_Salary" workbook using Office Scripts.

Could someone provide guidance or a code example on how to achieve this using Office Scripts? I'd appreciate any help or insights. Thank you!

Answer 1

Note: Office Scipts is different with VBA. It can NOT handle multiple workbooks within a script. You may need PA if you can't consolidate two worksheets in a workbook.

PA1_GetSalary (Office Script)

function main(workbook: ExcelScript.Workbook): string {
    // Get work sheet
    let salarySht = workbook.getWorksheet("Job Salary");
    // Get table
    let salaryTab = salarySht.getTables()[0].getRangeBetweenHeaderAndTotal();
    // Load data
    let salaryValue = salaryTab.getTexts();
    let salaryJSON = JSON.stringify(salaryValue);
    return salaryJSON;
}

PA2_SetSalary (Office Script)

function main(workbook: ExcelScript.Workbook, strJSON: string) {
    // Get work sheet
    let deductionSht = workbook.getWorksheet("deduction");
    // Get table
    let deductionTab = deductionSht.getTables()[0].getRangeBetweenHeaderAndTotal();
    let deductionValue = deductionTab.getTexts();
    // Store the empployee - salary list with Map object
    let salaryValue: string[][] = JSON.parse(strJSON);
    let myMap = new Map<string, string>();
    for (let i = 0; i < salaryValue.length; i++) {
        myMap.set(salaryValue[i][0], salaryValue[i][3]);
    }
    for (let i = 0; i < deductionValue.length; i++) {
        if (myMap.has(deductionValue[i][0])) {
            deductionValue[i][4] = myMap.get(deductionValue[i][0])
        }
    }
    // Update table
    deductionTab.setValues(deductionValue);
}

Power Automate flow

---

Below code only works for two worksheets in a workbook.

Option 1: Load data into array and loop up the value

function main(workbook: ExcelScript.Workbook) {
    // Get work sheet
    let salarySht = workbook.getWorksheet("Job Salary");
    let deductionSht = workbook.getWorksheet("deduction");
    // Get table
    let salaryTab = salarySht.getTables()[0].getRangeBetweenHeaderAndTotal();
    let deductionTab = deductionSht.getTables()[0].getRangeBetweenHeaderAndTotal();
    // Load data
    let salaryValue = salaryTab.getValues();
    let deductionValue = deductionTab.getValues();
    // Get the salary value
    for (let i = 0; i < deductionValue.length; i++) {
        for (let j = 0; j < salaryValue.length; j++) {
            if (deductionValue[i][0] === salaryValue[j][0]){
                deductionValue[i][4] = salaryValue[i][3]
                break;
            }
        }
    }
    // Update table
    deductionTab.setValues(deductionValue);
}

When dealing with a large dataset, utilizing a Map object can significantly improve lookup efficiency.

function main(workbook: ExcelScript.Workbook) {
  // Get work sheet
  let salarySht = workbook.getWorksheet("Job Salary");
  let deductionSht = workbook.getWorksheet("deduction");
  // Get table
  let salaryTab = salarySht.getTables()[0].getRangeBetweenHeaderAndTotal();
  let deductionTab = deductionSht.getTables()[0].getRangeBetweenHeaderAndTotal();
  // Load data
  let salaryValue = salaryTab.getTexts();
  let deductionValue = deductionTab.getTexts();
  // Store the empployee - salary list with Map object
  let myMap = new Map<string, string>();
  for (let i = 0; i < salaryValue.length; i++) {
    myMap.set(salaryValue[i][0], salaryValue[i][3]);
  }
  for (let i = 0; i < deductionValue.length; i++) {
    if (myMap.has(deductionValue[i][0])) {
      deductionValue[i][4] = myMap.get(deductionValue[i][0])
    }
  }
  // Update table
  deductionTab.setValues(deductionValue);
}

---

Option 2: Retrieve the salary value using the XLOOKUP (or VLOOKUP) formula and then convert the formula into a static value. The formula dynamically captures the data range using table column references within the formula.

function main(workbook: ExcelScript.Workbook) {
    // Get work sheet
    let salarySht = workbook.getWorksheet("Job Salary");
    let deductionSht = workbook.getWorksheet("deduction");
    // Get table
    let salaryTab = salarySht.getTables()[0];
    // XLOOKUP formula
    let formulaLU = "=XLOOKUP([@Employee],Table1[Employee],Table1[Annual Salary])"
    let deductionTab = deductionSht.getTables()[0];
    // Update formula with (salary) table name 
    formulaLU = formulaLU.replace(/Table1/g, salaryTab.getName());
    let dataCol = deductionTab.getColumnByName("Annual Salary").getRangeBetweenHeaderAndTotal()
    // Set formula
    dataCol.setFormulaLocal(formulaLU);
    // formula to value
    dataCol.setValues(dataCol.getValues());
}