Hi there!
What is Google Sheets?
It is a spreadsheet software available in the cloud and is a function of Google Workspace provided by Google.
Questetra, the company I work for, has also utilized Google Spreadsheets since its inception (2008). Since remote work had been in place at Questetra long before the COVID crisis, spreadsheet data was shared online and work was carried out efficiently.
While there are Google Spreadsheets for individuals, this article covers Google Spreadsheets for companies.
I Want to Automate Posting to Spreadsheets
In any business, the most troublesome part of any operation is the transcription of data.
Even when using Google Spreadsheets, this kind of manual work, such as:
- Transcribing the contents of an incoming email by copying and pasting
- Transcribing data displayed on the screen of some system by copying and pasting
- Opening CSV data exported from some system in a spreadsheet
is not only tedious and time-consuming, but also a cause of business errors.
It is natural to want to automate the process of posting to Google spreadsheets to reduce labor and prevent errors.
Google Sheets API for Automation!
If you are performing automation in a business that involves Google Spreadsheets, you will be utilizing APIs.
An API is a kind of interface for passing data to and from other systems. By utilizing APIs to pass data to Google Spreadsheets and vice versa, you can link data with other systems. (For more information on APIs, please refer to The Perfect Match for Workflows!? What is API?)
The API for manipulating Google spreadsheets is called the Sheets API, but it is not easy to use. The reason is that there are the following hurdles to overcome.
- Detailed specifications are written in English.
- For cloud services provided by overseas companies, documentation is often only in English.
- You must understand the details of the API.
- What is the content format of the data to be passed to the API?
- You must program what you understand
- Convert business data into a form that can be passed to the API and data received from the API into a form that can be used in the business.
Because of these hurdles, a certain level of knowledge and experience is required to build an automation mechanism using APIs.
It is difficult to find out the details of the Sheets API and work on automation by yourself, but there is a way to achieve automation using the API without having to be aware of it.
Google Spreadsheet Automation with No Code
Automated Items for Manipulating Google Spreadsheets
Questetra’s no-code development platform, Questetra BPM Suite, provides items to access the Sheets API.
This item allows you to work on automation even if you do not have the knowledge or experience to use the API.
Automated items for manipulating Google Sheets provided in Questetra BPM Suite include the following:
- 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
This article shows how to solve data transcription issues by using “Google Sheets: Append New Row”.
Automatically Insert Business Data into Spreadsheets
Consider the following operations:
- The inquirer makes an inquiry.
- The contact person responds to the inquiry.
- Of the inquiry data, the inquiry date and inquiry category are recorded in a Google Sheet.
In the third step above, one of the contact person members manually transcribes the information into a Google Sheet. This time, we will create a mechanism to automate this part.
To achieve automation, we will utilize Questetra BPM Suite as mentioned above. （Click here for a 60-day free trial)
Questetra BPM Suite is a platform for developing systems while creating workflow diagrams. Create a simple inquiry response flow diagram like the following
In fact, when an inquiry is generated the following manual and automated process are possible:
- Automatically sending a primary response email
- The operator creating a response to the inquiry
- A manager reviewing the response
- Getting advice from a relevant department
- Automatically sending a response email
but for the sake of simplicity, we have made the flow very simple (Questetra BPM Suite can achieve all of this).
When an inquiry arises,
- The staff member at the inquiry desk will record a note of the response outcome.
- The data is added to a Google spreadsheet.
These are the two steps in the process.
The workflow diagram is drawn based on the BPMN workflow diagram notation rules. Items with blue squares indicate human processing, and items with gray squares indicate some kind of automatic processing.
For the gray item “Google Spreadsheet: Append New Row”, configure it as shown in the following figure and the “Received Date” and “Category” of the query will be appended to the spreadsheet.
The spreadsheet to which the data will be appended should be created in advance. In the URL of the created spreadsheet, enter the blue portion shown below into the “Target Spreadsheet ID” field in the figure above.
https://docs.google.com/spreadsheets/d/spreadsheet-id-example-spreadsheet-id-exampleSets data items to be handled in the inquiry response flow. Define the items to be entered in the inquiry form and the inquiry response process.
With some other settings, it is possible to have the following screen where the contact person who makes the inquiry enters the results of the inquiry response.
The following figure shows the result of a number of queries actually entered and linked to the spreadsheet.
The following effects are expected from the automation of the inquiry response flow:
- Reduce the workload of transcribers
- Eliminate errors such as forgotten or omitted entries
- Real-time tabulation of data
Summary
We have shown how data can be automatically posted to a Google Sheet.
Questetra BPM Suite, a cloud-based no-code development platform, provides items for appending data to Google Spreadsheets. By utilizing these items, you can easily build a system to automatically append data to Google Sheets without any knowledge or experience of the Google Sheets API (Application Programming Interface).
The Questetra BPM Suite introduced here is available on a 60-day free trial basis. If you are interested, please apply from the following link.
There are still a variety of other automations that can be achieved. We will introduce them in future articles.
That’s all for today!
