Hi there!
We often find ourselves reading data from Google Sheets, creating documents for meetings and reporting on them via email. We would like to eliminate the process of reading data and transcribing it to other places if possible.
This article will show you how to automate such a posting process.
System In-house Production Tool Questetra BPM Suite
The automation of business operations addressed in this article will use Questetra BPM Suite, a cloud-based in-house system development tool.
In Questetra BPM Suite a business system is constructed through the drawing of workflow diagrams. In the workflow diagram tasks to be processed by humans and tasks to be processed automatically can be arranged. This makes it possible to construct a system in which, when a human process is completed (e.g. approval by the supervisor), an automated process will follow (e.g. automatic sending of an e-mail).
Items that are automatically processed in Questetra BPM Suite include those that can convert text, calculate numbers, and send emails, as well as those that can integrate data with cloud services such as Google Workspace, Box, Slack, kintone, etc. The following is a list of the items that can be processed automatically.
These automation items allow you to automate operations without knowledge or experience with APIs or programming.
Google Sheets Get Cell Values
Google Sheets are spreadsheets that can be used in a web browser and are included in Google Workspace, as are Google Drive and Google Calendar.
The automation items provided in the Questetra BPM Suite introduced in the previous section are related to Google Sheets. (v13.3.0)
- Google Sheets: Create File
- Google Sheets: Download Choice Data
- Google Sheets: Append New Row
- Google Sheets: Add New Sheet
- Google Sheet: Copy Sheet
- Google Sheet: Delete Sheet
- Google Sheets: Update Row
- Google Sheets: Get Row
The mechanism we will create in the next section uses Google Sheet: Get Rows.
How Spreadsheet Data is Retrieved and Reported via Email
In the article Recommendation for Automation – Google Sheets: Update Row we built a system in which approved totals and their reporting dates are automatically entered into the spreadsheet cells.
In this article we will build a system in which values entered in cells are read and reported by email. The following process will take place at 17:00 every day.
- The value of the cell in which the report date is entered is read.
- The value of the cell in which the number of cases is entered is read.
- An e-mail containing the Report Date and number of cases is sent.
Such a workflow diagram is created in Questetra BPM Suite (as in the figure below).
The round item on the leftmost side of the workflow diagram (17:00 daily) serves as a timer. At 17:00 every day, each item in the sequence after it is processed in turn.
The two gray squares in a row are the item [Google Spreadsheets: Get Row] which manipulates a Google Sheet.
- Get Reporting Date
- Obtains the reporting date entered in cell (B3) of the spreadsheet
- Get Number of Cases
- Obtains the number of COVID 19 cases entered in cell (B2) of the spreadsheet
The round item with the word “email” will send an email with the reporting date and number of cases inserted into the body of the email.
The spreadsheet ID is the next blue portion of the target spreadsheet URL.
https://docs.google.com/spreadsheets/d/spreadsheet-id-example-spreadsheet-id-example/edit#gid=1234567890At 5:00 p.m. each day, an email similar to the one shown in the following figure is sent.
Summary
We have introduced a mechanism for automatically retrieving values entered into cells in a Google Sheet.
By using the automation items provided in Questetra BPM Suite, an in-house cloud-based system development tool, you can build a system to retrieve values from Google Sheet cells without any knowledge or experience with Google Sheet’s API (Application Programming Interface).
Questetra BPM Suite is available on a 60-day free trial basis. If you are interested, please apply from the following link.
That’s all for now!
