Recommendation for Automation – Get Google Sheet Cell Values

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).

Questetra is a No-Code BPM whose development is completed by setting up flow diagrams, business data, and underwriting rules — Questetra BPM Suite Overview

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).

COVID 19 cases email reporting workflow diagram

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=1234567890

At 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.