Google Sheets
Last updated
Last updated
Let's see how we can integrate Google Sheets as a data source for building rules in our application. This feature allows users to leverage data from Google Sheets to create and manage rules efficiently without manually writing extensive tons of rules that you were writing before.
This would come handy if you want to use huge data within your excel, google sheets and want to run any logic/workflow over it. This can also be useful if you want to do certain POC for some point of time over sample data exported to google sheets before running the rules/workflow directly over your database.
Access to the Nected Platform
A Google account with access to Google Sheets
Navigate to the data source integration section in the left navigation pane and Select the option to connect a new data source.
Click on "Sign in with Google".
Choose the appropriate Google account and Grant the application access to your Google Drive.
After granting access, test the connection to ensure it's established correctly and publish it in Staging or Production depending on testing needs. Confirm successful connection before proceeding.
Before you start using the google sheet in rules, you need to add a new data source to your Nected environment. Follow these steps:
Now, select the sheet you wish to use from the list of available sheets in your Google Drive. Ensure the sheet contains the data needed for your rules.
Optionally, indicate whether the first row in the sheet serves as a header.
Navigate to the rule creation section in the Nected. Click on “Add Rules” and select a rule from the drop down menu.
Then click on "Add Inputs Attributes" and go to option “Map with Data Source” .
Select the Dataset from the drop down menu.
Fill in the required details and click on Save & Close.
Note: Ensure to map only one or two primary or unique key for efficient data retrieval. Rest of the data would automatically be fetched based on these primary keys
After defining input attributes, navigate to the "Property" tab.
In the dropdown menu, under "Input Attributes," you will find a list of attributes. These attributes have been mapped with sheet attributes on previous “Map with Data Source” step.
Define the conditions under which the rule should trigger and Use the mapped input parameters to set these conditions.
Optionally, set default values for certain conditions and Define fallback scenarios in case the primary conditions are not met.
Enter input attributes to test the rule.
Ensure these parameters correspond to the data in your Google Sheet.
Execute the test and Review the output to verify that the rule works as expected.
Triggering a Gsheet as an action is simple. Follow these steps:
Click on the "+Add Action" button and it will open the below list. Choose Gsheet from the options.
After selecting the Gsheet, there are two options:
Create new Connector: Click on the "+Gsheet" and it will take you to the integration page, on a new tab. Then follow the same steps to create a new connector and then come back to the rule editor. Then click on the refresh button and the new connector will be appear in the list. Click on the connector which will open a tab like this:
Now select any methods from the two given there: "Add Single Row", and "Update Rows".
Then select the spreadsheet by clicking on Add sheet. Now choose a worksheet from your account and add it as shown in the GIF.
For the update operation, simply follow the steps we performed in the add operation.
After that, you can simply test if the sheet is working as an action by testing the rule.
To add Gsheet as a node in Workflow, follow the below steps:
Click on the "+" icon and from the list of nodes, add Gsheet.
The same as adding action choose one connector from the given list.
After you select the list, as shown in the GIF, the side tab will be opened and then you can select the action. For workflow, the Gsheet has 3 actions:
Update Row(s): To update one or more than one rows in your worksheet, you can use this action. After selecting this action fillup the additional details and then you're good to go.
Insert Row(s): To insert values in one or more rows you can use this action. To do this, just select the action and fill in the details accordingly, and you'll be good to go.
Look-up: Look-up is to find one or multiple rows. So, if this action is selected then you can write conditions and can fetch the matching rows accordingly.
Once the configuration is done, test it and simply save it.
While integrating Google Sheets with Nected, you might encounter some issues. Below are the common problems users face during the process of connecting a Google Sheet as an action or node, along with solutions to resolve these issues.
Problem: After selecting Google Sheets, the system fails to authenticate or connect, showing an error like “Authentication Failed” or “Unable to Authenticate Google Account.”
Cause: This usually happens if the Google account authorization is incomplete or revoked.
Solution:
Ensure that the Google account you are using has been properly authenticated.
Go to the integration page and check if the Google Sheets API is enabled for the connector.
If the problem persists, revoke access from your Google account (via Google Account > Security > Manage third-party access) and then re-authenticate.
Ensure your Google account has the necessary permissions (like Edit access) to the spreadsheet.
Problem: After creating a new Google Sheet connector, it does not appear in the list, even after clicking the refresh button.
Cause: This could be due to caching issues or improper connector setup.
Solution:
Refresh the entire page and retry adding the connector.
If the connector still doesn’t appear, ensure that the integration was completed successfully. Check if the new connector is visible in the integrations page under ‘Connected Apps.’
Clear your browser cache and try again.
Verify if the connector was saved correctly on the integration page. You may need to recreate the connector if issues persist.
Problem: After clicking on "Add Sheet" or "Choose Worksheet," no sheets or worksheets are listed, or the target worksheet does not appear.
Cause: The Google Sheets API might not have sufficient permissions, or the sheet is not accessible.
Solution:
Ensure that the Google account you're using has permission to view or edit the target spreadsheet and worksheet.
Check whether the spreadsheet has been shared with the authenticated Google account (if you're using a different account than the one where the spreadsheet resides).
Confirm that the spreadsheet is not in the trash or deleted.
If it’s a protected sheet, ensure that you have the necessary edit permissions to access it.
Problem: Errors such as “Failed to Add Row” or “Unable to Update Row” occur during the process of adding or updating rows in the sheet.
Cause: This typically occurs due to invalid data, incorrect cell references, or formatting issues in the Google Sheet.
Solution:
Verify that the data being passed to the sheet matches the expected format (for example, numeric data in text fields, missing required columns, etc.).
Check that the specified worksheet and range are valid and that the cells being updated exist.
Ensure that the Google Sheet is not protected or locked for editing.
Test the rule with simple data inputs to ensure that the connection is working before using dynamic or complex data.
Problem: The Google Sheet integration takes too long to complete, or times out before an action is processed.
Cause: This might happen due to large datasets in the spreadsheet or poor network connectivity.
Solution:
Ensure the internet connection is stable and retry the operation.
If the sheet contains a large dataset, break the data into smaller chunks or use pagination to limit the rows being processed.
Check if any Google API rate limits are being hit and reduce the frequency of actions to avoid throttling.
Problem: When using the lookup function, the expected rows are not returned, or the function doesn’t work.
Cause: Lookup failures often occur due to incorrect condition settings or mismatched data types in the conditions.
Solution:
Ensure the lookup conditions are written correctly and correspond to the correct columns in the sheet.
Check if the data you're trying to match exists and is correctly formatted (e.g., text vs. numeric types).
If using dynamic values, verify that the values passed to the lookup condition are accurate and within the acceptable range.
Problem: The data fetched from the Google Sheet is incomplete or inaccurate.
Cause: This could occur due to incorrect data mapping or row/column selection in the connector configuration.
Solution:
Double-check the cell ranges or rows you’ve specified in the configuration.
Ensure that the data types match between the Nected inputs and the Google Sheet columns.
If working with dynamic data, validate that the correct data is being passed from the rules or workflow to the Google Sheet.
Navigate to the Data Sources section in the left navigation panel ,click on “+Create Data Source” and select the Google sheet option from the drop down menu.
Add from List: From the available list, choose one.
