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

Ah, I forgot about that step :sweat_smile:

Worked like a charm now.

Many thanks!

Another one just popup in Gsheet to Coda.

TypeError: Cannot read property ‘value’ of undefined (line 288, file “Code”)

Any idea what it is?

Hm not too sure, did you make sure the spelling and capitalization of your columns in your Google Sheet match exactly with the columns in your Coda table?

I found out the problem.

The column names in Gsheet are correct but 2 of them have identical names so when copied to Coda, they automatically changed to “column name 2” and thus cause a discrepancy in syncing.

Fix that and they sync work perfectly fine.

Thank you.

1 Like

Hey guys, is there’s a way to sync data that are filtered on a table? I just want to have the entire data from a table sync to google.

Thank you.

Hi @Marc_Fitt, if you’re trying to sync rows of data from a view to a Google Sheet, you can just use the ID from the view in the script and the filtered data should sync over.

Hi @Al_Chen_Coda , thanks for getting back to me rapidly. I actually want to sync the entire data from a coda table even if we have a filter on it.

Ex: if there’s 1000 rows that contain products and I filter by name and only show 2 products, I still want to sync to google 1000 rows.

I also often get this error attached

Thank you,

Hi @Marc_Fitt, when you provide the ID for the main table that you are trying to sync (and that main table is filtered), you’re saying only the filtered data gets synced to your Google Sheet? I think that error may be a result of two things:

  1. The first row in your Google Sheet does not contain your header columns (row 1 has to be your headers)
  2. The script isn’t finding the Coda source row URLs in your Google Sheet. This may be related to my first question, but perhaps try clearing all the data in your Google Sheet and run the sync fresh to make sure all the source row URLs get copied correctly to your Google Sheet.

@Marc_Fitt I see I think this might be the intended behavior of the listRows endpoint in the API. This endpoint has a query parameter which means the table should be unfiltered and then you would edit the query parameter to filter out any rows you don’t need on the sync. I’ll check internally to see if this is the case. The workaround is to have a page with the unfiltered table which you would sync to Google Sheets.

I noticed you have two scripts in the same Google Apps Script project. I’ve found that this may lead to functions crossing paths so I would create one unique project for each script. When functions are named the same between scripts in the same project, it leads to unexpected behavior.

I’d also recommend removing the Loom video from your post since your API key is visible in the video :slight_smile:

Good point for the Api. I thought combining all my scripts for the same purpose would be better so they are all triggered at the same time. I run the same scripts for multiple tables on the same doc to back up them and use Google sheet as a bigger database.

Another quick question:
If I use the formula to prevent deleting rows on google sheet, will it add the same rows over and over again or update the existing row created the first time in google sheet?

Thank you,

There is a visibleOnly flag in the API which should allow you to get all the data from the table regardless if the table is filtered. Looking into this some more and will report back once there’s an answer!

For the formula to prevent deleting rows, which formula are you referring to specifically?

Hi @Al_Chen_Coda, I got these errors when running Coda -> Gsheet sync.

TypeError: Cannot read property ‘toUpperCase’ of undefined (line 240, file “Code”)

Or sometimes

Exception: The starting column of the range is too small. (line 95, file “Code”)

Any idea what are they?

Hi @Tu_Bui I think you may be using an older version of the script since I removed the toUpperCase function from the script. This is the latest Coda->GSheets script, mind trying this version and see if you’re still running into errors?

@Al_Chen_Coda awesome let me know because it seems it only take the filtered table atm.

As for the deleting rows, I mean in google sheet to keep the row no matter what and juste update it if value changes. If the rows get deleted in Coda, it keep the row in google shit as archive.

Thanks,

@Marc_Fitt I see the way the script is set up, the Google Sheet will always stay in sync with your Coda table (so if you delete a row in Coda, it will also get deleted in Google Sheets). One workaround is to just delete the “Coda Source Row UR” column in your Google Sheet and I believe that should prevent the script from deleting anything on your Google Sheet.

@Al_Chen_Coda okay but will it update the row or create a new row every time if I delete the code source row ur?

Thanks,

hi there,

No longer see the UpperCase error but still getting:
The starting column of the range is too small. (line 38, file “Code”)

The data still seems to sync though.

@Marc_Fitt Ah not it won’t. If you’re comfortable editing the script, you could just delete the parts of the script that relate to deleting rows. Then the script will only update and add new rows to Google Sheets, but won’t delete anything.

@Tu_Bui this might be because your don’t have a source row URL column your Google Sheet. The column name you have for the source row URL (spelling and case) have to match exactly what you put in the script for the TARGET_SHEET_SOURCE_ROW_COLUMN variable.

1 Like

It worked flawlessly now. I thought we do not need that column for sync from coda to gsheet so I didn’t input.

Thanks a lot!

1 Like