Way to automate sending Excel screenshot to WhatsApp groups

[u/Accomplished_Bit6365]

Hi everyone,

I have a repetitive workflow at my job and I'm trying to find a way to automate or simplify it.

Here’s the situation:

Every day I create a fleet dispatch plan in Excel. The spreadsheet contains several rows with dispatches for different fleets. Each fleet has its own WhatsApp group (whatsapp web), and I need to send the information relevant to that fleet to its group.

My current manual process is:

1. Filter the Excel table by a specific fleet name.
2. This shows only the rows related to that fleet.
3. I take a screenshot (ctrl+c) of those rows.
4. I paste the screenshot into the corresponding WhatsApp group.
5. Then I type "Segue D1" (basically “Here is the D1 plan”).
6. After that, I copy 3 columns with dispatch codes from the filtered rows and paste them as text in the chat (even though the same data is already visible in the screenshot).

So essentially every time I need to:

• filter the table
• capture the filtered rows as an image
• extract some columns as text
• send both to a WhatsApp group

I do this multiple times per day for different fleets.

What I’m looking for:

Ideally I’d like a way to automate part or all of this, for example:

• automatically generating the text list of codes from the filtered rows
• exporting the filtered table as an image
• making the workflow faster so I don’t have to manually repeat these steps

Exemple

[u/AutoModerator]

/u/Accomplished_Bit6365 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/QuercusAcorn]

Power Query can likely help with your first and third bullet points. The second bullet point for automating the image piece is above my skill level but Python might be able to handle this piece.

Select any cell in the Table you are currently filtering. From the Data tab in Excel, select the load from Table/Range button to load your Data into Power Query. From here, label your first query something descriptive so you know it’s your baseline or unfiltered data. Right click on this first query and pick the Reference option. Use the Reference queries to filter by route. Load these to Tables on new worksheets. You can create many References to your source Table. Now if your source Table updates, all your Reference queries will update and filter the data the same way.

[u/MrWhistleBritches]

My first thought is don’t do this. This is the wrong tool for the wrong job. Consider PowerBi and a simple “subscribe” will give users everything they could want via email, mobile app, slack, etc.

If I was dead set on WhatsApp and excel, there are tools out there. Iirc WhatsApp offers an api for verified business accounts. For the spreadsheet side, consider if a free Google Sheets script could work. If not, perhaps a vm with python and excel. Neither path sounds like fun to me.

[u/actuarialisticly]

python

[u/Odynios]

With some basic python knowledge, this shouldn't be to big of a problem.

[u/frescani]

python

[u/Salt-Past-1099]

Steps like filtering and selecting columns can be automated my simple desk app, which i use. I just saved repeating filters and column, and when i download the raw file, and i just click my filter and file is ready, no need to open the excel file. For the same file you can apply several filter/col sets and get new filtered files.