Low Cost RPA with Google APIs and Python -Post Table Data to Slides: Use Case Overview-

Overview

In this article, I will explain an example of what can be automated by actually combining Google API and Python, using the use case of processing the data extracted from Spread Sheets with Python and posting it to Slides as an example.

• Spread Sheets is Google version Excel, Slides is Google version Power Point

Please refer to the following article for why automation is done using Google API and Python. Low cost RPA with Google API and Python -Introduction-

The specific code will be described in the following article.

Use Case

(Overview)

1. You are the PMO of a PJ.
2. PJ consists of 3 teams (requirements definition / proto app verification / infrastructure review), and the leader of each team updates the following items of each task on a daily basis in the Gantt chart (Fig. 1). --Person in charge --Deadline

• Progress rate --Comment

3. Every Friday, as a PMO, you read the Gantt chart, post the following items for each team in the weekly progress report (Fig. 2) and share them at the PJ plenary session. --Team name --Person in charge (all persons in charge for each team) --Average value of progress rate (%) of each task of each team --Risks listed for each team (if filled)
4. You used to do all this work manually, but as the amount of tasks increased, it became necessary to work on RPA for the purpose of reducing man-hours and mistakes during posting.

(Fig. 1: Gantt chart)

(Fig. 2: Weekly progress report material)

Detailed description of the posting process in this use case

The logic of each process to post each of the following items is as follows. The numbers (① to ④) in each item are numbered in the figure below.

--Team name --Check the gray team name (Spread Sheets ①) and post it to Slides ① --Person in charge (task owner for each team) --Check the person's name (Spreas Sheets②) entered for each team, and post it so that there is no duplication in Slides②. --Average value of progress rate (%) of each task of each team --Post the value obtained by dividing the total progress rate (Spread Sheets ③) of each team by the number of items to Slides ③. --Risks listed for each team --Check where the comment is entered (Spread Sheets ④) and post it to Slides ④

(Fig. 3: Image of posting)

Approach to automating the process

These use cases use the Google API and Python to break down the following process to automate posting. Basically, the Google API is used as an interface for reading and writing data, and all the steps for reading and writing and data conversion are performed in Python.

1. Set up Python to call the Sheets API and Slides API
2. Call the Sheets API in Python and extract all the table data into python from the Gantt chart in Figure 1 (double list)
3. Group the table data acquired in step 2 of the previous section by team.
4. Convert data by creating / using automatically calculated logic
5. Call the Slides API in Python and post the converted table data to the blanks in the "Progress Summary List" of the weekly progress material in Figure 2.

Finally

This time, we defined a weekly progress report as a use case, which can occur as a routine work even in general PJ. The specific logic of the automated process will be introduced in a subsequent article.