Create Text with GPT from an Excel Sheet

[CSilva]

Hello, I am attempting to use the Create Text with GPT to analyze and summarize some data from an Excel Sheet. I am having difficulty creating a prompt that uses data from an Excel Sheet in SharePoint. How can I create a prompt using an Excel Spreadsheet from PowerPoint? 

[rzaneti]

Hi @CSilva ,

Hos is your Excel data organized? Do you have it as a table record, or it is just a populated cell in the Spreadsheet? If you are not sure about it, feel free to share an image from your Excel file (make sure to remove any sensitive content).

If the data is allocated into a table, you can use the 'Get row' or 'List rows present into a table' actions. If it is not, the easiest way to return this data is to use an Office Script, which is similar to a 'VBA', but for Excel Online and with integration with Power Automate Cloud.

[CSilva]

It is in a table right now. My test table has the following headers: Name, Hours, Wage, and Total. My goal right now is to ask Chat GPT to fill in the Total column for each Name listed. 

[rzaneti]

Hi @CSilva ,

I don't know exactly how did you structure your prompt, so I'm using here an exemplificative template that just provide some context and asks for the calculation for each of the table records. Feel free to share some details or even your prompt template. 

In this answer, I'm providing only the steps to build the prompt, but not to write ChatGPT response to Excel. I'm assuming that you will ask ChatGPT to deliver the results in an specific format ("name1 - total1; name2 - total2; name3 - total3..."). If this is the case, please share the desired response format, as we will need it to build a text mining expression to isolate the names and totals. 

Useful links

Here are some useful links related to the features used below in case of you want to explore it:

Integrating Power Automate and Excel tables - http://digitalmill.net/2023/07/17/integrating-power-automate-and-excel-tables/ 

Excel connector reference (official docs) - https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/ 

Text mining in Power Automate - http://digitalmill.net/2023/08/12/how-to-extract-and-clean-texts-with-power-automate/  

Line breaks in Power Automate - https://tomriha.com/how-to-replace-new-line-n-in-a-power-automate-expression/   

Table overview

For this example, I created this table with the same structure that you described:

Step 1 - Initialize a string variable for Excel records:

As mentioned before, we will provide some context to Chat GPT connector, which will basically include "The Employee X worked Y hours and has a hourly rate of Z", where "XYZ" will be filled dynamically. This context will be stored in a variable with string data type (I'm calling it 'prompt_table'):

Step 2 - Access all table records:

Use a 'List rows present in a table' action and populate it with your Excel file and table details to return all of its records:

Step 3 - Map all table records:

Now you will access each table record and extract the name, the wage and the hours to build the dynamic part from your prompt. To achieve it, first add an 'Apply to each' loop, passing the property 'value' from the 'List rows present in a table' action as dynamic content:

Then include an 'Append to string variable' action (be careful here, as there is also another action to append values to array variables), set 'prompt_table' as variable and enter the template of the text that you want to populate with the dynamic content (in our case: "The Employee X worked Y hours and has a hourly rate of Z"):

Now you just need to replace the 'XYZ' for the dynamic contents of the properties to be included in the text. The dynamic contents will have the same name of your table columns:

Before move forward, we will need to include a line break at the end of the append action, so each of the context statements will be in different lines. In Power Automate, you need an expression to include a line break, which is decodeUriComponent('%0A'). Include it at the end of the text:

Step 4 - Integrate the context into your prompt:

Out of the 'Apply to each loop', initialize another string variable to store your prompt. Write your prompt template and in the 'context' part of your text, insert the dynamic content of 'prompt_table':

Output

The output will look like this:

Flow design

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 

[fumado]

Bulk prompt with CHAT-GPT + EXCEL FILE and receive the response in the excel

8m ago

Hi, i need to bulk prompt CHAT-GPT using a excel file, in one row it will be the prompt and in the other row i want to receive the response (output). I see some tutorial video in youtube a guy doing that but he doesn't explain the technical way to do it. Someone can help me?

I record a video better explain the situation: https://www.loom.com/share/Screen-Shot-15-September-2023-8bba77e4719a42838e06401dd0ab527e?sid=d1def0...

The flow the guy uses, is something like that:

But i don't know what information i need to fill in each part of the flow to the process work fine, someone can help me with this question? I really need to know how to do it this for a job.

I record a second video two explain better my pain in solve this case and showing the flow the guy in youtube made: https://www.loom.com/share/With-each-information-i-need-to-fill-e3b8ca1dbc40405686457f2e634afb50?sid...

This is the video from the guy in youtube: https://www.youtube.com/watch?v=Kcehq7SWb4g

i'm using this sheet to test: PROMPT-GPT

What information i need to fill in each place? i'm forgeting something? thats why is not working? look:

(*I'dont know each information i need to fill in: table ; filter query ; order by ; top count ; skip count ; select query ; datime format)

In this other table, i don't know how to fill too, i'm following the guy tutorial but in "Messages content" don't appear to me the option to put requeriments like in his video:

and in the last part of the flow i don't know what i need to put like in "compose" and in the other parts of excel:

(* Table ; key collum ; key value ; data time format: i dont know with each information i need to put too)

Can you help me? 😄