4

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.

Wesley Batista
  • 77
  • 5

3 Answers3

1

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.

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
  • Thanks for the quickly response @Sandy! But I think that we are missing a important point here: I need write the spreadsheet in the user's Drive. If I leave the option as "Execute the app as **me**" the spreadsheets will be written in my account, right? This is not what I want. – Wesley Batista Jul 23 '14 at 22:37
  • Okay. Sorry about that. I'll probably just delete my answer then. I guess the user would need to run the app as themselves, but then have you grant them permission to big query somehow? – Alan Wells Jul 23 '14 at 23:41
0

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

Serge insas
  • 45,904
  • 7
  • 105
  • 131
0

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

Community
  • 1
  • 1
Maksim Luzik
  • 5,863
  • 4
  • 36
  • 57