How to access BigQuery generally from Google Apps Script

0
votes

I have a Google Apps Script application that currently accesses a GCP SQL database with JDBC.

Using the normal SQL database doesn't cut it, so I decided to try BigQuery.

Is there a way to access BigQuery from Google Apps Script without connecting with an account connected to the GCP project? I want guests who use my script to be able to get data.

I'm looking for either a general way (as in: IP, database, username, password and I manage the connection) or a client library way I can use with Apps Script.

Note, the BigQuery Apps Script plugin seem to only allow access to my own databases, so guests will get denied access.

1
google-apps-scriptgoogle-bigquery
"Using the normal SQL database doesn't cut it" Would you mind expounding on that? What was it about Google Cloud SQL that made it ill-suited for your purposes? - TheAddonDepot
@DimuDesigns I'm new to this project, and the current employees told me that the current database doesn't cut it. We are talking about roughly 800GB in storage, 60 active connections on average, 300KB/s ingress and 125KB/s egress. - Amit
BigQuery is a good database for analytics - from the question I'm not sure if you're looking for an Analytical datastore or a transactional one. - Felipe Hoffa
@FelipeHoffa We are using the current database for analytics, specifically, Google Data Studio. I do understand the concern though. - Amit
@Amit Given those details I suspect they're using a 1st generation cloud sql instance. A 2nd generation instance can easily handle those specs. cloud.google.com/sql/docs/quotas - TheAddonDepot

1 Answers

0
votes

As per this link , you can share your BigQuery dataset with specific users even if they're not part of the GCP project. Since they need to be able to retrieve data, they will need "Viewer" permission on the dataset. The steps to do this are described in here and this link will show you with an example how you can query your BigQuery table from Apps Script.