In this article, we will show you how to build a system that automatically updates cell values in Google Spreadsheets.
No-Code Development Platform Questetra BPM Suite
The no-code development platform Questetra BPM Suite is used to automate operations.
A system for business execution in an organization can be constructed by drawing a workflow diagram in Questetra BPM Suite. This system not only automates the handing over of work to people. It also automates text conversion, numerical calculation, and email transmission. Furthermore, data integration to other cloud services can also be automated.
Such automation is achieved by placing an item that will automatically process something (e.g. uploads a quote file to Google Drive) at the timing you want to automate in the workflow diagram (e.g. after the sales manager approves the quote).
Various automating items available in Questetra BPM Suite will allow you to automate your operations easily.
Automatically Update Values in Google Sheet Cells
Google Sheets, a cloud-based spreadsheet software, is one of the features of Google Workspace provided by Google.
Among the automating items provided in the Questetra BPM Suite introduced in the previous section, there are many Google Sheets relevant items. (as of 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 and beyond will use [Google Spreadsheets: Row Update] (bold in the above list).
Cell Values Automatically Update After Reporting and Approval
As of February 1, 2022, the number of COVID 19 cases is reported daily in the news. The subject of this article is a super-simplified version of the COVID 19 cases counts reporting process that might exist in an agency that reports such cases. We will build a system that will update a Google spreadsheet cell with the latest information on the number of COVID 19 cases reported.
The mechanism we will build involves the following processes.
- The reporter reports the number of COVID 19 cases.
- The approver approves the number of COVID 19 cases.
- A cell in the Google Sheet is updated with the date of the report.
- The cells in the Google Sheet are updated with the number of COVID 19 cases.
Such a workflow diagram is created in Questetra BPM Suite (see the diagram below).
The blue rectangles are handled by people (reporters/approvers) and the gray rectangles are automating items. The automating item is referred to as [Google Spreadsheet: Update Row] and the two of them in the workflow diagram perform the following respectively.
- [Date of Report]
- The Report Date entered at the time of reporting is entered in the cell (B3) of the spreadsheet
- [Number of People]
- The Number of People entered at the time of the report will be entered in cell (B2) of the spreadsheet
Each automating item is set up as shown in the following figure.
The spreadsheet ID is the blue portion of the target spreadsheet’s URL, shown below.
In fact, when the report and approval are made the spreadsheet updates the values as shown in the next figure.
We showed you how to build a system in which cells in a Google Sheet are automatically updated.
Using the automating items provided in the Questetra BPM Suite, a cloud-based no-code platform, you can build a system to automatically update cell values in Google Sheets without any knowledge or experience with Google Sheets’ 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.
Many other automations can be implemented using Questetra BPM Quite, but that’s all for this issue!