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

Christiaan_Huizer · June 10, 2020, 11:55am

I noticed two kind of issues I cannot solve:

first this URL does not work : https://api.nasa.gov/api.html#imagery , but this is not so important to me.

second : this part creates an error and I do not understand why. I tried various alternatives, but none of them works , it concerns part 6.

var nasaResponse = JSON.parse(UrlFetchApp.fetch(nasaUrl).getContentText()); 
     return nasaResponse.url;

}

The error is : SyntaxError: Illegal return statement (line 58, file "Demo_Coda.gs")Dismiss

hope that @Al_Chen_Coda has a suggestion to make it work.

Al_Chen_Coda · June 11, 2020, 11:20pm

Thanks for the catch @Christiaan_Huizer, it looks like NASA changed the API since this guide was created. I updated the guide to include the proper link and updated the scripts. Here are the following changes that should be made to the script:

  var nasaUrl = 'https://api.nasa.gov/planetary/earth/assets/?api_key=DEMO_KEY&dim=0.1' + 
    "&lat=" + cityLat +
    "&lon=" + cityLong;

and

var nasaResponse = JSON.parse(UrlFetchApp.fetch(nasaUrl));

Thomas_Schulz · June 18, 2020, 4:00pm

The google sync sometimes overwrite the coda table column type, e.g. a date field from Google shows as List. Any suggestions or formatting to be done in Google to prevent this?

Al_Chen_Coda · June 18, 2020, 4:20pm

The first time the sync runs this may change the column type in Coda. After you set the column type to the format you want, future syncs from Google to Coda shouldn’t change the format.

Tu_Bui · July 30, 2020, 4:02pm

I got a problem that says:

Exception: Request failed for https://coda.io returned code 404. Truncated server response: {“statusCode”:404,“statusMessage”:“Not Found”,“message”:“Doc does not exist or is inaccessible.”} (use muteHttpExceptions option to examine full response) (line 409, file “Coda”)

All the settings seem fine and I did double check:

Source doc ID is correct, tried both with _ and without _
Source Table ID is correct, original table, not view
Target sheet ID is correct as well
Target worksheet name is correct (originally the sheet was locked from editing, I unlocked but still the same issue)
Target sheet source row column is correct, I renamed the column that way, but it is correct

Any idea?

Al_Chen_Coda · July 30, 2020, 9:02pm

Hi @Tu_Bui, you should remove _d from the SOURCE_DOC_ID (so the value for this variable should be “WN…”

Tu_Bui · July 31, 2020, 1:56am

Thank you, Al, it works for the Coda to Gsheet part.

Then I start the Gsheet to Coda sync and encountered another error:

ReferenceError: CodaAPI is not defined (line 9, file “Code”)

Which indicates an error with the API key? I generated a new API token and click the button to copy the key here, not sure why it does not work? I tried deleting the old token and created (several) new ones but again, not working.

The one in Coda to Gsheet is still working.

Al_Chen_Coda · July 31, 2020, 2:14am

@Tu_Bui that might mean you didn’t load the library for the Coda API. If you created a new project in Google Apps Script, you may have to add in the library again.

Tu_Bui · July 31, 2020, 2:23am

Ah, I forgot about that step

Worked like a charm now.

Many thanks!

Tu_Bui · July 31, 2020, 2:31am

Another one just popup in Gsheet to Coda.

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

Any idea what it is?

Al_Chen_Coda · July 31, 2020, 3:05am

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?

Tu_Bui · July 31, 2020, 4:00am

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.

Marc_Fitt · July 31, 2020, 9:04pm

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.

Al_Chen_Coda · August 1, 2020, 2:48am

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.

Marc_Fitt · August 1, 2020, 1:52pm

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,

Al_Chen_Coda · August 1, 2020, 2:20pm

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:

The first row in your Google Sheet does not contain your header columns (row 1 has to be your headers)
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.

Al_Chen_Coda · August 1, 2020, 5:01pm

@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

Marc_Fitt · August 3, 2020, 12:25pm

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,

Al_Chen_Coda · August 3, 2020, 5:46pm

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?

Tu_Bui · August 6, 2020, 4:38am

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?

Topic		Replies	Views
Can I sync data from a Coda table to a Google spreadsheet?	16	2238	May 5, 2020
Coda to Google Sheets Sync using a script	3	517	October 12, 2022
Issue coping data from Coda to Google Sheets with App Script	4	95	August 19, 2024
Syncing Data from Excel to Coda Table	8	724	November 1, 2022
Google Sheet - Coda Sync	4	561	July 8, 2022

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

Related topics