Call Microsoft Excel APIs from a custom function using Excel built-in functions?

Question

I successfully called my First Microsoft Excel API from a custom function thanks to this link Call Microsft Excel APIs from a custom function. However, I am trying to add the VLOOKUP built-in Excel function inside of my custom function. After attempting multiple things for the my code to work, I still am getting a #VALUE! in the cell. What is my code missing? I know this is brand new to production as of 5/11 so is this feature not available yet?

/**
 * @customfunction
 * @param firstName person's first name
 **/
export async function getRangeValue (firstName: string) {
    let lastName;
    let context = new Excel.RequestContext();
    const range = context.workbook.worksheets.getActiveWorksheet().getRange("A1:C5");
    range.load();
    await context.sync();
    lastName = context.workbook.functions.vlookup(firstName, range, 3, false);
    lastName.load('value');
    await context.sync();
    return lastName.value;
}

=CONTOSOSHARE.GETRANGEVALUE("Happy")

ruoyingl · Accepted Answer · 2020-05-18 10:15:16Z

1

As you have two calls in the function, please consider to use Exce.run. For example:

export async function getRangeValue (firstName: string) {
let lastName;
await Excel.run(async (context) => {
    const range = context.workbook.worksheets.getActiveWorksheet().getRange("A1:B5");
    range.load();
    await context.sync();
    lastName = context.workbook.functions.vlookup("Happy", range, 2, false);
    lastName.load("value");
    await context.sync();
    console.log(lastName.value);
  });
return lastName.value;
}

Please let me know if it works.

answered May 18, 2020 at 10:15

ruoyingl

1213 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Call Microsoft Excel APIs from a custom function using Excel built-in functions?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related