Live data from google sheets to Coda.io

We have some very extensive google sheets document to run our business (planning, budget, …)
I can’t convert them all to coda at this time (because of some functionality, but also time to do this)

But I want to build some coda docs that use certain values from these google sheet documents.

Is there a way to import certain cells from google sheets in Coda
preferably constantly, or if needed after a certain trigger (push of a button)

I don’t need to add rows or so, just updating a few values

I see two options:

  1. Use an automation platform like Zapier or Integromat and set up an “If new data in Sheets, put into Coda” integration. It will automatically check for changes every 5-15 minutes. Most likely will be paid, although if your needs are simple, maybe free tier will be enough.

  2. Set up a Google Apps Script, and trigger it on demand from your Sheets / maybe it can also be triggered by changes, but I’m not sure. Will require some coding, but won’t incur any extra costs.

1 Like

Yep, it can be event driven.

I would lean toward a solution that uses Google Apps Script because (a) I’m extremely biased, (b) it’s free (you already pay for G-Suite), and ( c ) you used the word “extensive” in your question.

(a) is an indicator; I have this bias because I tend to repeat choices that actually work. :wink:

(b) @Paul_Danyliuk is right about the cost, but it also increases the attack services on your data (i.e., increased security risks).

(c ) If the data is extensive, no-code integrations can be complicated and inefficient. They’re great for simple integrations, but tend to struggle with complex data sets.

This requirement suggests you need some business logic applied before sending the data to Coda and such logic is probably best performed in the same system where your data lives.

Well, I’m extremely biased (lately) towards advocating for not writing code :slight_smile:

Especially to people who specifically turn to no-code tools like Coda.

I haven’t seen the document, but I have a feeling that spinning up a zap for “importing certain cells” and “just updating a few values” would be a matter of 5-15 minutes, unlike having to educate oneself about google apps scripts or alike.

1 Like

That’s fine - we all have biases (and budgets, and security rules, and complex business logic in some case). Some data can freely move about the planet without fear of being valuable to hackers. And some organizations are not concerned about security. And many users have no trepidation about maintenance and support of black-box transformations in third-party systems.

But making any decisions purely on the basis of writing or not writing code [sometimes] fails to factor the comprehensive requirements. This is why I was attempting to glean important details in @Pieter_Lesage’s intimations.

I have a vast client base who use scripts (partly because I’m biased, of course), but these clients also use Zapier (a lot). And the data is clear - codeless integrations - especially the more complex ones - are far more likely to create issues or introduce constraints. If this were not the case, I would not find myself writing scripts often to replace Zaps.

I like the Zapier and Integromat offerings - they are very useful in many cases; just not all cases.

Sidebar - @BenLee - your marketing team should consider inviting @Paul_Danyliuk and me to a webinar fashioned like SNL’s Point-Counterpoint so we can debate the benefits of code/no-code integration strategies. :wink:

2 Likes

I’ll bet you’re right and you should do that for @Pieter_Lesage to demonstrate this pathway.

I will gladly do so if I’m hired to do so, @Bill_French :slight_smile:

I do not mind pro bono work in the community (actually love it), but only if it benefits the community as a whole, not just a specific case for a specific individual.

In fact, this is exactly how I pick the topics I reply to. If the problem is common, or I can give some guidance that can apply to many common use cases, or offer an unusual approach (which is also universal), then I’ll most likely chime in. Or if I see a beginner struggling with something basic like Filters, and I know I can quickly guide that person towards the right path, I’ll also answer.

2 Likes

Yep - I get that - our insights are freely available to a point. And that point is when we must pay our bills. 15 minutes; 15 hours - it’s still pulling from the finite inventory known as the rest of your life.

Thank you @Paul_Danyliuk and @Bill_French for answering my question.

First of all, I’m nog looking for pro bono work, I’m in consulting business as well, so I understand what’s advice and what is implementation.

I have no bias for Code or No Code solutions :slight_smile: , but my coding skills are basic.

A bit more info about my setup.

In my google sheets the data I want to import in Coda are complex calculations (for example the number of not planned days for a certain person in a planning document).

Does Zapier or integromat (or google app script for that matter) see a new calculated value as a trigger? since the cell (the formula) in itself does not change.

Can you link to a cell in google sheets based on a value of an other cell (like you do with Vlookup and sumif calculation in google sheets, or lookup in Coda). I would love a lookup functionality from coda to a google sheet.

Thanks for the added clarity Pieter.

There are some key distinctions worth noting related to your questions and specifically with regard to events.

  • Coda doesn’t [yet] provide us with events about changes to data fields, rows, or tables. Typically, events are combined with webhooks (another missing feature) that allow other systems to be notified based on changes of any type.

  • Since Coda doesn’t support events, all other systems integrated with Coda are at an equal disadvantage with one exception - Slack (more about this later). If external systems have no way to be informed about a change, they cannot be [automatically] responsive to things that happen in your Coda data.

  • All three platforms you mentioned must “poll” for changes in Coda; typically minute-by-minute They simulate change events by tracking previous values with each new poll request. This is very inefficient, but for the most part, it gets the job done and if by “getting the job done” means pummeling Coda’s servers 1,440 times a day for one change.

