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.
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.
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.
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.
- 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).
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.