In this article, I will explain the automatic process relating to Google Sheets.
Google Sheets is an online spreadsheet software provided by Google. Anyone with a Google account can use it for free. Also, if the APIs are enabled in Google Workspace, you can access it from the outside to perform various operations. The automatic Steps introduced here use them.
About each Auto-step
There are several preinstalled features related to Google Sheets, such as [Create File], [Add New Sheet], [Copy Sheet], [Append New Row], [Append New Rows (Table type data)], [Download Choice Data], [Get Row], and [Update Row]. In this article I will use [Append New Rows (Table type data)] and [Get Row].
First, I will briefly explain each auto-step.
Google Sheets: Append New Rows (Table type data)
It appends the values in the Table-type Data Item to the specified Google Sheet. The field names set in the sub-items of the Table-type Data Item are used to specify the target column on the spreadsheet.
Google Sheets: Get Row
It retrieves the data from the specified row in the Google Sheet. To specify a row, prepare a String-type Data Item (single-line) to enter the target line number. The input values for each column in the spreadsheet are prepared as String-type Data Items (single line) and saved separately.
Example App
Below is the Workflow Diagram of an example App using these auto-steps.
This App appends the content entered in Daily sales (a Table-type Data Item) to the spreadsheet and gets the cumulative value after the addition. It allows you to input test data to see its operations. By adding these auto-steps to Apps that use Table-type Data Items such as Out-of-pocket expense claims or Business trip requests, various budget consumption aggregations such as by person or by the department will be automatic since the table data in separate Processes can be amalgamated in one spreadsheet.
Preparing the Spreadsheet
You must prepare a spreadsheet beforehand.
Go to the Google page, then click the menu list () in the top-right corner and select Sheets. (If you are not logged in to your Google account, the login screen will be displayed.) You will see several templates in the [Start a new spreadsheet] section, so select Blank. When you enter the title of the spreadsheet it will be saved as a file in the My Drive area of your Google Drive. (You can also create a spreadsheet file in any folder of your Google Drive by clicking + New on the top left.)
Open the created file and make a note of the file’s ID. The ID is the following part in the URL.
https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0Enter the following as headings in columns A to E on the first row.
A: DATE, B: ITEM, C: QUANTITY, D: UNIT PRICE, E: SUBTOTAL
Then add another sheet to your spreadsheet. Click “+” (Add Sheet) at the bottom left of the screen to create Sheet 2 to the right of Sheet 1. Click the Sheet 2 tab to open it and enter AGGREGATE into the first row of the A column. Below that cell, the second row of the A column, enter the following function.
=SUM(Sheet1!E2:E)This means “display the total sum of the input values of the second row and below of column E of sheet 1”. Make sure that all the characters are entered in half-width characters.
Your spreadsheet is now ready.
- You can obtain various aggregate values by setting the spreadsheet, such as separating the sheets for each user and devising the aggregation target
Google and Questetra integration settings
I will explain how to set up integration between Questetra and Google.
- System Administrator privileges are required to set up HTTP Authorization settings
- The registered HTTP Authorization settings are also shared with other Apps on the same workflow platform
- Since the scope of the request of the two auto-steps is different, you need to add the settings using the same procedure in each step respectively
Place the aforementioned auto-step icons using the Questetra’s App editor. In the Append New Rows (Table type data) config screen, click the [Set up Settings] button below the C1: OAuth2 settings item to open the HTTP Authorization Setting Google Sheets API screen.
Click [Add] on the screen and specify a name of your choice. (An arbitrary string: it’s better to give a name that is easy to understand later.)
Click [Get Token] in the added settings to move to the Google authentication screen.
In the account with which you will manipulate the spreadsheet, allow the request.
You have now obtained a token for OAuth2 authentication with Google. You can confirm it by checking the “○ (refresh token)” indicator above the [Get Token] button.
Go back to the Append New Rows (Table type data) config screen and select and specify the setting name you have added in C1: OAuth2 settings.
Follow the same procedure on the Get Row Step config screen to set this up.
Data Items settings
Set up the Data Item settings as follows.
| Data Item Name | Field Name | Data-type | Required | [Enter Value] Step | [Confirm] Step | Description |
| Title | – | – | – | Editable | Display only | The title of the Process |
| Aggregate | q_Sum | String-type (single line) | No | No display | Display only | The aggregate value obtained from the sheet is saved |
| Row | q_Row | String-type (single line) | No | No display | No display | It is used to specify the row to be acquired. Set the numeric of the row number to the initial value. |
| Daily Sales | q_Table | Table-type | Yes | Editable | Display only | The contents will be added to the sheet. |
Set up the Table-type Data Item as follows.
| Table Item Name | Table Item-type | Field Name | Description |
| DATE | Date | date | |
| ITEM | String | item | |
| QUANTITY | Numeric | quantity | |
| UNIT PRICE | Numeric | unit | |
| SUBTOTAL | Numeric | subtotal | [Show calculated value] QUANTITY * UNIT PRICE |
Add table data (Google Sheets: Append New Rows (Table type data)) settings
Settings in the Add table data step are as follows.
| Config name | Required | Description |
| C1: OAuth2 Config | Yes | Specify the HTTP Authorization Setting name for connecting to Google Sheet APIs. You need to get the OAuth2 token in advance with the setting name to be specified. (Refer to Google and Questetra integration settings.) |
| C2: Target Spreadsheet ID | Yes | Specify the Sheet ID you made a note at Preparing Spreadsheet. |
| C3: Target Sheet Title | Yes | Enter the name of the Sheet (Sheet1) |
| C4: Table type data item | Yes | Select the Data Item Name of the Table-type (Daily sales) |
| C-A: Field Name of Sub Data Item for Column-A | No | Enter the Field name set in the Table Item of the Table-type Data Item. (date) |
| C-B: Field Name of Sub Data Item for Column-B | No | Enter the Field name set in the Table Item of the Table-type Data Item. (item) |
| C-C: Field Name of Sub Data Item for Column-C | No | Enter the Field name set in the Table Item of the Table-type Data Item. (quantity) |
| C-D: Field Name of Sub Data Item for Column-D | No | Enter the Field name set in the Table Item of the Table-type Data Item. (unit) |
| C-E: Field Name of Sub Data Item for Column-E | No | Enter the Field name set in the Table Item of the Table-type Data Item. (subtotal) |
| (Leave the following setting items blank) |
The value of each Table Item entered in the Table-type Data Item in the Enter Value Step is added to the specified spreadsheet column. Please note that an error will occur if no value is entered in the Table-type Data Item.
Get sum (Google Sheets: Get Row) settings
Settings in the Get sum step are as follows.
| Config name | Required | Description |
| C1: OAuth2 Setting | Yes | Specify the HTTP Authorization Setting name for connecting to Google Sheet APIs. You need to get the OAuth2 token in advance with the setting name to be specified. (Refer to Google and Questetra integration settings.) |
| C2: Source Spreadsheet ID | Yes | Specify the Sheet ID you made a note of in Preparing Spreadsheet. |
| C3: Source Sheet Title | Yes | Enter the name of the Sheet (Sheet2) |
| C4: Row to Get | Yes | Select the Data Item to be specified (Row) |
| C5: Column Range to Get | Yes | Specify the column to be obtained with the range. (A:A) |
| C6_1: Data item that stores the value in the 1st column | No | Select the Data Item to be specified. (Aggregate) |
| (Leave the following setting items blank) |
It retrieves the value of the AGGREGATE cell calculated on the spreadsheet. You can confirm the value obtained at the Confirm Step.
When you finish setting up the App, [Release] it and Start a new Process for a test run. If you have set it up correctly, you can see that the table data entered in the workflow is added to the spreadsheet and the accumulated value is automatically set in the Sum Data Item.
When you run multiple Processes, the accumulated value across the Processes will be retrieved.