Hi there!
In the „Recommendation for Automation“ series we introduce tips on how to automate the tasks at hand to make them a little easier and achieve better results.
This time we will automate two processes with Google Sheets: file creation and sheet copying (I only wrote one because I could not fit them both in the title). By combining these two processes, we will build a system that automatically prepares an application form that corresponds with the contents of the request.
In-house System Production Tool Questetra BPM Suite
The automation of business operations addressed in this article will use Questetra BPM Suite, an in-house cloud-based system production tool.
In Questetra BPM Suite various business systems can be constructed by drawing workflow diagrams. The constructed system will not only automate the handing over of work items to workers according to the workflow diagram. It will also automate text conversion, numerical calculations, email transmission, and more. Furthermore, data integration with other cloud services such as Google Workspace, Box, Slack, kintone, etc. can also be automated.
These automations are achieved by placing items in the workflow diagram that will be automatically processed at the desired time. For example, automatically adding query data to kintone when an inquiry is received.
The various automation items available in Questetra BPM Suite will enable you to automate your business operations without programming skills or experience.
Google Sheets File Creation
Google Sheets is a spreadsheet software that can be used in a web browser and is included in Google Workspace provided by Google.
Many of the automation items available 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 Sheets: Copy Sheet
- Google Sheets: Delete Sheet
- Google Sheets: Update Row
- Google Sheets: Get Row
The mechanisms we will create in the next section use [Google Sheets: Create File] and [Google Sheets: Copy Sheet] (in bold).
A system that automatically guides you to an application form that matches your billing information
With more snow than usual in the winter of 2021-2022 and the start of the Winter Olympic Games in Beijing on February 2, I suspect that interest in snowsports will be on the rise. As a ski enthusiast, I (Yahagi) have taken the liberty of assuming that this is the case.
So in this issue we will consider automation in skiing and snowboarding related operations.
We will establish a system to provide ski and snowboard rental application forms in a Google spreadsheet file. When a potential renter selects whether they want to rent skis or snowboards, an email will be sent to them with the URL of the application form for the one they have selected.
- The prospective renter selects either skis or snowboards on the application request form and enters their email address.
- A Google Sheets file is created.
- The prepared rental application form (for skis or snowboards) is copied into the created file.
- The URL to view the created file is inserted into the email and sent to the rental applicant.
A workflow diagram of this business can be created with Questetra BPM Suite.
The leftmost round item (Application Request Form) in the workflow diagram represents the form for requesting an application form. The workflow starts when a prospective renter enters their email address and other information here.
The three gray squares are automated items that interact with Google Sheets. Two types of automated items are used: [Google Sheets: Create File] and [Google Sheets: Copy Sheet].
- Spreadsheet file creation (Google Sheets: Create File)
- An application file is created to guide prospective renters.
- Copy of ski application form (Google Sheets: Copy Sheet)
- The application sheet for ski rental that was prepared in a separate file is copied to the created application file.
- Copy of snowboard application form (Google Sheets: Copy Sheet)
- The application sheet for snowboard rental that was prepared in a separate file is copied to the created application file.
The diamond-shaped item relates to a conditional branch, while the round item labeled „email“ will automatically send an email.
Each item will be configured as shown in the following figure.
The Parent Folder ID in [Google Sheets: Create File] is the blue portion of the folder URL where the file will be created, as shown below.
https://drive.google.com/drive/folders/folder-id-example-folder-id-example-folder-id-exampleAlso, set the share settings for this folder to everyone who knows the link. (Settings in Google Drive)
With [Google Sheets: Sheet Copy] a sheet from a prepared spreadsheet is copied to a specified spreadsheet. In this case, „Ski Rental Application“ and „Snowboard Rental Application“ are prepared as the source sheets to be copied.
Skiing and snowboarding require slightly different things to check before renting. For skis, the only thing you need to check is the ease of disengaging the bindings (release value), but for snowboards you need to check the angle of the bindings and the width of the stance.
In the URL you have prepared to display the source spreadsheet, enter the blue portion shown below in the „ID of Spreadsheet“ field.
https://docs.google.com/spreadsheets/d/spreadsheet-id-example-spreadsheet-id-example
As shown in the following figure, when a prospective renter requests a rental application form they will receive an email and can review the rental application form.
Summary
For Google Sheets we have built a system that utilizes items for which files are automatically created and sheets are automatically copied.
By using the automation items provided in Questetra BPM Suite, a cloud-based system internalization platform, you can build a system to operate Google Sheets automatically without any knowledge or experience of Google Sheets API (Application Programming Interface).
Questetra BPM Suite is available on a 60-day free trial basis. If you are interested please sign up below. (No credit card required!).
Various other types of automation can be achieved but that’s all we’ve got time for today!
