A few people have requested the ability to sync data between Coda and Google Sheets, so I wrote two scripts in Google Apps Script to help you accomplish this. I published a doc with the two scripts along with a tutorial and how the two scripts work. Note: I tested these scripts out a few times but there are most likely edge cases that I haven’t tested, so please be aware the scripts can break at any time. If you see any bugs or issues please let me know (or report in GitHub)! Some other resources:
12 Likes
Thank you so much @Al_Chen_Coda! Already have a simple test up and running and about to do a deep dive.
1 Like
@Al_Chen_Coda so far so good — but running into an issue with dates.
We have our test Coda doc timezone set to Tokyo, but we’re getting a different date in Sheets:
Original Coda Date: 5/27/2020 8:30 AM
Import Sheets Date: 2020-05-26T16:30:00.000-07:00
Would also love to get this date formatted into a workable Google Sheets date, as well, if anyone has any ideas!
Thinking to try tweaking a formula like the one mentioned here:
https://support.google.com/docs/thread/12562633?hl=en
Thanks again @Al_Chen_Coda! This is going to be a huge help.
1 Like
Could make Coda and Gsheet more easily ,more speed ,more efficient, less time to sync data ?
2 Steps:
1. input Gsheet api key
2. Get Gsheet datas into Coda table.
@Michael_Warren you should be able to use the native Google Sheets date formats to get the date format you need. It looks like the format of the date is the Zulu format so you Sheets should be able to detect the time. In terms of getting Tokyo time in Google Sheets, unfortunately time zones you set in a Coda doc don’t carry over to Google Sheets with the script. My recommendation is create a new column that converts the raw date synced from Coda and adds/deletes the number of hours to get to Tokyo time.
1 Like
@Al_Chen_Coda Have had a good bit of success with the script so far! But after trying it on an older/bigger doc, have just run into this error:
Exception: Request failed for https://coda.io returned code 404. Truncated server response: {“statusCode”:404,“statusMessage”:“Not Found”,“message”:“Could not find a table with the specified ID or name.”} (use muteHttpExceptions option to examine full response) (line 267, file “Coda”)
Has anyone else seen this error or something similar to it?
I tried a few different tables in the same doc, but kept getting the same error.
Would generating a new API token do anything?
@Michael_Warren I haven’t seen this error before, but my guess is the table ID you put into the SOURCE_TABLE_ID might be incorrect (if you are using the Coda->Sheets script). Here’s the part of the video tutorial where I describe how to get the table ID. The other potential issue is that you have the correct table ID but perhaps the wrong Doc ID. Would just double-check that as well!
Thank you again, @Al_Chen_Coda!
I realized my problem was that I’d copied a View ID instead of the source Table ID.
However, I’ve now run into a different error because it looks like our table is too big!
Exception: Request failed for https://coda.io returned code 422. Truncated server response: {“statusCode”:422,“statusMessage”:“Unprocessable Entity”,“message”:"Sorry, this doc has 155% of the maximum content size to be accessible via the C… (use muteHttpExceptions option to examine full response) (line 267, file “Coda”)
We’re only 55% over the limit — is there any chance this will be expanded in the future? Or perhaps a way to extract a filtered section of the data (a view, for example) for use with the script?
1 Like
@Michael_Warren there are indeed some limitations to the amount of data you can pass through the API (help article here). You can definitely get data from a view vs. a table so that you’re not syncing unnecessary data to Google Sheets. Here are a few edits you would need to make to your script:
-
Line 203 - Change
getTable to getView
-
Line 206 - Change
listColumns to listViewColumns
-
Line 209 - Change
listRows to listViewRows
As you discovered, the View ID is different from the Table ID. Whatever value you have for View ID you can keep the same in the SOURCE_TABLE_ID variable in Line 11. Hope this helps!
@Al_Chen_Coda, I am not sure if this little typo is on purpose or not, but it took me a while to see that something was wrong after I pasted this part.
2 Likes
Thanks for the catch @Christiaan_Huizer, you’re referring to the Coda API starter guide right? I just fixed the typo.
Hi @Al_Chen_Coda 
It’s works fine, as long as you only have one table to sync.
How can I sync 2 tables from the same google sheet? Adding a new project in script does not work
Many thanks
Thomas
Did you create a brand project in Google Apps Script or a script in the same project where your first script is located? I’ve noticed that having multiple scripts in the same project doesn’t work since there are duplicate function names. So you have to create a new project with just a single script and you should be able to sync multiple tables into/from Google Sheets.
1 Like
Thanks @Al_Chen_Coda need to check and will get back to you
@Thomas_Schulz,
There is something else one should beware of and that concerns the amount of tables active in a doc. I was working on a business case and had already processed a bit of data when I added a new table to link to the API. That did not work well, the script could not find the ID of the Table, too many tables. My solution was to create a new doc with only the tables I needed and once ready I’ll link them via a cross doc to my business case.
Did this “too many tables” error come up in the logs in Google Apps Script? I haven’t come across this error in the Coda API, so curious how you came across it.
hi @Al_Chen_Coda, it simply states this:
did you notice this before? As indicated I continue in seperate docs dedciated to the API I am working on.
1 Like
Ah yes. This happens in Google Apps Script when you are sending too much data to the log. You might get lucky and see your table in the truncated response but this is not a scalable way to find table IDs (especially as you add more tables to your doc). That’s why in the walkthrough video I recommend turning on developer mode in your account settings so you can see the table ID directly in your doc.
1 Like
I looked over this one ,many thanks for resharing. This is so much easier!
2 Likes
