Office Script - Copy across workbooks

Question

Hello!

I'm looking for a way to combine office script and power automate to do a manual copy and paste task.

The example is:

I have 3 products with 5 features. I ask 5 people to provide a review.

I called the products - Product 1, 2, 3. The features are Usability, Design, Price, Options, Weight. The reviewers are called R1, R2, R3 etc.

I have 2 x workbooks. One to collect feedback and one to consolidate all feedback

I wanted the first script to copy all the feedback data. Rather than copy a specified range it should be able to collect all data within the sheet. I was thinking to do this by making the feedback data a table.

I wanted the 2nd script for the consolidated sheet. I was thinking to do a sort of lookup feature. So I need to match.

• The features, The reviewer, The product. Then if those things matched, I would copy the scores and the comments.

The consolidated workbook has 3 worksheets for each product. And the feedback sheet just has one worksheet. I wanted all scores and comments from all evaluators to populate in the corresponding worksheet for the product.

I was thinking to use power automate to run the scripts and have it populate the consolidated sheet when a new feedback sheet is added.

The example layout is here on Google sheets https://docs.google.com/spreadsheets/d/1bzQYSOMzUDDiSZxU5-wKyvkQRWN0KU0VFbtmjG0_mns/edit?usp=sharing

I am wondering if I am asking too much, it's complicated and I haven't been able to figure it out.

Would really appreciate your help.

Thanks

Answer 1

I’d suggest you go to Microsoft Power Automate Community - Power Platform Community to post a new thread, which is the specific channel to handle Power Automate issue.

Thank you for your understanding.