Hey Community! I am currently working with data from my MySQL database, which I import to coda via Nodemation (open source version of zapier), but I find myself quite limited with this method.
I have seen people in this forum talking about huge data movements, databases being live-updated to coda, and APP-like documents.
How do you connect your databases? Is there any must-know trick that I should be aware of?
Let me know your ideas please 
Thank you
I’m in. I have the same problem, I need to connect coda with an acces database and another mariadb and I don’t know how to do it without using zapier.
Hey @SureKT @Ximo_Escamilla,
I did this. No, there’s no direct way to do this without Zapier or akin, or scripting.
It is tricky to the point where the client I did this for eventually migrated away from Coda to Bubble.
Feel free to share more about your use cases (maybe they are valid). But my overall suspicion would be that you probably want something else than Coda if you need to connect it to SQL and anything that resembles a 2-way near-realtime data syncs.
Thanks @Paul_Danyliuk, very useful to hear that from someone who’s very experienced here
Thank you very much @Paul_Danyliuk , it is a shame not to be able to do it from coda.io, in my case I wanted to connect to the FACTURAPLUS 2016 database (the db is acces) so that when the client creates a new invoice in FACTURAPLUS it is added to a coda table and from coda create a workflow with warehouse. I only want to be able to read the facturaplus data but I wanted to do it without using zapier to avoid intermediaries.
Hi @Ximo_Escamilla ,
You can do it with the API -> https://coda.io/developers/apis/v1
I have similar use cases. I’m moving data from one doc to another or sometimes I use Coda tables as input, and use the API to save the data somewhere else.
The API is very well documented and easy to code, both with Javascript or Python.
You can use Google script with trigger (hourly, daily, etc) to get the data from your table and save it to your SQL server. If you need to refresh the data more often then you can use AWS lambda. You can refresh the db every minute.
I used Zapier a year ago for that, but it turned out to be very limited, and pricy. With the API, you have all the flexibility you need.
I am very excited to hear that @Thomas_Pernet, thanks for letting me know it can be done!
I’ll investigate the API’s options ASAP, could you give me any tip for getting it up and running?
here is a basic Google Script to see all your docs
You can copy and paste the script in your own Google account, you can see your docs name and ID. You need a token.
You don’t need any setup, since everything is happening in the Cloud. If you feel more confortable using Python, AWS Lambda is a good choice.
import requests
token = 'XXX'
headers_coda = {'Authorization': 'Bearer {}'.format(token)}
url = 'https://coda.io/apis/v1/docs'
res = requests.get(uri, headers=headers_coda).json()
print(f'Doc ID: {", ".join(c["id"] for c in res["items"])}')
A potential workflow could be the following:
1/ Create a table in Coda with the columns that need to be move to the database.
2/ Add two extra colums, one for the status and a second one with a row ID. This column can accept “to_add”, “added”, “to_update”, “updated”.
It’s a trick to add or update only some rows from Coda’s table. “to_add” is the first value, “Added” is done by the API (when the API moves the data, it updates the status, so that we don’t need to add existing data). The status “to_update” is based on Automation. When a value is changed, then change the status to “to_update”, so that the API can query either new rows or rows to update.
3/ Create a function that queries the rows to add or to update (https://coda.io/developers/apis/v1#operation/listRows)
4/ Move to the database
5/ Update the status in Coda’s table (https://coda.io/developers/apis/v1#operation/updateRow)
6/ Add a trigger (In Google Script, time or event based trigger)
If you need to update more than one Doc, you can create function, and loop each doc.
If you need more details, I can make an example for you.
Well that was a quick answer @Thomas_Pernet thanks
I’ll put my hands on the script and the API’s documentation tomorrow and hopefully I can solve my problem with this, I’ll let you know if I need an example. Thanks for helping!
Thank you very much for everything, I will try it.
Hey @Thomas_Pernet 
I have heen reading the documentation of the Coda API and the scripts from Sheets, ways of Syncing them that other people shared on the community and more… but I can’t figure out how to setup what I need.
Could you give me an example of a one way sync that only copies to Coda.io the rows that hasn’t been already copied?
I will update the google sheet table every minute with new information, but I don’t know how to move the information to coda every minute.
(every minute because it’s supposed to be a realtime view of the database)
Thanks in advance, also if you can exlpain it so you don’t have to do it, it would be okay, I’d feel bad for the waste of time it could be
Hi @SureKT
Here is a spreadsheet
There is a column to indicate the status. Kind of a trick to know what rows to update, or you can add a parameter sheet to indicate the range to update.
In Google Spreadsheet, you can use a code that looks like this (it’s a very simple code)
function copyData() {
//CodaAPI.authenticate('XXX'); Uncomment
var ss = SpreadsheetApp.openById("1kXW5upwZ_sgXXP1suSF_s5y9aIgq47YsuUqaPeZYbh0"); // change Spreadsheet ID
var data = ss.getSheetByName("Sheet1").getRange('A2:F13').getValues() //. Change the range. Can be done in the spreadsheet or by code
// Note, it returns a list of list, so need to loop through the list
var body_item = {
'rows': [
],
};
var sizeArray = data.length;
for (var i = 0; i < sizeArray; i++) {
if (data[i][5] == 'To_update'){ // filter the rows to update based on column 6.
var body_archive = {
'cells': [{
'column': '12345',
'value': data[i][0] // first value of the list
},
{
'column': '12345',
'value': data[i][1] // second value of the list
},
{
'column': '12345',
'value': data[i][2] // third value of the list
},
{
'column': '12345',
'value': data[i][3] // fourth value of the list
},
],
}
body_item['rows'].push(body_archive)
}
}
Logger.log(body_item)
//CodaAPI.upsertRows('DOC ID', 'TABLE ID', body_item);
}
The output looks something like this
[20-08-12 21:08:26:102 CEST] {rows=[{cells=[{value=0.7678706982212772, column=12345}, {column=12345, value=0.4886391651380442}, {value=0.023075644472727808, column=12345}, {value=0.2585414003318711, column=12345}]}, {cells=[{column=12345, value=0.9498433809353624}, {value=0.8420385345291842, column=12345}, {column=12345, value=0.25699186793684914}, {column=12345, value=0.8726593972991112}]}, {cells=[{value=0.16375182948578404, column=12345}, {column=12345, value=0.4536541646065664}, {value=0.9426593744944924, column=12345}, {value=0.2616218417359777, column=12345}]}, {cells=[{value=0.05366056841040445, column=12345}, {column=12345, value=0.4488398461583192}, {value=0.7879987048350671, column=12345}, {column=12345, value=0.8353628763223561}]}, {cells=[{column=12345, value=0.6158953462316646}, {column=12345, value=0.9301338531622705}, {column=12345, value=0.820574421697939}, {column=12345, value=0.7750813810957976}]}]}
and the values will be saved to Coda
You can trigger the function each minute.
A simpler workflow could be to paste the data into Coda directly in order to avoid to store the data in a spreadsheet. In this way, you save the data in two places, DataBase + Coda, not DataBase + SpreadSheet + Coda. If you still need a spreadsheet, you can try to save the data in the spreadsheet, and in Coda directly without the need to get the data from the spreadsheet
Note, there is a propagation delay for changes made via the API to appear on the published doc