Can I using Google Sheet API only with API Key or using Client ID and Client secret, but without client_secret.json?

Question

In this provided Python code in this quickstart, it using credentials.json such in this line:

flow = InstalledAppFlow.from_client_secrets_file('credentials.json', SCOPES)

I have enabled the Sheet and Drive API. I created credentials from the APIs & Services menu, then on the Credentials tab, click on the CREATE CREDENTIALS button, then click on the OAuth client ID, and on the Application type I selected the Desktop app, then I download its JSON file, then set the credentials.json file to the right path, like this:

flow = InstalledAppFlow.from_client_secrets_file('client_secret_274233513361-l7vpffd7g9oree4tg5tledq9keqrevk3.apps.googleusercontent.com.json', SCOPES)

Then, when I run the quickstart code above, it shows a new browser pop-up that requires me to log in. After successfully login, yea, I can run the Python code successfully without any error.

But I don't want a new pop-up that requires me to log in first.

So my question: Can I use Google Sheet API only with my credentials Client ID and Client secret

or only with API Key

If we can do it, how to do it? is there any documentation on how to achieve that?

Can I confirm your current situation? In your current situation, you are using your downloaded JSON file for OAuth2 to /home/myuser/token.json of creds = Credentials.from_authorized_user_file('/home/myuser/token.json', SCOPES). Is my understanding correct? If my understanding is correct, what file did you use at credentials.json of flow = InstalledAppFlow.from_client_secrets_file('credentials.json', SCOPES)? If I misunderstood your situation, I apologize. — Tanaike
– Tanaike, Commented Dec 23, 2022 at 12:04
Thank you for replying. I understood that /home/myuser/token.json was created by running the quickstart script. If my understanding is correct, after 2nd run, the access token is retrieved from the refresh token in token.json. About But I don't want a new pop-up that requires me to log in first., when OAuth2 is used, it is required to authorize the scopes by the browser. If you don't want to use the browser, how about using the service account? When the service account is used, the access token can be retrieved without using the browser. — Tanaike
– Tanaike, Commented Dec 23, 2022 at 12:43
And, when you use an API key, in this case, the API key can access only the public contents. So, it is required to publicly share the Spreadsheet. And also, the API key can be used for only the GET method. By this, for example, the value cannot be put into Spreadsheet. Please be careful about this. — Tanaike
– Tanaike, Commented Dec 23, 2022 at 12:43
About is there any documentation on how to achieve that?, if you need the sample script for using Sheets API with the service account, how about these threads? stackoverflow.com/… I thought that the threads in Stackoverflow will be useful. If my comments were not useful, I apologize. — Tanaike
– Tanaike, Commented Dec 23, 2022 at 12:47
Service accounts are like regular accounts in some ways, such as having their own Drives. So using a service account to create a spreadsheet will create it for itself. Your options here are to delegate domain-wide authority to the account so it can act on behalf of your domain users, or you can share the folder where you want to create the spreadsheets with the service account and then create them there and share them with other users. It depends on what your end goal is. — Daniel
– Daniel, Commented Dec 23, 2022 at 14:31

Linda Lawton - DaImTo · Accepted Answer · 2022-12-23 15:49:01Z

1

open the credentials.jsom file it contains your clie id and client secret.

In order to access private user data you need the users permission.

If the sheet is set to public and you only need read access then yes you could use an api key.

However if the sheet is private you need permission.

my question is do you own this sheet if you the developer own the sheet you could use a service account which would mean you won't need to request authorization.

If this is a sheet owned by a user of your application then you need to use Oauth2 to request permission of the user to access it this requires the client id and client secret.

answered Dec 23, 2022 at 15:49

Linda Lawton - DaImTo

118k40 gold badges229 silver badges511 bronze badges

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

Comments

Tri · Accepted Answer · 2022-12-27 00:49:07Z

As @Tanaike suggests, I use the Google Service Account instead. It doesn't require me to login into a browser pop-up anymore. I can access private and public spreadsheets, not only public sheets if using API like mentioned by @daimto.

On how to create the service account and get the JSON file you can follow the instruction on my question here.

For example to create a new spreadsheet I use this code:

from apiclient import discovery
from google.oauth2 import service_account

