Sync a Table with a Google Sheet using Make.com
# 📖tutorials
m
This tutorial will show you how to use an Execute Code card inside your Botpress workflow to sync a Table with data from a Google Sheet by wiping the table and replacing the data with the updated values. Note: The free account with Make.com includes 1000 operations per month. The scenario below uses 5 operations per run. Step 1: Create a Table and Selector - Create a Table named 'SheetsDataTable' - Add headers to match the data you will pull over from Google Sheets - Create a selector named 'allRecords' and set the Selector Query to 'Find all records' Step 2: Execute Code - Create a new node with an Execute Code card. Use the following code and insert your webhook ID from Make.com. - (This also sends the current date and time to the webhook to optionally keep a log of when this runs in a Make.com data store. This is not shown in the scenario below.) code in comment below - post was too long to paste here Step 3: Create Make.com Scenario - Create an account or log into Make.com and create a new scenario. Set it up with these modules in the screenshot. - Create the webhook and run the code to send test data. - Set up your Google Sheets module with the data you want to collect, excluding the header row. (eg A2:Z1000) - Follow the Make.com documentation to create the JSON element (name the JSON element 'data' to match the code below or update it accordingly) - https://www.make.com/en/help/tools/json. - Send the JSON back to Botpress in the Webhook response. - Turn on the scenario You should now have a working one-way sync between Google Sheets and a Botpress Table. Add the Execute Code card anywhere in the beginning of your workflow to populate the table. If your bot runs frequently, you may want to limit the amount of times it runs or pay for an account with more operations in Make.com.
const currentDateAndTime = luxon.DateTime.now().toISO() const webhookUrl = 'https://hook.us1.make.com/4dg3ua8jld2bydey07qt2k2jfx6wkw53' try { const response = await axios.post(webhookUrl, { currentDateAndTime }) const webhookResponse = response.data const { data } = webhookResponse // Extract the array (this should be the same name as the Name and Label of the JSON element in Make.com) await removeAllRecords() await insertRecords(data) } catch (error) { console.log(error) } async function removeAllRecords() { try { const data = await SheetsDataTable.findRecords({ selectorName: 'allRecords' }) if (!Array.isArray(data)) { console.log('No records found or data is not an array.') return } const recordIDsToDelete = data.map((record) => record.id) if (recordIDsToDelete.length > 0) { await SheetsDataTable.deleteRecords(recordIDsToDelete) console.log('Records deleted successfully.') } else { console.log('No records to delete.') } } catch (error) { console.log(error) } } async function insertRecords(sheetsData) { try { await SheetsDataTable.createRecords(sheetsData) } catch (error) { console.log(error) } }
c
Thank you so much for sharing this with the Community @melodic-barista-55125 ! 🚀
b
Hey @melodic-barista-55125 your instructions are amazing! Im just having one issue. The data wont save to the table. Its giving me this error: [TypeError: Cannot read properties of undefined (reading 'map')]
h
Hey melodic-barista-55125 thank you for the excellent explanation. I am having the same issues as bland-hamburger-59230 with a Type Error when trying to add the Jason data to the botpress table. Any ideas on what could cause this? I verified that my table column names match the key names coming in from Make in the json file. Here is a small sample of the json data I'm returning from Make. { "Gsheet Data": [ { "Product": "Series 1: Chairs", "Configuration Step": "Step 1 Select Your Chair", "Item Description": "Series 5 Chair w/200W Transformer (Powers Chair, Edge Lights & Accessories)", "Part Number": "C6650", "Color": null, "Retail Price": "$9,100.00" }, { "Product": "Series 1: Chairs", "Configuration Step": "Step 2 Select Your Back Style", "Item Description": "ErgoBack Plush", "Part Number": "52319", "Color": null, "Retail Price": "N/C" } ] } Any suggestions, ideas you might have would be super helpful!
m
@bland-hamburger-59230 @aloof-memory-59908 Be sure you set the name of your JSON element in Make.com to the same variable in line 6 of the code.
*UPDATE: The code has been updated to work if there are zero records found in the table and doesn't error out at that point any more.
p
If I wanted to limit this to run once a day would it be in the bot or in Make.com ? Thanks so much!
b
Kk thanks I’ll take a look this weekend!!
m
Right now this is triggered by the bot itself using the execute code block to call a Make.com webhook. Webhooks are run immediately on-demand and the scenario uses the webhook response to return the data to Botpress. To schedule this to run automatically at a certain interval you'd have to remove the webhook and find another way to pass the data to Botpress. I'm not sure if there's an API endpoint for Botpress tables or not, but if there was then you could schedule the Make.com scenario to run on a schedule and send the data to Botpress via the API.
p
Thank you 🙂 @bumpy-butcher-41910 can you help here please?
c
@early-train-33247
e
Hey guys we have Schedule Triggers in the studio. You could use them to start a conversation on a fixed schedule
This one runs every day at 12pm
Check out this third-party tool for creating cron expressions (schedules): https://crontab.guru/
j
any yotube tutorial ?
m
If i create it like this, it will only import the data one time a day true?
e
That's correct
b
heyyy
I have a question, in my scenario everything looks perfect. But in my botpress table only 595 record were added. In my airtable modelue I have the limit set to 650 records. https://cdn.discordapp.com/attachments/1138855942235103403/1230920356210933861/Screenshot_2024-04-19_at_11.24.47_AM.png?ex=663512ea&is=66229dea&hm=e1a71e2d80cb01dfbedf2041a3820c49b89598cccab02ff92d711297fb617210&
@quick-musician-29561 @melodic-barista-55125
What could be the issue
q
I haven't tried it with such large datasets. If everything works with a smaller number of records (as it has worked for both of us so far), then my best guess is that it has something to do with KB table limits or other limits. If there are any error messages, those will provide more information.
f
@best-army-74344 I'm curious to know why you decided to duplicate your data in two places Google Sheets and Botpress Tables. Why not just use botpress tables? You can access them from external systems also using the Botpress Client. https://www.npmjs.com/package/@botpress/client
b
my data comes from airtable, I have a code that when I need to update the table in botpress it will get the data from airtable
Can you explain me about this
f
I see. Sorry for the misunderstanding. So yeah, where is the data in Airtable coming from?
b
Will be the database of the client
f
Is it an app you've created?
Who enters the data in this Airtable?
b
they will
f
By going to Airtable?
b
yep
f
Well, if you ditch airtable and just use Botpress Tables, you won't need to store data in two places.
And won't need to worry about the data sync
b
I get your point
f
Do you have any programming experience?
b
But this way I can automate that don't you think? (I'm asking bc I'm intereted in your opinion)
f
Automate what exactly?
b
6/10 🤣
adding data to the table just running the bot
f
How do you mean? Maybe I was not clear. Instead of person x going to airtable and entering data there manually. They can go to botpress bot and enter the data in the Botpress table. Botpress Tables are way better for your use case. Unless you don't want to give your client access to botpress studio.
17 Views