In this article, you will learn how to connect your Manychat account to Google Sheets and how to simply transfer your contact's data to tables by the actions in automations. We will cover the following sections:
- Connecting your Google account
-
Adding Google Sheets action to the automation
Setting up your Worksheet action - Limitations
- Troubleshooting
Connecting your Google account
First, you have to connect your Google account to Manychat:
- Go to Settings > Integrations.
- Tap the button Connect Google Sheets Account and confirm the authorization.
- Now, you can start using Google Sheets integration.
Adding Google Sheets action to the automation
You can add the integration action to one of your automations. When your contact passes through this action, their data will be pushed to your Google Sheet.
Create a new worksheet in your Google Docs and prepare column names on the first row of the document:
Go to the automation and make a new action node:
Choose Google Sheets actions:
Setting up your Worksheet action
Insert row action
This feature allows sending data from Manychat to Google Sheets.
Select the Insert row action, and the Google Sheets wizard will appear.
Choose your spreadsheet, worksheet, and match Manychat fields to the worksheet fields. All contact's data, Custom User Fields, bot fields, or tags are available to send.
💡 The tags are exported to the worksheet as True/False values, indicating if the contact has a corresponding tag or not.
Once the action is set up, press the Save button.
Now you can try your automation, and the chosen data will appear in your worksheet:
⚠️ If you want to change the structure of your worksheet, you have to go to the wizard of your Google Sheets action and refresh the fields.
Get a row from Google Sheets by lookup value
This action lets you import data from the Google Sheet into Manychat.
Select the Get row by value action, and the Google Sheets wizard will appear.
Choose your spreadsheet and worksheet.
As the lookup column, set the worksheet column where the integration will search for the information.
The lookup value will be searched in the lookup column. You may use system fields, custom user fields, custom bot fields, or tags as a lookup value.
⚠️ Note: Make sure the lookup value is unique for each row of the lookup column. If a Google Sheet integration doesn’t find any value identical to the lookup value, it won’t return anything. If you have two or more rows with the same values, only the first row will be returned.
⚠️ Note: If possible, avoid searching lookup values in the cells containing formulas. Although such setups might work, we can’t guarantee the 100% functionality.
Correspond the columns of your worksheet with the fields in Manychat. The information from the selected columns will be saved into set fields.
Once the action is set up, press the Save button.
Now, let’s review our action and check how it works. Basically, when the contact activates the get row by value action in the automation, Manychat takes a value from the field set as a lookup value and searches it in the lookup column of the worksheet.
In our case, it will search for a value from the custom user field called User ID in the Customer ID column.
As we can see, the following row should be picked up in our example:
Next, the integration will check the worksheet columns we set in the action to be saved into fields in Manychat.
If the corresponding cell has any value, it will be saved into a specified field. If no field is selected for the column, no data will be saved.
In our example, we are saving the value of the column Favourite Food into the Favourite Food custom user field.
After the automation is executed, the selected field is updated with the value from the Google sheet.
Update existing row
This feature allows you to update the information in a Google Sheet you have already filled out, using a search in the lookup column and a custom field as the lookup value.
Create a Google Sheet action and select the Update row sub-action.
Select the spreadsheet, the worksheet, the lookup column, and the lookup value. The action will look for the lookup value in the lookup column of the worksheet to find the row where the information should be updated. This process is similar to how the get row by value action works.
Set the fields with data you’d like to update in the corresponding column of the worksheet.
Press the Save button to finish the setup.
Once the automation is executed, the setup worksheet column will be repopulated with the field’s current value.
Limitations
Google Sheets integration has a limit of 150 requests per 60 seconds. This limit applies to the user who has enabled this integration under their Google account. If the user has multiple pages in Manychat and connected Google Sheets integration, then the limit will be shared across those pages. Hitting this limit causes the error in Settings > Logs: "X or more requests were not processed due to the Google API limit", and the integration action won't be executed, but the processing of the flow will continue.
We have implemented monitoring of the requests being performed to Google Sheets per such user. All the automations that trigger integration and overflow the limit will be suspended at the point of Integration Action until they can be executed according to the limit. In such cases, you will see a new Warning in Settings > Logs instead of the error: "X or more requests exceeded Google API limit and will be throttled and executed later."
⚠️ Note: Actions that should be executed by triggers & actions can't be suspended, so we will attempt to execute them anyway without checking the limit.
Troubleshooting
Numbers are saved in E+15 format
To avoid having long numbers saved in exponential notation (2.81837E+15 instead of 2818370524879520), please make sure to format the cells of your sheet to Plain Text before passing the data to your sheet.
You can do this by opening our sheet and going to Format > Number > Plain text:
Please make sure to format the cells to Plain text before the data is passed to your Google Sheet.
Data isn't passed to Google Sheets
Make sure that the fields you're using to send data from Manychat to Google Sheets actually have some values in them.
If you're collecting your Contact's information with Data Collection blocks, don't forget to save the reply into a System Field:
Or specify a Custom User Field to store the answers before trying to pass this info to the Google Sheet:
If you're seeing weird behavior when passing data to Google Sheets, you can try creating a new sheet and copying your existing structure there.
Make sure the Sheet you're using is actually created in your account and is not shared by someone else. This may cause some unpredictable behavior, so it is a good idea just to copy the entire shared sheet to a new one created in your Google account.
Google Sheets API limit
Google API can only process 250 requests to one Google Sheets Account in 100 seconds, meaning that your Automation can send only 250 users into that sheet per 100 seconds. If you send more, Google may reject your request, and all the following data will be lost. We recommend splitting your contacts into smaller groups to get it done.
Here is a simple example of how you can split your contacts within a certain automation using Randomizer and Smart Delays with different delay times:
A new row is created instead of being updated in Google Sheets
If you're seeing duplicates inserted instead of getting your rows updated in Google Sheets with Update row action, please try the following:
Make sure you've specified the correct Lookup Value and Lookup Column in your Update row action:
Below are examples of the Lookup Column:
And a Lookup Value:
If the values are specified correctly, then the formatting of your Google Sheets document might be causing this issue. To solve that, please try selecting all the cells in your sheet and formatting them to Plain Text:
If you still see duplicates, please try creating a new empty sheet with the same structure and formatting the cells to 'Plain Text'. Then select the new Sheet in your Update row action block and set up all the fields accordingly.
Can’t get sheets. backendError: Internal error encountered.
If you ever come across an error like this while selecting a certain Google Sheets doc in one of your Google Sheets actions:
Then, you should check whether there are any drawings hanging outside the canvas of the worksheet in this document. Please make sure to check all the worksheets of the problematic Google Sheets doc.
Here's how it may look:
If you find any drawings or text boxes that exceed the current canvas of the worksheet, please make sure to resize the drawing to fit the current worksheet or simply add some rows or cells so it fits. Alternatively, you can delete the drawings from your worksheets.
After this is done, please try selecting the Sheet in the Google Sheets action again.