Sidebar - one way to improve Coda’s performance is to simply enable events and outgoing webhooks.

  • Unfortunately, polling processes need business logic to be effective; they need to know the definition of the “change” you want to detect.

  • As you readily make clear, the change of a specific cell value is a requirement, but this type of granular logic is not possible in Integromat and Zapier without adding this logic to these platform configurations using script (as far as I know). @Paul_Danyliuk can probably confirm this. Google Apps Script is no different, it must use script and the Coda API to assess change and then act.

Armed with a clear understanding of these constraints…

If you are referring to a new calculated value in Coda, the answer is no.

If you mean link to a cell in Google Sheets based on a value of an other cell in Coda, the answer is also no.

Indeed. This would be ideal because it would give us the ability to perform some very complex logic external to Coda docs. Furthermore - and this is perhaps as important - we could seamlessly integrate live data from the many legacy systems in Sheets, Excel, etc.

Google Sheets -> Coda Tables

With some of my clients, I overcome this limitation with Google Apps Script by defining business logic that leverages Google Sheets’ events. When a cell in a Google sheet changes, a script is triggered, and using the Coda API, I update the cell (or cells and rows) in Coda that are impacted as a result of the Google Sheets changes.

HTTP GET and POST Support

In addition to events and incoming webhooks, Coda is missing what I refer to as the third leg of the three pillars of integration - HTTP POST (and GET). Typically, webhook architectures support both incoming and outgoing webhooks (Slack is a good example) where outgoing webhooks perform an HTTP POST.

But we must be precise when discussing these concepts. Outgoing webhooks that use HTTP POST to send data and messages from Coda to other systems is quite different than features which enable arbitrary HTTP POST actions. The former (webhooks) are generally connected to events, whereas the latter is simply an action that might be invoked by a user clicking a button (for example).

The Coda Slack Pack currently invokes an outgoing webhook, but such capability is presently not surfaced through the Coda API. This is why I have lobbied for access to the primitive methods that the Slack Pack obviously takes advantage of. Indeed, outgoing webhooks exist in Coda - they’re simply not yet exposed. My plea - Pack development should be open and I suspect that is planned.

Integration Desperation

In some cases, I have been so desperate for events that I use the Slack Pack to trigger messages about row changes. Then I use the Slack outgoing webhooks to inform Google Apps Script that something has changed. It gets the job done but at great complexity.

There have been numerous hacks posted in the forum - this one in particular - but these still lack events and thus, require humans to do stuff.

Ideally, a cell in Coda should be able to (a) sense that a change has occurred, and (b) make an HTTP POST to another service all behind the scenes and quietly. This would allow us to build real-time update processes into other systems that support incoming webhooks and update their data accordingly. A few examples -

  • A Coda cell changes and a comparable cell in a Google sheet also needs to be updated.
  • A Coda cell changes and a process in Google Apps Script needs to be started.

But back to your original need - we can rephrase this slightly and achieve your objectives.

I would love a cell in my Google Sheet to update a cell in Coda when it changes in my Google Sheet.

This is possible now using Google Apps Script and events that detect when changes occur in Google Sheets.

I hope this helps and I urge other Codans to validate my assessment because I do get it wrong from time-to-time.

At this point, I only need data from Google Sheets to go to Coda

I’m still at the phase of trying to figure out a setup that would be workable
I’ll do some tests later to determine what works best for me, but thank you for the info.

Yep - that’s what I thought. You might try prototyping your first crack at it via Zapier. Coda is very flaxible in that you can create an integration into a “triage” table in a section that’s not mainstream in your document and then use lookups and formulas to draw key data values into more visible tables and content.

Hi Pieter, just wanted to chime in (I head Growth at Integromat) and let you know that Integromat offers a pretty extensive set of integrations Google Sheets and data wrangling is really easy with Integromat’s excel-like functions. More info on the available integrations here: https://www.integromat.com/en/integrations/google-sheets

We also have a thriving community of integration experts where you can post your requirements and get ideas/help pretty fast: intm.at/community

Happy to answer any other questions about Integromat!

1 Like

Nice hustle @Arpit_C :slight_smile:

Yeah, Integromat looks more powerful with Sheets than Zapier. I see it supports cell-level edits and formulas, and it’s instant — I’m not sure Zapier can do that (although I never tried either with Sheets). Very helpful to know!

2 Likes

Yes, completely true based on my limited experience with both services.

Thanks @Paul_Danyliuk! Glad you find this helpful! :raised_hands:

P.S. Why won’t you make a Discourse community like this one? Facebook communities kinda suck. It’s still a timeline, not the best choice for a knowledge base. Something like a forum would be much more convenient to browse.

1 Like

… and some firms don’t let workers use Facebook to engage in business activities.

Hey @Paul_Danyliuk, thanks for your suggestion. We actually had a Discourse community before we moved to Facebook. The amount if activity was very low while moving to Facebook helped us shoot that through the roof. Moreover, Facebook actually makes it easier for people to engage with posts and discover new ideas. That said, I know there are many drawbacks to a FB community and a forum definitely has its benefits.

Do check out our community if you haven’t already and you will see what I’m talking about! :slight_smile: