GCP Sheets/Docs API not displaying files in iframe

Question

I have a project that every time a new object is saved it also creates blank Google Doc and Google Sheet as additional resources users can work with.

For that I created a Service Account inside Google Cloud Platform, generated the .json, granted the permissions said in the documentation (Browser, Service Account Admin, IAM Workload Identity Pool Admin, Workload Identity User).

Also created an API Key.

With that I can successfully create, delete, list all files and rename particular file (scripts below) but I cannot load the iframe with the document and the spreadsheet itself. The address I tried to load in the iframe's src:

https://docs.google.com/document/d/DOC_ID?key=APIKEY

https://docs.google.com/spreadsheets/d/SHEET_ID/?key=APIKEY

https://docs.googleapis.com/v1/documents/DOC_ID?key=APIKEY

https://sheets.googleapis.com/v4/spreadsheets/SHEET_ID?key=APIKEY

First two address loads the "You need access - Ask for access, or switch to an account with access." Google message (screenshot here "You need access" issue).

The other two throws 401 - "Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential.".

Questions:

How can I display these files on Google's standard iframe (with the user interface, toolbar, etc.)?
Is there a way to see all files created by the service account in the Google Cloud Platform website like a personal Google Drive allows? I tried drive.google.com and it's empty, but I can list them all using Python.
I tried to make it work with Workload Identity Federation in AWS so I created the Pool, set AWS as the Provider with my AWS ID and selected the Service Account from the "CONNECTED SERVICE ACCOUNTS". Is there a guide about the AWS configuration side for this? I put it aside for now because I don't know the next step.

# GOOGLE DOC CREATION
def document_create(request, obj):

    SERVICE_ACCOUNT_FILE = '.json'
    SCOPES = ['https://www.googleapis.com/auth/documents']
    body = {
        'title': obj.title,
    }
    creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
    service = build('docs', 'v1', credentials=creds)
    document = service.documents().create(body=body).execute()

# GOOGLE SHEET CREATION
def spreadsheet_create(request, obj):

    SERVICE_ACCOUNT_FILE = '.json'
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
    spreadsheet = {
        'properties': {
            'title': obj.title,
        }
    }
    creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
    service = build('sheets', 'v4', credentials=creds)
    spreadsheet = service.spreadsheets().create(body=spreadsheet, fields='spreadsheetId').execute()

# LIST ALL FILES
def index(request, template='gcp/index.html'):
    
    SERVICE_ACCOUNT_FILE = '.json'
    SCOPES = [] # WORKS WITHOUT ANY SCOPE SOMEHOW
    creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
    service = build('drive', 'v3', credentials=creds)
    
    results = service.files().list().execute()
    drive_list = results.get('files', [])

# RENAME A FILE
def rename(request):

    SERVICE_ACCOUNT_FILE = '.json'
    SCOPES = [] # ALSO WORKS WITHOUT ANY SCOPE SOMEHOW
    creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
    service = build('drive', 'v3', credentials=creds)
    
    try:
        service.files().update(fileId=file_id, body={'name': new_file_name}).execute()
    except Exception as e:
        pass

(It's my first post here, so apologies if something is not how it's supposed to be.)

Thanks in advance!

I'm not sure why you are getting into service accounts and the API if all you want to do is to display Google Documents within the native interface. Can't you just share them and let Google do the heavy lifting? Do you want to somehow host a Google Sheets server, or something like that? — iansedano
– iansedano, Commented Aug 23, 2021 at 7:51
I need the API to allow users to create a Doc and a Sheet by themselves for each project they start. The project contains several things users can work with and a Doc to document their analysis and a Sheet to input data they collect. This can be done by multiple users working on the same projects with different roles and tasks. — Jon Levischi
– Jon Levischi, Commented Aug 23, 2021 at 14:03
You can do all this without the API though, why do you specifically need the API? What can you not accomplish with the normal user interface? — iansedano
– iansedano, Commented Aug 23, 2021 at 14:37
You can do this manually assuming all users know how to do it all right? I'm talking about thousands users online simultaneously, around 3~5 million per month, and they are nowhere near experienced users, they need it all to be instantly done for them by a single click "Start Project" so they can start working in one second, and not spend time setting everything up as the majority of them don't even know how to do it all by themselves. — Jon Levischi
– Jon Levischi, Commented Aug 23, 2021 at 14:53
Are they all in your domain? You don't need a service account for this. You could just make a normal internal app. Have you followed the quickstart for the different APIs, for instance? Its possible you could do this entirely with the Drive API. developers.google.com/drive/api/v3/quickstart/python - I think you may be getting into a rabbit hole that it going to make everything much more complicated than it needs to be. — iansedano
– iansedano, Commented Aug 23, 2021 at 15:02

Martin Zeitler · Accepted Answer · 2021-08-22 01:44:20Z

0

You'd need to share those documents to the public, in order to serve them on a web-page. Otherwise you could only render them to HTML, XLSX or PDF to view, but not embed them.

answered Aug 22, 2021 at 1:44

Martin Zeitler

79.6k20 gold badges170 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Levischi Over a year ago

Those files are created by the users and are going to be used right away. The amount of files is supposed to be huge, it's impossible to do this manually and also totally unsafe to turn them public. This was working before in the alpha version but stopped working after I rewrite the beta version. Looks like an authentication issue, not file permissions. In addition to that, there's not even a Drive (as I mentioned in the question), I'm using Google Cloud Platform'a API via Workspace account.

Jon Levischi · Accepted Answer · 2021-08-24 01:05:47Z

0

Turns out every new file created using Sheets and Docs API is private by default.

To make it available you just need to add new permissions specifying type (user/domain), role, and the value corresponding to the type selected.

This must be done using Drive API.

answered Aug 24, 2021 at 1:05

Jon Levischi

113 bronze badges

Collectives™ on Stack Overflow

GCP Sheets/Docs API not displaying files in iframe

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related