I want to periodically bring in summarized data from Google BigQuery to Coda tables so I can do fun stuff with it.
I already found I can bring the data in through Zapier, but they want to charge me an entire task for every row brought into Coda, which is insane! They charge more based on how many tasks you make, so this would eat through my 2500 tasks/month quota in a flash.
So, I’m wondering if I can make those API calls from a Coda Pack rather than through Zapier.
My coding skills are very limited, but I’d be willing to try. However, I know that sometimes there are blockers to creating certain types of Coda packs (for instance, there is no generic “call a webhook” pack for some reason around having to declare outgoing URLs in advance). Would there be any such blocker to creating a BigQuery pack, where I can pass an arbitrary query and get a set of rows in response?
I actually think this could be super useful, since you could move data in and out of a proper data warehouse from Coda.
Hi @Alberto_Delgado - I’m glad to hear you are interested in building Packs! I looked into BigQuery more, and it is possible to build a Pack for it. In fact, I got a crude version working this weekend:
Getting Google to approve a Pack for general use is a long journey, but I can add you as a beta tester if you are interested. That will allow you to use the Pack, but you’ll have to sign-in again each week. If you are interested, DM me the email address of your Coda account.
@Eric_Koleda for what it’s worth a BigQuery pack would entice an upgrade to the team plan. To get BQ data into Coda today is often jumps through Zapier or Integromat, adding complexity, fragility, and cost. A really nice pack to unlock with the higher tier.
@Eric_Koleda 's beta pack is already working great for querying.
A few thoughts on future development:
A formula to retrieve details from BigQuery would be fantastic. I’m picturing something like BigQueryFetch(dataset, table, search_column, search_term, column_to_retrieve). With something like this, I would be able to consult certain information (e.g. use a client’s ID to get their details) without leaving Coda.
A button to send information to BigQuery. That way I could use Coda to push data into a database. I’m picturing using BigQuery to archive old data, or to add daily records to a database, that kind of thing. Something like BigQueryPush(dataset, table, column, data, column, data... column, data)
I’m wondering what kinds of things should a BigQuery pack do for other users? I’m sure there are useful cases that haven’t come up. A data warehouse is a powerful thing!
Unfortunately the prototype was rejected by Google. In order to publish the Pack as-is I needed Google to approve my use of OAuth, and they don’t allow developers to request access to a public user’s Google Cloud resources.
Going forward there are a few options:
I publish the Pack, but in order to use it you need to grant my service account read access to your BigQuery data (too risky I assume?).
I publish the source code and you deploy your own copy, using a set of OAuth credentials you generate for your account.
I publish the Pack, but it requires that you use the Enterprise-only feature Pack controls to swap in your own OAuth credentials.
Which if any of those would y’all be most likely to use?
That’s too bad Eric, but it seems there’s still a way forward. Here’s what I think of the options you provided:
I publish the Pack, but in order to use it you need to grant my service account read access to your BigQuery data (too risky I assume?).
Maybe I’d trust you with my own data, but I can’t make that decision for my clients. Also, you might not support the project forever, so it’s not sustainable.
I publish the source code and you deploy your own copy, using a set of OAuth credentials you generate for your account.
I like this solution:
a) It works because you have to be tech-savvy if you’re going to be working through BigQuery, so downloading the code from GitHub and creating a pack shouldn’t be a problem.
b) A step-by-step guide to do this would make it more accessible for people who’ve never made a pack.
c) This way people can collaborate to make the pack better!
The only con is that the pack wouldn’t be available through the gallery, so discovery would be limited.
I publish the Pack, but it requires that you use the Enterprise-only feature Pack controls to swap in your own OAuth credentials.
Good for discovery, but it limits access to a small subset of users (and I am left out). It could be reasonable since it’s mostly a power-user feature, but there’s also lost of use cases (such as pulling in public data) that non-enterprise users might be interested in.
2 and 3 are not mutually exclusive though.
The pack could be open-source and published under option 3.
The pack description could say “if you want to use this pack and you don’t have an Enterprise subscription, follow these steps to create your own version of the pack”
If people choose to collaborate to make the pack better, improvements made in the open project can flow to users of the enterprise pack.
Thanks for the detailed reply @Alberto_Delgado! If you were to deploy a private copy of my code, would your preference be:
The Pack uses OAuth2 to authorize access, so everyone that sets up the Pack would need access to BigQuery and the queries would run under their identity.
The Pack uses a service account with no per-user authorization. Anyone in your org could install and use the Pack and it would have whatever access the service account has.
Thanks for the detailed reply @Alberto_Delgado! If you were to deploy a private copy of my code, would your preference be:
The Pack uses OAuth2 to authorize access, so everyone that sets up the Pack would need access to BigQuery and the queries would run under their identity.
The Pack uses a service account with no per-user authorization. Anyone in your org could install and use the Pack and it would have whatever access the service account has.
Thank you for working on this!
I can see the merits of both options, but OAuth2 seems simpler and maybe more versatile.
I’m not sure I understand the full implications of each alternative though.
Hi Eric - I was so excited to see your reply come through!
I’d love to have access also if your are willing to publish and share. Both of the authentication options you noted make sense to me, and I don’t have a strong preference of one vs the other. I presume option 2 would just require limiting access at the doc level which would work for my needs.
The setup instructions are fairly involved, but hopefully the README can guide you through the steps. Option #2 would certainly be easier to use, but I wasn’t comfortable with a single service account gaining access to the data of multiple users, and the potential risk of data getting into the wrong hands.
