Questetra provides a variety of automation items that work with Google Workspace. By combining these automation items you can design a workflow that integrates Google Workspace services and Questetra.
This article introduces a Workflow App that performs add/retrieve/update/delete operations using employee information managed in Google Spreadsheets as an example.
About Each Automation Items
Automation items related to Google Spreadsheets include [Create File], [Add New Sheet], [Copy Sheet], [Append New Row], [Append New Rows (Table type data)], [Download Choice Data], [Get Row], and [Update Row]. This time, four of them are used: [Append New Row], [Get Row], [Update Row], [Download Choice Data]. In addition, [Update Choices] is also used to update the choices master file on the workflow platform.
First, a brief description of each items
Google Sheets: Append New Row
Add a row to the end of the sheet and enter data in each cell. Expand the row area if necessary.
Google Spreadsheet: Get Row
Retrieves data from a specified row in a Google spreadsheet. To specify a row, prepare a String-type Data Item (single row) and enter the target row number. Input values for each column of the spreadsheet are stored separately by preparing a String-type Data Item (single line) for each column.
Google Spreadsheet: Update Row
Updates data in the specified rows of a Google spreadsheet. To specify a row, prepare a String-type Data Item (single line) and enter the target row number. Specify a range of cells to be updated and specify the input contents for each cell.
Google Spreadsheets: Download Choice Data
Retrieves data in two specified columns from a Google spreadsheet, preparing two String-type (multi-line) Data Items and storing the retrieved data in each column.
Update Choices
Register and update the list of choices defined in the Select-type Data Item as an app-shared/app-specific choice master (add-on). It must be set by a user with app management authority. Each choice list specifies a String-type (multi-line) Data Item that will be used as a choice ID display label.
Sample App
Here is a workflow diagram of a sample App created using these automation items.
The starting point is divided according to the operation to be performed. Retrieving/updating employee information starts from the same point, retrieves the information, and then modifies and updates it.
Note that this App handles two employee lists.
- Google spreadsheets (stored on Google Drive) that record employee details
- Employee master XML file (stored on Questetra platform) that corresponds to the line numbers in the spreadsheet containing the individual employee information
Please make the distinction between these two lists clear before reading the subsequent explanations.
Target Spreadsheet
As an example of a spreadsheet to be manipulated, we assume an employee list as shown in the figure below.
Notice that column A is populated with the same numbers as the row numbers. Since each automation item uses the row number to specify the target record, the number in column A is used to specify the target row for retrieving/updating/deleting information.
Columns B and after are as follows: B: employee number, C: name, D: email address, E: team, and G: string (team / name / email address) that will be the display label in the employee master file.
Setting up Google and Questetra integration
Please refer to the description in Adding Table Values to Google Sheets and Getting the Aggregate Value for setting up the linkage with Google.
Since the scope in Google is different for [Get Row], [Download Choice Data], and [Append New Row], separate authorization is required.
Data Item Settings
Data items are set as follows
| Data Item Name | Field Name | Data Type | Description |
| Title | – | – | The title of the Process |
| Employee master (for updating) | q_master_staff | Select-type (Search Select Box) | Referenced when updating the Choice Master File. It is not displayed on the operation screen, but only on the confirmation screen. |
| Choice ID | q_item_value | String-type (multiple lines) | This will be the choice ID for the Employee master (for updating). |
| Display label | q_item_display | String-type (multiple lines) | This will be the display label for the Employee master (for updating). |
| Employee master (for reference) | q_staff | Select-type (Search Select Box) | Specify target employees to retrieve/update |
| Row number | q_line_num | String-type (single line) | The IDs of the choices selected in the Employee master (for reference) are stored. This is used to specify a row in the spreadsheet. |
| Employee ID number | q_staff_id | String-type (single line) | Stores data retrieved from spreadsheets. |
| Email Address | q_staff_email | String-type (single line) | Stores data retrieved from spreadsheets. |
| Full Name | q_staff_name | String-type (single line) | Stores data retrieved from spreadsheets. |
| Team Name | q_staff_team | String-type (single line) | Stores data retrieved from the spreadsheet. |
| New Employee Number | q_new_staff_id | String-type (single line) | Enter the employee information to be included when adding a new employee. |
| New Employee Email Address | q_new_staff_email | String-type (single line) | Enter the employee information to be included when adding a new employee. |
| New Employee Name | q_new_staff_name | String-type (single line) | Enter the employee information to be included when adding a new employee. |
| New Employee Team Name | q_new_staff_team | Select-type (Select Box) | Select and enter the employee information to be included when adding a new employee. |
| (Added row number) | q_row_number | String-type (single line) | Saves the added row numbers when adding a new employee. |
| (Labels for master) | q_master_id | String-type (single line) | Saves the string synthesized in the Label Generation Step. |
(Labels for master) are Team Name / Name / Email Address so that you can search and input more easily in Employee master (for reference) (Select-type (Search Select Box)). This way, you can narrow down your candidates by just typing a few letters of your team name, name, or email address.
Key Points for Automatic Process Setup
(For settings other than those listed below, see Adding Table Values to Google Sheets and Getting the Aggregate Value
Generate master label (Update Data)
- Data Item to be set: [(Label for master)]
- Value or Expression: #{#q_new_staff_team} / #{#q_new_staff_name} / #{#q_new_staff_email}
Append new (Google Sheets: Append New Row)
- C4: Data item to save the appended row number: [(Added row number)]
- C-A to C-G: Reference expressions for Data Items to be inserted in each column (e.g. #{#q_new_staff_id})
Append row number (Google Sheets: Update Rows)
- C4: Row to Update: [(Added row number)]
- C5. Column Range to Update: A:A
- C6_1: Value that is updated in the 1st column: #{#q_line_num}
Get data (Google Sheets: Download Choice Data)
- C4: Range of Choice IDs : A:A
- C5: Range of Choice Labels: G:G
- C6: Data item that will save Choice IDs: [Choice ID]
- C7: Data item that will save Choice Labels: [Display Labels]
Update master (Choice Master Update)
- Select data item whose choices will be saved: Employee Master (for updating)
Delete employee (Google Sheets: Update Rows)
- C4: Row to Update: [Row number]
- C5: Column Range to Update: B:G
- C6_1: Value that is updated in the 1st column: (deleted), etc.
- C6_2 to C6_6: “-“, etc.
Get info (Google Sheets: Get Row)
- C4: Row to Get: [Row number]
- C5: Column range to get: B:E
- C6_1 to C6_4: [Employee ID number] [Full Name] [Email Address] [Team Name] respectively
Update info (Google Sheets: Update Row)
- C4: Row to Update: [Row number]
- C5: Column range to Update: B:G
- C6_1 to C6_4: Reference expressions for [Employee ID number] [Full Name] [Email Address] [Team Name] respectively
- C6_6: Reference expression for [(labels for master)]
New Addition Flow
This section describes the section of the flow for registering a new employee.
Start with [Register New], enter the information of the employee to be registered and finish.
In the next Update Data [Generate master label] step, the string inserted in column G will be synthesized. The Data Items [New Employee Team Name], [New Employee Name], and [New Employee Email Address] will be referenced respectively.
At the subsequent automatic step [Append new], the information entered in the first steps is inserted into the cells in columns B through G of the row following the last row of the spreadsheet. The row number of the inserted row is also retrieved and stored in the [(Added row number)] Data Item.
The value of [(Addedg row number)] will be inserted in column A in the next [Append row number] step.
Employee Master Update Flow
This section describes the section of the flow for retrieving data from the updated spreadsheet and updating the [Employee Master] with the retrieved data.
This flow is connected from each of the Add/Update/Delete Employee Information flows on the spreadsheet so that changes to the spreadsheet are reflected in the employee master on the workflow platform. If the spreadsheet is manually modified directly, start with the [Update master] step to update the Employee Master.
The [Update master] step is to confirm the input in each flow. If the Process was started manually, finish it without doing anything and proceed to the next step.
At the Get Choices step the spreadsheet is accessed and the data is retrieved; all data in column A is retrieved and stored in the Choice ID Data Item. Similarly, all data in column G is retrieved and stored in the Display Label Data Item. The stored data is separated line by line.
At the next step of the [Check Choices] step, the retrieved data is confirmed. In the Choice data, each line of the Choice ID and Display label corresponds.
The [Update master] auto-step overwrites the Choice Master XML file with the column A data stored in the Choice ID and the column G data stored in the Display Label.
Confirm the update by viewing the contents of the selected Data Items in the Employee master (for updating) at the [Check Update] step.
For more information on creating/updating the Choice Master XML file using Google Spreadsheets: Download Choice Data, please refer to the following page.
Utilize Customer Data on Google Sheets as Choices
Employee Deletion Flow
This section describes the flow for deleting registered employee information.
Specify the employees to be deleted at the [Delete Employee] step using a Select-type Data Item. At this time the Select-type Data Item refers to the Employee Master.
The value in column A (matching the line number), which is the choice ID of the choice selected is set in the Row number Data Item at the next [Set row number] auto-step.
The subsequent [Delete employee] process overwrites rather than deletes the row with the row number of the value set in the Retrieve Row Data Item. The overwrite targets columns B through G and is overwritten with (deleted) or “-“.
This prevents the deleted employee’s row from being retained in the spreadsheet and changing the row number in subsequent rows (i.e. shifting the value in column A).
Employee Acquisition/Update Flow
This section describes the flow of retrieving/updating registered employee information.
Specify the employees to be included in the[ Get/Update Info] step using Select-type Data Items. Again, the Select-type Data Item refers to the employee master. As in the Delete Flow, the row number is set to the Row number Data Item at the [Set row number] step.
At the [Get info] step, the data in each column in the row specified with the value set in the Row number Data Item in the spreadsheet will be retrieved and set in the Employee Id number, Email Address, Full Name, and Team Name Data Items respectively.
Review the acquired data at the [Confirm/Edit Info] step. If nothing is to be done, click the Finish button to exit.
To check another employee’s information, click the Reselect button to return to the first step.
To change and update employee information, edit the values of Employee ID number, Email Address, Full Name, and Team Name. (For example, if the team has changed, replace the Team Name with “Team B,” etc.) Once all edits have been made, click the Update Info button to proceed to the flow for updating the spreadsheet.
At the [Generate master label] step, the strings are combined with the updated contents. Subsequent employee information updates overwrite the data in the rows of values in the retrieved rows of the spreadsheet.
The above settings are for operation with the Advanced edition.
The Professional edition can also be configured to use add-on auto-processing to identify line numbers using employee numbers or email addresses as keys. In this case, the entire row can be deleted when deleting a record.
In this way, by combining Questetra’s automatic processes, operations of external services can be performed from Questetra, and records of operations performed by Questetra will be stored in Questetra as history.
Automated processes are also available that work not only with Google Workspace, but also with Box, kintone, and others. Please make use of the combination to automate your business operations.
Pingback: Automatically Populate Multiple Data Fields with Selected Employee Information – Questetra Support