Answered

OfficeScript to compare data in two Workbooks (must PowerAutomate be used?)

(0) Share

Report

Posted on by AlexBrandt

Hello Power Automate pros,

I was on MS Office community with my question and they sent me here as apparently Office Script questions are answered here. I hope I can get some info.

I am starting to dabble with Office Scripts inside MS Excel to automate certain simple data processes. Have been using VBA before that...

I need to create a Script which will essentially compare a list of company names in Workbook A with another list reference list in Workbook B. When there are matches, it will return an answer in a new column in Workbook A.
I'm reading the the Office Script documentation with a Use-case example and it is talking about using Power Automate in order for the data to be compared in two separate workbooks.

I'm wondering Power Automate is a must here to achieve the above? Wouldn't that script be able to run if the Workbook B was stored in the same directory than the Office Script itself?

Thanks

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (1)

v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @AlexBrandt ,

I need to create a Script which will essentially compare a list of company names in Workbook A with another list reference list in Workbook B. When there are matches, it will return an answer in a new column in Workbook A.

I think there is no need to use Office Script to do that . Just try excel function 'vlookup':

How to use VLOOKUP in Excel

Best Regards,
Bof

Was this reply helpful? Yes No
AlexBrandt 19 on at

Like (0)

Report
Copy link

Link copied!

Hello @v-bofeng-msft

Thanks for the suggestions.
I'm well aware of the VLOOKUP function. But in this case its different.
My script will compare 3 data points (Company names, website and Linkedin url) and if one matches will return some information. Not only that but the script pulls de domains names only out of the URLs before doing the matching...

We are working with lists with thousands of lines etc. Its not so workflow friendly to have to pull formulas and adapt them everytime.

Was this reply helpful? Yes No
GeoffRen Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Unfortunately cross workbook access from a single script is not currently supported. This feature is in our backlog, however, we do not have a timeline to share. Using Power Automate for this is the recommended workaround in the meanwhile.

Was this reply helpful? Yes No
AlexBrandt 19 on at

Like (0)

Report
Copy link

Link copied!

Hello @GeoffRen
Thanks for the info.

I see that it is possible to to make an external API call with Office Script and fetch data. I'm pretty new to all this, but wondering if it would be possible to fetch data to the sheet stored in SharePoint or OneDrive?

Was this reply helpful? Yes No
Verified answer

GeoffRen Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

It may be possible with external API calls by calling the Graph API, for example Get Table. You'll have to figure out the auth flow, but you can probably test it by getting an auth token from the Graph Explorer and hardcoding that in your Graph request.

Click Access token and it should display your auth token used in Graph requests.

Was this reply helpful? Yes No
AlexBrandt 19 on at

Like (0)

Report
Copy link

Link copied!

Hello @GeoffRen

Wow ok. I had not idea about the existence of the Graph Explorer. I will need to read more about that.
Thank you for this.

Was this reply helpful? Yes No