CODEWITHSUNDEEP
google-apps-scriptgoogle-bigquery
Wesley Batista
Wesley Batista

Reputation: 87

Is there a way to build an Google Apps Script application that uses BigQuery Service as a service account instead as a user?

What I trying to do here is an application that connects to bigquery, executes some query and writes a spreadsheet in current user's Google Drive.

I know that I can change the "Execute app as" option when deploying the app. But when I do this, and the user try to use the app, Google asks for permission to access the BigQuery.

I do not want to give permission to each user to access BigQuery. We have about 50 ~ 100 users who will use (and this number can increase)

Some images:

Hope I've been clear.

Upvotes: 4

Views: 1293

Answers (3)

Maksim Luzik
Maksim Luzik

Reputation: 6733

Yes, that is possible. Check out this answer here: Using Advanced Google Services with Service Account and also check this awesome library: https://github.com/googlesamples/apps-script-oauth2

Upvotes: 0

Serge insas
Serge insas

Reputation: 46802

One solution that you can use if you don't want all your users to enable BigQuery is to use a webapp deployed as a service running as "you" and that will respond to an urlFetch coming from each user through the script they run as "themselves". This service will be deployed as any other webapp using a doGet main function and return its data using contentService

Upvotes: 0

Alan Wells
Alan Wells

Reputation: 31310

You would probably need an App, that had people sign in with their Google account using oAuth2

Google Accounts Authentication and Authorization

I think that this link gives an overview of what your options are:

Permissions and Types of Scripts

I guess you would need to have an App, that ran as User at the keyboard, so that the spreadsheet saved to their Google Drive. If you wanted the user to be able to download a file to their computer drive, that's a little different.

Just use HTML Service. It's basically just building a website. You just don't have a domain name. But the spreadsheet you are distributing doesn't have a domain name.

HTML Service Google Documentation

From an Apps Script HTML Service, you can write to Spreadsheets, Documents, and run HTTP Requests (urlFetch).

URL Fetch Service

The permissions in the Apps Script App can be set to run the Apps Script file as yourself, with access to anyone, even anonymous. That way, the user will never be asked for permission for any files that you own. You will need to authorize it once, the first time any script that needs access to your files is run.

Because anyone could run your Apps Script App, you might want to have a log in system to authenticate users.

You would need to use the Big Query API with Apps Script urlFetch to access Big Query.

So, with the HTML Service, you can do most everything you could do with HTML, CSS, and Javascript on a regular website. You can create custom input forms, have different pages and display data in tables. Plus it's served over HTTPS.

Upvotes: 1

Related Questions