How to sync data from Coda to Google Sheets and Google Sheets to Coda using Google Apps Script

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:

7 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!