Answered

Read excel attachment with office scripts

(0) Share

Report

Posted on by cvig

Hello,

I'd like to be able to read a specific cell (for example, cell J8) from an excel file that I am periodically receiving as attachment to an e-mail (coming from outside my organisation, prepared by others with a pre-defined template). This is because the value of cell J8 changes depending on the excel I receive via e-mail.

The excel file I'm receiving has no "format as table" style and cannot be modified on the fly (because then it's used by other functions in my company).

I'm wondering if this can be done via Office Scripts somehow... can anybody suggest if it's feasible?

Thank you very much.

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Yutao Huang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

The "Run script" action only works with Excel workbooks stored on SharePoint or OneDrive for Business. So your flow will need to first save that attachment to a place on SharePoint or OneDrive for Business. But after that, you should be able to write up some scripts to read that cell value and return to the next action in your flow.

These articles might be helpful:
* Save Office 365 email attachments to specified OneDrive for Business folder | Microsoft Power Automate
* Return data from a script to an automatically-run Power Automate flow - Office Scripts | Microsoft Docs

Was this reply helpful? Yes No
cvig 36 on at

Like (0)

Report
Copy link

Link copied!

Hi, thank you for the reply Yutao. However, I think that it would not work, as I cannot find the way to open an excel file from an office script written into another excel file.
I mean: I may prepare "file1" with the office script and put it on SharePoint or OneDrive.
Then I receive "file2" via e-mail; this file, which is the one I'd like to read automatically, does not contain any script and cannot be modified. I save "file2" into the same path of "file1". Up to this point everything is fine.
Then, I should be able to read some content of "file2" (for example, one specific cell) having programmed the office script of "file1": is it possible? That's what I'm searching for.

On the other hand, from what I see on the internet it seems that an office script can "act" only on the file where it is written. It's just self-referring to itself, but cannot be used to get data from another excel file. In other words, I need to carry out a "cross-workbook" operation.

Thanks.

Was this reply helpful? Yes No
Verified answer

Yutao Huang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @cvig -,

Office Scripts is not "tied" to any particular workbook. It's stored separately on user's OneDrive for Business and it can run against any workbook the user has access to (e.g., either created by the user or other people have shared with the user).

So with the "Run script" action, you can pick any existing workbook from your OneDrive for Business (or SharePoint library). You can also specify a dynamic file ID, which can be passed from a preceding action so you will be able to run your script against a workbook created on the fly (for example, saved from an email attachment).

Was this reply helpful? Yes No
cvig 36 on at

Like (0)

Report
Copy link

Link copied!

Hi @Yutao ,
What if the "workbook" created on the fly has no office script in it?

I fully understand the Power Automate side of the thing I'd like to do, but I think I'm in the case you wrote about here:
https://powerusers.microsoft.com/t5/Using-Flows/Trying-to-use-Office-Scripts-and-Flow-to-copy-from-one-workbook/td-p/687633

I need to do a cross-workbook operation, because the file I receive via email and store (with Power Automate) on SharePoint has no office script in it.
Or maybe I don't get fully the meaning of an "office script not tied to any workbook": are you saying that, in your example, you can pick the script "Build table" and select any workbook / file you like? Does it mean that once I create an office script (with excel) on SharePoint or OneDrive, then it's available in SharePoint environment no matter what the workbook I want to run the script on is?

What you think about it?

Thank you very much for your patience.

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

@cvig - Yes, that's exactly right. Once you create an Office Script from any workbook, it will be available in the "Run script" action to run against any other workbook that you have access to.

Was this reply helpful? Yes No
cvig 36 on at

Like (1)

Report
Copy link

Link copied!

Hi @Yutao ,
Thank you very much! Now that's clear. This is a "basis" that, I have to admit, I missed and I hadn't found in the documentation I've read to date, but I've come across just few articles on Office Scripts into Power Automate. 🙂

I'll try it today!
Thanks

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Yes! Please give it a try and let us know what you think!

Was this reply helpful? Yes No
cvig 36 on at

Like (1)

Report
Copy link

Link copied!

Hi @Yutao ,
I tried it and that was exactly what I was looking for (but didn't understand from the beginning the principle of scripts not "tied" to a workbook, but "living" in the cloud environment available to any workbook once they've been written for the first time in one workbook; maybe I was still thinking in "VBA-fashion"...).

Thank you again.
Take care.

Was this reply helpful? Yes No