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

69

@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.

1 Like