I used the Coda script (https://blog.coda.io/how-to-sync-data-between-coda-docs-and-google-sheets-using-google-apps-script-128307ec2ad9) to create an import table in Coda to sync with a Google Sheet. Then, I have another table with reference formula’s to the import table. When the import data table updates, the reference formulas do not unless I go in and manually touch each row in the import table.
Any suggestions on how to fix this?
Thanks, good suggestion.
@Al_Chen_Coda It looks like I have to touch the date field to make it update, but if I do a straight cut-n-paste into the import table it works fine. Here is the forumla from the second table.
[Rev Forecast Import].filter(Month=thisRow.Month AND [Client Type]==thisRow.[Client Type]).[Pipeline (Verbal)].sum()
Hi @Ron_Gerrans, welcome to the Coda community! Could you paste a gif or screenshot of what you mean when you say “touch the date field?” The script simply takes the data from the Google Sheet and writes over the table in Coda that you specify.
I see, my guess is that when the data gets synced over to the Coda table, the data is stored as text (e.g. “March 2021” instead of “3/1/2021”). One workaround is to have another column that uses the ToDate function which converts the text from the sync into a usable date format. Then the reference formulas would reference this new column.
It was definitely showing up as a date in the selector, but that said the change fixed the problem. The date in the txt field included a time value so I’m wondering if that was what was throwing it off. Thanks for the help!!!
Also, quick side question. I moved the script from global to the actual sheet. It seems to work, but wanted to confirm I won’t break anything?
Yeah that time might be throwing the forma off, or you could try changing MonthTxt to a Date and Time format? Moving to the individual sheet shouldn’t break anything. I would just run it a few times to make sure everything syncs over!
That’s what I had done originally that was showing but not carrying to the other table unless I clicked on them. This is working now so I’m good to go. Thanks again for the help
@Al_Chen_Coda I just noticed that I’m having a similar error with the calculation field in the Coda sync table not updating. The problem is that I’m pulling in the data from a dynamic source table. So when January went away in my source table the formula is still showing it in the sync table in Coda even though the MonthTxt field is showing February (it’s the same Coda_Source row in the source table which was originally January).
Can you share some screenshots of the error you are seeing?
No problem. Please see below. This was workng a couple of hours ago and it looks like an update was made to the source data and it’s out of sync again.
Realized it’s only Jan & February that are wrong
Here’s my source table (latest changes haven’t synced yet)
After sync, same issue:
@Al_Chen_Coda I fixed my problem. I added a new column in my source table that converted the date to txt. When that came through Coda recognized it as date so I could use it as my Month column w/o a formula in Coda. I also changed the option to resync the full table each time to deal with the changing rows. Thanks for the help
