Edit

Report generation and dissemination workflow

Script overview

Environment

• Windows OS that meets System Requirements
• Any of these browsers: Google Chrome, FireFox, or Internet Explorer
• MS Excel (version doesn't matter)
• MS Outlook (version doesn't matter) with the email account configured and logged in for a bot to be able to send emails

Data and logic

Input data:

• List of email addresses (ten emails) stored in the mail_list variable
• Metrics as the five .json files (see the downloaded sample)

Output data:

• Excel file with log data

Common logic

1. Open log files from a specific folder.
2. Scrape details from the logs.
3. Create a new Excel file and save the information.
4. Send the file via Outlook to the list of specified persons.

The Excel file will have the following information from the .json files.

Host	Timestamp	Uptime	TaskProcessingTime
127.0.0.1	2019-02-07T12:46:07.312Z	267328	46285
127.0.0.1	2019-02-06T12:44:28.466Z	133786	36970
127.0.0.1	2019-02-05T12:43:31.466Z	265583	0
127.0.0.1	2019-02-04T12:52:11.256Z	530193	137936
127.0.0.1	2019-02-01T12:43:42.456Z	369786	39631

Script variables

Variable name	Type	Description	Default value
col1	String	column name in Excel file	Host
col2	String	column name in Excel file	Timestamp
col3	String	column name in Excel file	Uptime
col4	String	column name in Excel file	TaskProcessingTime
content	String	stores data from input file
content_list	List	stores data from input files in list format
empty_table	Table	replaces the content of the Excel file
excel_file_name	String	Excel file name	/report
excel_table	Table	replaces the content of the Excel file
file_names	List	stores data from input files
files_numbers	Number	stores the quantity of input file + 11
format_file	String	Excel file type	.xlsx
full_path	String	stores the path to Excel file
mail_list	List	list of mail addresses	tuk.tuk.rpa@gmail.com, .... . (any number you need)
path_to_folder	String	path to folder with input files	C:\Samples
subject	String	mail subject	Log Report
url	String	website url for conversion from .json to .csv	https://toolslick.com/conversion/data/json-to-csv

Script workflow

Copy files content into clipboard (reusable)

Description: The group of actions is required to copy the files content into clipboard. The whole group is reusable in other projects.

Actions 2-8 reads the defined folder content including files, subfolders, or both, and saves the results (full paths to the items) to a Recorder variable (List).

tip

To reuse the script, change the value of thepath_to_folder variable.

Open website (reusable)

Description: The group of actions is required to open a website, add data from clipboard to special area in the website, make conversion from .json to .csv, download a file from the website, and open the downloaded file.

1. Action 10 adds the exception handling, so that the script can be opened in a default browser installed on your machine (Google Chrome, Mozilla Firefox, or Internet Explorer).

2. Action 12 opens the website from the urlvariable.

3. Action 14 specifies the area on the website for pasting data using XPath.

4. Action 22 specifies the button on the website for downloading the result .csv file using XPath.

5. Actions 13, 15, 38, 42, 52, 55 are using the special Application group action such as Maximize Window and Window for different browsers to work correctly.

6. Action 27 allows to wait for the .csv file to open.

tip

To reuse this group, change:

• value of the urlvariable–the website url
• XPath values in actions 14 and 22
• (if your defalt browser is Internet Explorer) – Window title in action 52 (open the website on a particular page and select the window title from the list)

Save opened .csv file as Excel file

Description: The group of actions is required to save the already downloaded and opened .csv file in the .xlsx format.

1. Action 60 uses the standard key combination F12 to open the Save As Excel window. Actions 62-63 type the file full name. Action 67 provides the first type file option, which is .xlsx. (Enter Keystrokes)

2. Actions 71-73 (Exception Handling) check the existence of an .xslx file in the destination folder. If the file exists it will be overwritten by Excel.

Adjust Excel file (reusable)

Description: The group of actions is required to make adjustments to the Excel file.

The downloaded file looks as follows.

In the end, the file should look like this.

1. Action 81 (Join Strings) creates a full path to the Excel file by joining three variables of the String type.

2. Action 82 (Open Spreadsheet) opens the Excel spreadsheet using the full_path variable.

3. Action 83 (Set Range) copies data from f2 to g6 and action 84 pastes it to the range of a2:b6. Action 85 (Set Cell Value) copies data from j2 to k6 and action 86 pastes it to the range of c2:d6. Action 87 pastes empty cells from e1 to n6. Actions 88-91 set the column names (Get Range).

tip

To reuse this group, perform the following steps.

• Provide the correct path to the Excel file in action 82.
• Specify the needed range in actions 83-87.
• Set the right values for column names in the col1-col4 variables.

Send email with attachment via Outlook (reusable)

Description: The group of actions is required to open Outlook and send an email with the Excel attachment to unlimited number of persons. The whole group is reusable in other projects.

1. Actions 92-101 open Outlook. The bot opens the Run window, types 'Outlook' (Keyboard), and then opens Outlook. With the help of the Window action, the bot switches to the specified window and ignores any random popups.

2. Action 102 (For Each) specifies that the bot will send as many letters as many elements (email addresses) are there in the mail_listvariable.

3. The actions send an email with the specific subject and the attached file to the email from the mail_list variable. To do that, you will need the Enter Keystrokes, Click Mouse (via Inspector), and Window actions.

4. The exception handlings are added as some window titles and elements differ in various versions of Outlook. The script below works for Outlook from 2007 to 365.

   1. Action 104 adds the exception handling due to the window title difference in Outlook versions.
   2. Action 111 adds the exception handling due to the difference in the Subject selector in Outlook versions.
   3. Action 115 adds the exception handling as the Attach file selector differs in Outlook versions.
   4. Action 120 adds the exception handling as the flow for attaching a file differs depending on the Outlook version.
   5. Action 126 adds the exception handling as the Send button selector differs depending on the Outlook version.

tip

To reuse this group, change the variables values.

• mail_list*– *change the users email to send to. Add any quantity you need in the list variable.
• subject – choose the subject to set into the variable value.
• full_path – set the right path for the file you want to attach, for example, C:\Samples\report.xlsx and unmark action 123. The alternative way is to provide this path in three variables:
  • path_to_folder (for example, C:\Samples);
  • excel_file_name (for example, \report);
  • format_file (for example, .xlsx).

Close Outlook (reusable)

Description: The group of actions is required to close Outlook when the email is sent. The whole group is reusable without any changes in other projects.

1. Action 132 (Wait) provides time for the email to be sent.
2. Action 133 (Exception Handling) adds the exception handling due to the window title difference in various Outlook versions.
3. Action 135 (Enter Keystrokes) enters Alt+F4 to close Outlook.