def create_spreadsheet(spreadsheet_title)
    scopes = [
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/drive.file",
        "https://www.googleapis.com/auth/spreadsheets"
        ]


    # Open the JSON file that you have downloaded from the service account, and paste it here.
    service_account_json_credentials = {
          "type": "service_account",
          "project_id": "odoo-spreadsheet-371808",
          "private_key_id": "321ee08baexample6ae125918examplea69ddbec64",
          "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDIzR5iPZEQqjDh\nFJlSfQOd0wJfwbgHt+V3gosd2zT5FV8gBHfZXqEz0Nd+nFqOSebI2XxwpF99tOXo\n+PR+tetOGkY5wP8LE1GVZstyyDa/xQp7eE82Yexample9mAsDwpgEt9MQ\nxGEo/NsRGlCwedj9X0u0xd3cLeQa+H1xfhm8/QXWRxPYLSeoflxKEOyjc2QwdcIR\n2FgHDBBzCQ4UcZutjrZmlHeydhNxMP+nfC6dzfMOgs/+26yGmLuJqB+2DukHyITq\nglSKhUUUBO9ktC34favwZtuGfsRjDkqoVWOEkQxjzIC6Fvc/X/X9GaFDtn5qbECt\nDqpXOq9TAgMBAAECggEAAb2FXeSatUJEWi8LhSOAPIXvp1TOe4KkgnynKAMWWnJM\n+MIRJ9kiHIja1UYVotYRIfyAj5ldGoHO3E4MEG0TNQIVSbraewYX1uulJ8GEd/UF\nOjy4swf2JUmekEFHexUVB8Whks7E8oWFVsfc77YBUAyiVmexamplel9Dcthg\njzsornpl70FZgctAJCsVLqbPRZs3igce5+8eIXPigcy7NfyA3SbCY26Z7zSl+Wkx\nN3XLVsSkUPpUolyKe+Rf5+BGdw+aYaGlH3bjrckfIvmXNNcyQok+Lkp2KEgYz7xT\njHAF2f0g75/ilVHfl+WwxdOyv+UcvrcV+mL+eeIugQKBgQDq0MQW9+V+iGtvgS8A\nUgqt8/7R9dA3tAIOvz+jbk0av1rmcfLzJexamplefvu59lL3P3Qw+n2JVojc\nUeOBDoxUJZexampleGhOjGUjg+pTAFYjbfkBOxAupZegnyrzhRvK6pT\ns13gfZQz2lUE6dWErQfYT6VUeQKBgQDa6sTd+cnSRvgkuc5ogEQy9oZrnhfXMVZv\nPdp6pkKVZLMbl86AH4iK7ejEAuITWyWqWxJSKEZW+2QZRnEUhIva0YexampleCKaVXQsbE+jbfijdJFgNq1F6hi7yeKmSmTT5SNlecUrnjGpsqhPPZ99O8nUEZZ9v\nnjyEHOu3KwKBgGlSGBHBQMM/RwjtZXp8UePyI6Ji4fCKIb4hk9am+5h6qbfg9jqO\n71buEq+rpOb/wsKHNHFHmcL6J2J5WMuRQzy4nMKFWLQHuQ2G9IiAfegxwOqy4lgi\nW+3wdE4WyptQhfMGfbmIR4j71viCGlSOiBXP3bBgRm/8vdNTlMg1+Q7pAoGBAI8O\nuiXA1RV30TLWxT7fCzIujbsH7dZvZGd/iSV7Pqm3y5+tfYGWmbkexampleaL9UppMkrkjvxPh95sM6h4ahM1dnCaHHVAqvoP9QnxnZ1CKGuIamqxEUpes7V\nFZwN8hZYfLWBug5WumlgHLrPkI6WBjF/u1YuPA9PAoGAYg7GUkefdLyc8O5u20dm\n8AzGEzsWlJh2cEqJ1iYmOS42s9jw7fxQUbIH9K1UWORIShdQTxZilRDGvfbSqWH5\nLSjr8j0iR7WlvB9KxrSSAnKtt+vkdJz8YqL6/XzogG/C+ruh8f5T8wPNPh6tDtcj\n2Z/gZ6cOD8dVrDTFQebdrMI=\n-----END PRIVATE KEY-----\n",
          "client_email": "[email protected]",
          "client_id": "101569example94608",
          "auth_uri": "https://accounts.google.com/o/oauth2/auth",
          "token_uri": "https://oauth2.googleapis.com/token",
          "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
          "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/odoo12%40odoo-spreadsheet-371808.iam.gserviceaccount.com"
        }

    credentials = service_account.Credentials.from_service_account_info(service_account_json_credentials, scopes=scopes)
    spreadsheet_service = discovery.build('sheets', 'v4', credentials=credentials)

    spreadsheet = {
        'properties': {
            'title': spreadsheet_title
        }
        }
    creation_response = spreadsheet_service.spreadsheets().create(body=spreadsheet, fields='spreadsheetId').execute()

    spreadsheet_id = creation_response.get('spreadsheetId')
    return spreadsheet_id
    
create_spreadsheet('a new apreadsheet')

Hope this can help someone who facing a similar or wants to achieve the same things. Again, thank you to @Tanaike and everyone that has been taking your time to shed some light on the case I'm facing.

I hope that is not a real private key you have posted here. If so, you should disable it immediately.
Actually I have edited the key with some example words @andrewJames, thanks for your care.

Collectives™ on Stack Overflow

Can I using Google Sheet API only with API Key or using Client ID and Client secret, but without client_secret.json?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related