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

[Cong_Nguyen]

Hi Chen
Thanks a lot for your solution. It’s very helpful!
Is there any way to not sync the TARGET_SHEET_SOURCE_ROW_COLUMN?
Thanks

[Al_Chen_Coda]

That’s strange…deleting that line or commenting it out should have the same effect (should break the script). When you say it “broke” your Google Sheet, what do you mean exactly?

[Al_Chen_Coda]

Unfortunately you need to have the TARGET_SHEET_SOURCE_ROW_COLUMN in the Google Sheet. The way the script is written requires that column in order to know which rows need to be updated whenever you run the sync again. You could alter the script so that it just deletes everything in the Google Sheet every time it runs and then sync the data over from Coda. It wouldn’t be very efficient if you have a large dataset, but it’s one way of doing it!

[agoodman]

A quick update but I am hoping to get back to this work today. My spreadsheet looks like this with your code:

When I remove your code, I see (ignore the top row, that’s a “feature” of the script I’m using to pull in the other sheets/tabs):

[Al_Chen_Coda]

I see. I’m not quite sure what the issue is without seeing the Google Sheet and/or the Coda doc. So the 2nd screenshot looks likes it syncing values over but is including the column titles too?

[agoodman]

I tentatively can share that creating a new workbook and importing range from my first workbook’s Dashboard sheet then connecting it to Coda is working! I now need to go back in here and do a full clean up on an actual working demo for my colleague but it looks like I may got this!

Thank you so much.

[agoodman]

@Al_Chen_Coda I have run into the issue where the Coda Source Row URL isn’t being written into Google Sheets. I checked and I have the exact same header name on each side. I have a suspicion that this is because I’m using IMPORTRANGE() in this second workbook that connects to Coda. Does that sound right to you?

The data order then is:

1. New JotForm is made and synced to an existing Google Sheet.
2. A script writes a new row to a “catch-all” sheet/tab whenever a new sheet/tab is made (from the JotForm sync). This is within the same workbook, so you have: “catch-all tab” sheet, sheet2, sheet3, etc.
3. This catch-all is then a list of all sheets/tabs within that workbook.
4. If I try to add the Coda Sync at this point, the script that calls in all those new sheets/tabs breaks because “Spreadsheet = SpreadsheetApp.openById(SOURCE_SHEET_ID);” appears to break the script that pulls data from the other sheets into my “catch-all” sheet.
5. I then created a new workbook which does an IMPORTRANGE from the first workbook’s catch-all sheet into this new workbook. I can then sync from this point into Coda.

What I run into next then is that Coda is sometimes displaying 2-3 copies of a single row. I suspect this is because of the lack of a Coda Source Row URL being written into my second workbook (the one that actually connects to Coda). What I’m doing now is to use a filter formula from someone else: thisTable.Filter([Events]=thisRow.[Events]).First()=thisRow

I also noticed that my column types are changing. I set the date column as such and set it to show me only MM/DD/YYYY. Upon the next sync, it goes back to (what I assume is) full UTC time. Checking the others, ALL the column types get changed back to text.

[Al_Chen_Coda]

@agoodman Things could definitely break if you are using IMPORTRANGE in Google Sheets so I would avoid using that formula if you can to make the script work!

[agoodman]

Thanks! I’ll see if there’s anything else I can do. I can report in that it exploded over the weekend. Instead of having 5 rows, I had over 6,000. I’ve broken the connection with the helper spreadsheet (that used ImportRange) to stop it.

[Joel_Santisteban]

@Al_Chen_Coda first of all, thank you so much. The Coda → GSheet script has been incredibly helpful for my team.

We have recently expanded the use case internally to pull data from a larger data table in Coda (125 rows X 160 columns). I am now running into an odd issue where the script:

a) skips the first column (A1) of data - I have checked the column headers are matching
b) stops pulling data after column 101 or CW1 - everything starting from CW1 to FD1 is completely blank. The last column is the source row ID column and this is populated correctly.

Any ideas why these things might be happening? The table does not seem to be above 125 MB. Is 160 columns too many?

Any insight would be wonderful

Thank you

---

EDIT - following up on my questions for those curious. from the testing I have conducted it appears 100 columns of data is the maximum I can pull. Reducing Coda columns to this number results in fully populated data and also resolves the missing first column issue. thanks!

[Al_Chen_Coda]

Hi @Joel_Santisteban thanks for posting your question! I didn’t know there was a 100 column limit for the script, but glad you found it out!

[Minh-Tran_BUI]

Hello @Al_Chen_Coda !
Thanks a lot for your great scripts and for sharing all of this!
I have tried the one to sync data from a spreadsheet to Coda, it does work and I don’t have any error messages when it’s triggered.
However, it seems that some lines in coda get overwritten in a pretty hectic way (sometimes, I will see duplicates, sometimes one line will be overwritten by another and one line will be missing).
The value of REWRITE_CODA_TABLE is false. I tried with true which actually solved the issue but I actually need to trigger email notifications after once a new row is created so I can’t actually use it.
Have you seen such a situation?

Thanks a lot!

[Al_Chen_Coda]

I’ve seen this happen if you try syncing too often (syncs that happen within a few seconds of each other). If the time between sync is too short, Coda may not be “snapshotting” the existing data that has been synced. This means that the next time you sync the script may not see the most updated data in Coda. I would lengthen the amount of time in between syncs to ensure data is getting snapshotted properly to Coda.

[Minh-Tran_BUI]

Got it! I was indeed testing pretty intensively. Thanks!

[Lucas_Sanchez]

We need a Pack the simplify this! Is in the roadmap?

[Paul_Danyliuk]

I’m working on it. It’s super early and there’s no commitment to any date (if I’m funded from the Maker Fund it’s gonna happen sooner ) but I’m working on it.

[Al_Chen_Coda]

Awesome! Hoping the community doesn’t have to rely on Google Apps Script in the future to sync their data in and out of Sheets.