Google Sheets and DronaHQ Studio integration is an easy and assured go-to solution to build user interfaces over the structured data storage that is the power of Google sheets.
Configuring Google Sheet connector
The Google Sheets connector is available under Ready Third-Party Connectors.
-
Select Google Sheets and click Continue.
-
Select the Action from Select from Google Sheets and click Continue.
-
Now you need to add the Connected Account to Authenticate. If you have already configured your account, you simply need to choose it from the list and click Continue.
-
Now Configure connector fields. Add a Connector name. Then add the respective Fields for the connector account
Once all details are added, click Finish. Your connector configuration is now done.
Using Google Sheet connector
Get all rows
To fetch all rows matching criteria you can use the GetAllRows connector. To configure fields you can provide the dynamic values using the keywords. You can add variables to return a response value to this connector. There are several Output values returned which can be used as required. In this example shown below, we will first fetch all the rows matching the Search Value which is a dynamic value provided using the keywords.
Then we will be using variables to fetch the data from column B for the searched rows. You can see here that we have also used the Row number as well as a return value. These Row number values can be used in other use cases or scenarios like updating rows, deleting rows based on certain conditions, and so on.
.
While using GetAll Rows in Google Sheets the response had fixed keys values.A and values.B and so on. Studio has introduced a new enhanced feature Use Header Rows option that replaces the fixed keys with headings in your sheets.
For the GetAll Rows option, you provide the required inputs, and view the result with fixed keys B and C.
In the above example we can view fixed keys B and C whereas the headings of the google sheets action-name, type and so on are viewed as rows.
Now change the Use Header Row in Response to True/Yes. Click Refresh Response you will be able to view this screen.
Click Refresh Response> Done. Select the columns to bind. View the result.
Find a Row
Now you can also find a row based on criteria. In this case, the output is a single row only. So based on what you provide as the criteria the first one that matches the criteria is displayed.
Now if we take an example to find a row from the Marketing Team sheet which contains the name “Joe” you can see in this sheet that there are two rows matching the criteria. However, this action will fetch only the first row that matches the criteria. Add the Search Column and the Search Values. In the example, if we specify that the row to be searched would be searched from top to bottom, then in that case we would get Row number 2 from the sheet as in the example below. You can add a Response variable to return the Output values. You can fetch all values or from a particular column using output.values.A. The first matching row would be returned.
In the next action like say a Popup task, you can use the response variable and then display the values fetched.
Find Rows
The Find Row option searches and fetches only the first row that matches the criteria. However, if you want to find all that match the criteria, then you can use the Find Rows action from the Connector. So based on what you provide as the criteria all the rows that match the criteria are fetched.
Now if we take an example to find a row from the Marketing Team sheet which contains the name “Joe” you can see in this sheet that there are two rows matching the criteria.
Both the rows will now be fetched. In the example, if we specify that the row to be searched would be searched from bottom to top, then in that case we would get Row number 2 and row number 4 from the sheet in the example below. So it returns an array of matched rows.
Now in this case as well you can make use of the appropriate Response value to return to the next task.
Now, whenever you run this form you would get an array of the rows matching the criteria fetched. You can see the pop-up task taken for this example. Note that all the matching rows are fetched and displayed as an array.
Another feature available in both the actions is to add an additional Supporting Search Column which is an optional criteria. So whenever the condition is specified, only the row/s that match both the criteria will be fetched.
Add a new row
You can also use the integration to add a new row to the google sheet. The Google sheet connector has an Add Rows action that can be used to add new rows to the sheet.
You can provide dynamic values using the keywords as input to the add new row function.
You simply need to add the SpreadSheet Name, the Sheet name, and Column Range. The fields from your sheet would be listed out. You can then bind them to the respective controls and the row/s would then be inserted into your sheet.
- Now whenever you want to add multiple rows you can enable Iterate the Task so that multiple rows can be added with the multi-repeat controls that you may be using.
Update row
Now in a situation where you want to update the specific rows in a Google sheet with some specific values, you would typically have to search the row, get the row number and then update the specific column with the specific values. In that case, we have seen how we can search a row and get a row number. We can then use this row number to update the record.
Let us take the same example to search we used earlier in the Search sample. We have fetched rows for the particular Team member’s Name. Now we want to update the Details for the Name. So we will provide the value returned for row id as the Row to Update and then add the details to be updated.
Add Multiple Rows
The Add multiple rows option comes to help when you want to add multiple rows to Google sheet. You can use data from tablegrid control for instance to add data to your google sheet.
We have data in a tablegrid control that we want to insert in our google sheet. So we use a button and use actionflow to insert rows in google sheet. Instead of inserting rows, we select the columns having collection of all the particular values of all rows. Our main aim is to provide an array of values for each column. For instance, our User data is of given format
Name, Email and Phone Number, we will provide the data for individual columns as follows
Name field value : [“user1” , ”user2” , ”user3”]
Email field value : [“email1” , ”email2” , ”email3”]
Phone field value : [“Phone1” , ”phone2” , ”phone3”]
in case of Array of arrays, each array will be inserted as a csv for each row.
For the button, create an action flow. From server side actions select google sheets > addmultiplerows option. Select the spreadsheet and sheet name. Columns are Dynamically generated. Select the columns from the tablegrid using keywords. Test and save it.
Additionally you can use an alert or another tablegrid to visualise the google sheet data.
GetSingleRow
To fetch the row details of a specific row number provided by the user can be done by using the GetSingleRow subcategory of the Google Sheet API connector.
You just have to select the subcategory and provide the sheet details such as the spreadsheet name and sheet ID along with the row number. These details will be used to fetch the row details of a specific row number from a certain spreadsheet.
You can add this connector in the data bind section or action of any control. Select the columns to bind in the control as keys. We will be using table grid control to view the data received by the API subcategory.
ClearMultipleRows
The Clear multiple rows option comes to help when you want to clear multiple rows to Google Sheets. You can use data from tablegrid control for instance to clear data to your google sheet.
The procedure is very similar to any other method, it is just you have to pass an array of rows’ numbers in the method along with the spreadsheet name and its sheet name.
This will clear the mentioned row numbers from the google sheet of the respective spreadsheet.
UpdateMultipleRows
The update multiple rows option comes to help when you want to update multiple rows to Google Sheets. You can use data from tablegrid control for instance to update data to your google sheet.
In the above image, you can follow that for multiple row numbers you can put multiple data for updating provided that the starting row number is taken into account and based on array size number of rows below will be affected. For example row number is 4 and the array size is 2, then rows 4 & 5 will be updated.
Notes
- It is important to remember that when you are updating any row and you do not want to update some of the columns then in that case you should Use Keywords as NULL. This ensures that the column is not overwritten. The original value is retained. Only the column/s with specified inputs are updated.
- You can query several columns at a time (A:AZ).
*. Once you configure a connector on DronaHQ Studio and you add Column to your Google Sheet, then your existing configured Google Sheet connector will have to be updated/refreshed in order to reflect the new column added in your Google Sheet.
Known Issue
- Column with Numeric name isn’t supported yet. If used, it may or may not work as expected.
Configure Google Sheet Account
-
To configure the account, under Authenticate Google Sheet, click +Add Account. Tag your account with the relevant name and click Sign in with Google. Choose your respective account and authorize access to the sheets and other files and you are done.
